SQL Server : Constraints and Indexes

What is a "constraint"?

      

• A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:

ü  PRIMARY/UNIQUE - enforces uniqueness of a particular table column.

ü  DEFAULT - specifies a default value for a column in case an insert operation does not provide one.

ü  FOREIGN KEY - validates that every value in a column exists in a column of another table.

ü  CHECK - checks that every value stored in a column is in some specified list

• Each type of constraint performs a specific type of action. There are five kinds of constraints in all, Primary key, Foreign key, Unique, Check and Default.

What is PRIMARY KEY?

• A PRIMARY KEY constraint is a unique identifier for a row within a database table. 
• Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.
•  The primary key constraints are used to enforce entity integrity.

What is UNIQUE KEY constraint?

• A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. 
• The unique key constraints are used to enforce entity integrity as the primary key constraints.

What is FOREIGN KEY?

• A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. 
• A foreign key in one table points to a primary key in another table. 
• Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. 
• The foreign key constraints are used to enforce referential integrity.

 What is CHECK Constraint?

• A CHECK constraint is used to limit the values that can be placed in a column. 
• The check constraints are used to enforce domain integrity.

What's the difference between a primary key and a unique key?

• Both primary key and unique key enforces uniqueness of the column on which they are defined.
•  But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. 
• Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

What is NOT NULL Constraint?

• A NOT NULL constraint enforces that the column will not accept null values. 
• The not null constraints are used to enforce domain integrity, as the check constraints.

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

• Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. 
• A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. 
• The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. 
• Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

• One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. 
• One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. 
• Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What are the difference between clustered and a non-clustered index?

1.    A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

2.    A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?

A table can have one of the following index configurations:

• No indexes
• A clustered index
• A clustered index and many nonclustered indexes
• A nonclustered index
• Many nonclustered indexes

Define candidate key, alternate key and composite key.

• A candidate key is one that can identify each row of a table uniquely.
• Generally a candidate key becomes the primary key of the table.
• If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Is it possible to create Cluster Index on Unique Key Column?

• The answer is YES. Though unique key allows null value but it still maintains the uniqueness of the column.
• Unique key column by default creates a non-cluster index on the column.
• Primary key creates cluster index on the column. The only difference is Unique key column allows only one null value.