Resources » SQL Server

Different types of keys in SQL


Posted Date: 16-Nov-2011  Last Updated:   Category: SQL Server    
Author: Member Level: Silver    Points: 10


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.


Did you like this resource? Share it with your friends and show your love!

Responses to "Different types of keys in SQL"
Feedbacks      

Post Comment:




  • 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.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India