Primary Key And Foreign Key In SQL

Primary Key:
Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

Foreign Key: 
 A foreign key is a field  that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

Creating  And Dropping Primary Key In SQL Tables:

1)Add Primary key during the creation of Table

Create Table Employee

(Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Name Varchar(50))

Primary key with Clustered

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key Clustered (Emp_Id  )

 )

Insert Into Employee  Values (200,'Johny'),(100,'Job')

Select * From Employee

Emp_Id	Emp_Name
100	Job
200	Johny

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key Clustered(Emp_Id Desc)

 )

Insert Into Employee  Values (100,'Job'),(200,'Johny')

Select * From Employee

Emp_Id	Emp_Name
200	Johny
100	Job

Primary key with Nonclustered

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key NonClustered(Emp_Id)

 )

Insert Into Employee  Values (200,'Johny'),(100,'Job')

Select * From Employee

Emp_Id	Emp_Name
200	Johny
100	Job

2)Add Primary key After the creation of Table

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50))

Alter Table Employee Add Constraint PK_Emp_Id Primary Key(Emp_ID)

Note : During the creation of Primary key  where Key column should be Not NULL

3) Drop Primary Key

Alter Table Employee Drop PK_Emp_Id

Alter Table Employee Drop Constraint PK_Emp_Id

Creating  And Dropping Foreign Key In SQL Tables:

1)Add Foreign key during the creation of Table

Create Table Employee

( Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Name Varchar(50),

 Emp_Dep_Id Int Constraint FK_Emp_Dep_Id Foreign Key References Dept(Dep_Id)

 )

Create Table Employee

(Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Dep_Id Int,

 Emp_Name Varchar(50),

 Constraint FK_Emp_Dep_Id Foreign Key (Emp_Dep_Id) References Dept(Dep_Id) )

2)Add Foreign key After the creation of Table

Alter Table Employee Add Constraint FK_Emp_Dep_Id Foreign Key (Emp_Dep_Id) References Dept(Dep_Id)

3) Drop Foreign Key

Alter Table Employee Drop FK_Emp_Dep_Id

Alter Table Employee Drop Constraint FK_Emp_Dep_Id