Skip to main content

3. Keys and Constraints

Image
By


§ Key are used for identifying unique rows from table. It also establishes relationship among tables.
§ Constraint are used to specify rules for the data in a table.
§ Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
Identity
§ Its purpose is to allow SQL Server to automatically insert numerical primary key values to a  table as new data is inserted.

CREATE TABLE TABLE_NAME
( PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY { [Initial_Value],[Interval]},

Where [Initial_Value] is the first value of the primary key, and [Interval] is the interval between two consecutive identity values. If no [Initial_Value] or [Interval] is specified, the default for both is 1. In other words, the first row would be 1, and subsequent rows would get a value that is 1 larger than the previous row.

Primary Key

§ A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.
§ Nulls are not allowed in primary key columns.
§ It is applicable for single column in a table.
Which column should you pick?

§ Choose a column as a primary key is to fetch dataset quickly and it allocate a unique number to each row if it is an identity.
§ In a table of employees, any column like First Name is a poor choice since you cannot control employee's first names and it may be duplicate.
§ Often there is only one choice for the primary key, which is its ID.
§ CREATE TABLE Persons
(
P_IdintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

Alter
ALTERTABLE Persons
ADDPRIMARYKEY (P_Id)

Drop
ALTERTABLE Persons
DROPPRIMARYKEY

For Constraints
ALTERTABLE Persons
ADDCONSTRAINTpk_PersonIDPRIMARYKEY (P_Id)

Foreign Key

§ Foreign keys are columns that point to primary key columns.
§ For example, OrderNo is the primary key of the table ORDERS and CustomerNo is a foreign key that points to the primary key in the CUSTOMERS table.
CREATETABLE Orders
(
O_IdintNOTNULL,
OrderNointNOTNULL,
P_Idint,
PRIMARYKEY (O_Id),
FOREIGNKEY (P_Id)REFERENCES Persons(P_Id)
)

Alter
ALTERTABLE Orders
ADDFOREIGNKEY (P_Id)
REFERENCES Persons(P_Id)

Drop
ALTERTABLE Orders
DROPFOREIGNKEYfk_PerOrders

For Constraints
ALTERTABLE Orders
ADDCONSTRAINTfk_PerOrders
FOREIGNKEY (P_Id)
REFERENCES Persons(P_Id)

Unique Key

§ Unique key uniquely identifies each record in a database table or ensures that all values in a column are different.
§ Note that you can have many UNIQUE key per table, but only one PRIMARY KEY per table.

CREATETABLE Persons
(
P_IdintNOTNULLUNIQUE,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
City varchar(255)
)

Alter
ALTERTABLE Persons
ADDUNIQUE (P_Id)

Drop
ALTERTABLE Persons
DROPINDEXuc_PersonID

For Constraints
ALTERTABLE Persons
ADDCONSTRAINTuc_PersonIDUNIQUE (P_Id,LastName)

Not Null

NOT NULL constraint enforces a column to NOT accept NULL values.This means that you cannot insert a new record, or update a record without adding a value to this field.
CREATETABLEPersonsNotNull
(
P_IdintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
City varchar(255)
)

Check Constraints

The CHECK constraint ensures that all values in a column satisfy certain conditions.It is used to limit the value range that can be placed in a column.
CREATETABLE Persons
(
P_IdintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
City varchar(255),
CHECK (P_Id>0)
)

Alter
ALTERTABLE Persons
ADDCHECK (P_Id>0)

Or
ALTERTABLE Persons
ADDCONSTRAINTchk_PersonCHECK (P_Id>0 AND City='Sandnes')

Drop
ALTERTABLE Persons
DROPCONSTRAINTchk_Person
Or
ALTERTABLE Persons
DROPCHECKchk_Person

Default Constraints

Provides a default value for a column when none is specified. The DEFAULT constraint is used to insert a default value into a column, if no other value is specified.

CREATETABLE Persons
(
P_IdintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
City varchar(255)DEFAULT'Sandnes'
)
Alter
ALTERTABLE Persons
ALTER City SETDEFAULT'SANDNES'

Drop
ALTERTABLE Persons
ALTER City DROPDEFAULT


Difference Between Primary Key and Unique Key


               PRIMARY KEY
             UNIQUE KEY
It doesn’t allow null
Allow Null value, but only one Null value.
By default it adds a clustered index
By default it adds unique non-clustered index
A table can have only one primary key column
A table can have more than one unique key















<<Prev                                                                         Next>>
Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                               ...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more