Keys in sql server - key Constrains

• To avoid duplication, we need to define constrains on data.
• For example, all students will have unique roll number.
• Here are primary key, foreign key, unique key etc.

Primary Key

• Primary key is column inside table that can be used to uniquely identify a record.
• For example, we can get record of particular employee based
• on his employee number.
• We can define primary key at the time of table creation.
• Syntax: create table <tableName> (<columnName><datatype>(<size>)Primary key));
• Example :  CREATE TABLE Employee (Emp_Id int PRIMARY KEY IDENTITY(1,1), LastName varchar(255) NOT NULL, FirstName varchar(255), LastName varchar(255), Add varchar(255), City varchar(255)
• Here Emp_Id field is primary key & auto incremented
• which has been specified by Identity(1,1)

• There are some attributes of primary key.
• It will not allow duplicate values.
• It will not allow null values.
• Only one primary key is allowed per table.
• It’s main purpose is record uniqueness.

Foreign Key

• When one column is primary key in one table and being referred in second table it is called foreign key in second table.
• For example, Emp_Id of employee will be referred in personal information table, leave table, salary table and so on..
• Syntax: <columnName><datatypa>(<size>) references <tableName> [(<columnName>)]
• Example: 
• create table Salary_Master(ID int primary key, Emp_ID numeric(5,0) references Employee_Master, basic numeric(5,0) date datetime);
• This will refer Emp_Id from Employee_Master table and inside Employee_Master it is primary key.
• Attributes of foreign key are…
• Parent that is being referred must have primary key.
• Parent record can be deleted if there is no record in child.
• Child may have duplicate value from parent but parent can not have.
• Record can not be inserted in child if corresponding record does not exist in parent table.

Unique Key

• When column inside the table is defined as unique key, this key can be used to uniquely identify a record. 
• It is similar to primary key but difference is it allows null inside column where as primary key will not allow null value.
• A table can have more than one unique key but it is not possible with primary key.