Resources » SQL Server
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
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:-
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.
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.
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.
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.
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.
In a relation, the column whose data values corresponds to the values of a key column in another relation is called a foreign key.
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.
Did you like this resource? Share it with your friends and show your love!
Return to Article Index
Active MembersTodayLast 7 Daysmore...
Talk to Webmaster Tony John