Skip to main content

SQL Primary key and Foreign Key

Primary Key Constraint:
  • It contains only unique value.
  • It will not allow NULL value.                        
  • It is used to uniquely identified the records in table.
  • It can be refer by another table
CREATE TABLE Employee (id int PRIMARY KEY ,Emp_name nvarchar(20),AGE int,salary int);
 Field Data Type Null key Default
 id INT NO PRIMARY KEY PK 
Emp_name NVARCHAR(20) NO  

 Age INT   
 salary INT   
CREATE TABLE Employee (id int CONSTRAINT PK PRIMARY KEY ,Emp_name nvarchar(20),AGE int,salary int);
The PK is name of constraint.

Using ALTER 
CREATE TABLE Employee (id int NOT NULL  ,Emp_name nvarchar(20),AGE int ,salary int);
 Field Data Type Null key Default
 id INT NO  
Emp_name NVARCHAR(20) NO  

 Age INT   
 salary INT   
 To set id as primary key using alter statement it should be not null.

 ALTER TABLE Employee ADD CONSTRAINT PK PRIMARY KEY (id) ;
 Field Data Type Null key Default
 id INT NO PRIMARY KEY PK 
Emp_name NVARCHAR(20) NO  

 Age INT   
 salary INT   

To Drop Primary Key.

ALTER table Employee DROP CONSTRAINT PK 

FOREIGN KEY
  • It refer to another database table.                          .
  • It not be NULL value.
  • It must be UNIQUE.
  • It used to relate table
Let us look another example. 
 CREATE TABLE Department (Depart_id int PRIMARY KEY, Dept_name nvarchar(20));
in this table department the column id is primary key.
Deparment
 Dept_id Dept_name
     1 Director
     2 Manager
     3 Employee
     4 HR
     5 TeamLeader
     6 Developer
     7 Tester
     8 Supporter   

CREATE TABLE Employee (id int PRIMARY KEY , Emp_name nvarchar(20),AGE int,salary int, Dept_id int REFERENCES Department(Depart_id), Location nvarchar(20));
Employee
 id     Emp_name      Age         Salary Dept_id Location
 1 Jebas  35 55000 2 TVL
 2             Thomas  30 70000 1 TVL
 3 Mark              25 55000 2 TEN
 4         Harry  42 55000 2 TUTY
 5 Willam  45 70000 1 TUTY
 6 James  32 45000 3 TEN
 7 Mock  22 45000 3
 KOVI
 8 Sherin  23 12000 7 TUTY
 10 Moses  26 23000 4 RAMD
The REFERENCES Department(id) is set to refer department table primary key column Dept_id.
In above table the employee jebas depart_id is 2, and is refer as Manager in Department.


The another format to add foreign key
CREATE TABLE Employee (id int, Emp_name nvarchar(20),AGE int,salary int, Dept_id int,Location nvarchar(20), PRIMARY KEY(id), FOREIGN KEY(Dept_id) REFERENCES Department(Depart_id));

Using ALTER to add Foreign Key.

ALTER TABLE Employee ADD FOREIGN KEY(id) REFERENCES Department(id);


Thank You. Like, Share and Subscribe 👍 Followlect.com

Comments