Different types of keys in SQL
Pallavi G Rao
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
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:-
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.
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.
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
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.
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
, 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
attribute is not a candidate key for the entity set EMPLOYEE, since it cannot uniquely identify an individual employee.However, the
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
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!
Responses to "Different types of keys in SQL"
Notify me by email when others post comments to this article.
Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
No HTML formatting and links to other web sites are allowed.
This is a strictly moderated site. Absolutely no spam allowed.
to fill automatically.
(Will not be published, but
to validate comment)
Type the numbers and letters shown on the left.
Last 7 Days
Awards & Gifts
Articles Rss Feeds
Forum Rss Feeds
Talk to Webmaster Tony John
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India