Keys are generally properties applied to a column of a database table that signify it’s worth in that table. Following keys will help you understand the similarities and differences in the various column key options presented by MS SQL Server. In simple words, key in database is something that can identify a record.
-
Super Key
Super key is a group of one or more keys that can be used to identify a unique record in a table. Primary key, Unique key, Alternate key are collectively called Super Keys.
-
Candidate Key
A Candidate Key is a set of one or more columns that can identify a unique record in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key but all Candidate Keys are not primary keys.
-
Primary Key
Primary key is a set of one or more columns of a table that uniquely identify a record in a table. It doesn’t accept null and duplicate values. Only one Candidate Key can be Primary Key.
-
Alternate key
An Alternate key is a candidate key that currently is not primary key.
-
Composite/Compound Key
Composite Key is a combination of more than one columns of a table. It can be a Candidate key, Primary key.
-
Unique Key
Unique key is a set of one or more columns of a table that identify a unique record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.
-
Foreign Key
Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
Example : We can have a ManagerID column in the Employee table which is pointing to EmployeeID column in a employee table where it a primary key.
Post Comments if this helps.