Different types of keys in SQL
In this article I have tried to explain what is key and the different types of keys available with sql server-Super, Primary, Candidate, Composite, Secondary, Foreign and tried to provide example for each to make it more clear
Key
A key is that data item that exclusively identifies a record.
For example, Account_number,Product_code,Employee_Id and customer_number are used as key fields because they specifically identify a record stored in a database.
Different types of keys available are:-
1.Super key
A Super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set.
For example- for an entity set Employees, the set of attributes
(emp-name,address) can be considered to be a super key, if we assume that there are no 2 employees with the same emp_name as well as the same address.
2.Primary key
A Primary key uniquely identifies each record in a table and must never be the same for the 2 records.Primary key is always unique and not null.
For example- emp_code can be primary key for the entity set Employees.
The primary key should be chosen such that its attributes are never or rarely changed.For instance, the address field of a person should not be a part of the primary key, since it is likely to change. Emp_code on the other hand , is not changed, till employee is working in a particular company.
The primary key of a relation can be said to be a minimal super key.
3.Candidate key
A Candidate key is an attribute or set of attributes that uniquely identifies a record. In such a case, one of the candidate key is chosen to be a primary key.The remaining candidate keys are called Alternate keys. There
is only one primary key in a table, but there can be multiple candidate keys.In other words, candidate keys are those which have ability to be a primary key.
4.Composite key
In many cases, as we design a database, we will have tables that will use more than one column as part of the primary key.These are called Composite keys or Concatenated keys. In other words, when a record cannot be uniquely identified by a single field, in such cases composite key is used.
5.Secondary key
A Secondary key is an attribute or combination of attributes that may not be a candidate key but classifies the entity set on a particular characteristic. For example- the entity set EMPLOYEE having the attribute Department, which identifies by its value which means all instances of EMPLOYEE who belong to a given department.
More than 1 employee may belong to a department, so the Department attribute is not a candidate key for the entity set EMPLOYEE, since it cannot uniquely identify an individual employee.However, the Department attribute does identify all employees belonging to a given department hence, it can be considered as a secondary key.
6.Foreign key
In a relation, the column whose data values corresponds to the values of a key column in another relation is called a foreign key.
For example-
Product(t1) -- Prod_code(Primary key) Product_Name Supp_Code(Foreign Key)
Supplier(t2) -- Supp_Code(Primary key) Supp_Name
There are some rules with foreign keys:-
a. t2 can have entry for Supp_Code not present in t1.
b. entry deleted from t2 affects entry of t1.
c. entry deleted from t1 does not affect t2.