Wednesday, May 20, 2015

Different types of keys in Sql server

A key is a single or combination of multiple fields. Its purpose is to access or retrieve data rows from table according to the requirement. The keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables.

1.  Super key: A super key is a combination of an attribute or set of attributes that uniquely identify a record in a table. A table might have many superkeys. Primary key, Unique key, Alternate key are subset of Super Keys.

2.  Primary key: A primary key uniquely identifies each record and prevents the duplicate values for a column or columns in a database table. Primary key column can't have null values. Each table can have only one Primary key and it creates clustered index on the column or columns. Read more about Primary key

3.  Foreign Key: A foreign key Constraint is used to link two tables together. A Foreign key in one table refers to primary key of another table.  It can accept multiple null and duplicate values. Read more about Foreign key

4.  Unique Key: Unique Key as name describe only one of its kind means uniquely identifies each record in a database table. It is like a primary key and does not allow the duplicates values in column or columns. The main difference is Unique key allow only one null value and primary key doesn't allow null value. Unique Key by default create a non-clustered index. We can have multiple Unique Key per table but only have one primary key. Read more about Unique key

5.  Candidate Key: A Candidate key is an attribute or set of attributes that uniquely identifies a record uniquely in a table. Tables can multiple candidate keys. Each candidate key can act as a primary key.

6.  Alternate key: Alternate Key can be any of the Candidate Keys except the Primary Key.

7.  Composite key: Composite Key is a combination of more than one column as part of the primary key.


No comments:

Post a Comment