Tuesday 23 July 2013

SQL SERVER - PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, COMPOSITE KEY, CANDIDATE KEY AND ALTERNATE KEY


Keys are used to find the Row with identity and sort the data .They are so many keys present in SQLSERVER

PRIMARY KEY 
     Keys which are used to find the row with unique identity is known as Primary Key, Primary key is also a Unique Key but doesn't allow the NULL Value, In the below table EmployeeId is consider as primary key


EmployeeId
EmployeeName
E1
Rajesh
E2
Rajesh

Create Table Employee
(
 EmployeeId varchar(3) primary key,
 EmployeeName varchar(40)
)

UNIQUE KEY
        Key which is used to find the uniquely identify the rows of a table is known as Unique Key,Unique Key can allow one NULL value. It doesn't allow duplicate values.

FOREIGN KEY
       Key which is used as Reference key for another table and also behaves primary key of the table is known as Foreign Key.In the Below table EmpId is a Primary key for the first table and also it is reference as foreign key in the second table.


EmpId
Name
Department
1
Rajesh
Computer
2
Suresh
Electronics
3
Praba
Civil


Id
EmpId
PhoneNumber
1
1
3452342
2
1
1234563
3
1
2345612
4
2
2398521
5
2
2323177
6
2
5302994
7
3
3958218

COMPOSITE KEY
    Composite Key is a combination of more than one columns of a table. It can be a Candidate key, Primary key

CANDIDATE KEY
     Any number of columns that are uniquely identify the row in a table is known as Candidate key, Any Candidate key be a Primary Key,But we can select one of them as Primary key.


EmpMailId
EmployeeId
EmployeeName
E1@gma.com
E1
Rajesh
E2@gma.com
E2
Suresh


      In the above table,EmpMailId and EmployeeId are Candidate Keys But EmployeeId is consider as Primary Key.

ALTERNATE KEY
              Key which can choose as Primary key,But it is a Candidate Key other than primary key for Ex Above Diagram have the EmpMailId is a Alternate Key which can be choose as Primary,but it is a Candidate key not a Primary Key

In this article i explained the concepts of Keys, I hope this will give some understanding about the concepts of various keys in Sql Server.