Today I am going to explain difference between Primary key and Foreign key in SQL. It is very frequently asked interview question for beginner or experienced. So, I will try to explain one by one.

Primary Key

1.  Primary key is a column or set of columns that is basically used to identity a rows in table.

2.  Primary key can’t be null. It means that if you define a column or set of columns as primary key then you can not pass null value in that column.

3.  Primary Key doesn’t allow duplicate value. It means that you can not enter same value in primary key column for multiple rows.

4.  Only one primary key can be defined for a table.

5.  Primary key creates by default a clustered index for the table that organized the data in physical order.

6.  If you have defined a column as primary key related with foreign key in different table. You can’t delete primary key defined rows till that foreign key data exists in related table.

7.  You can define primary key constraint on temporary table and table variable.

 

Example

CREATE TABLE Persons
(
  Person_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  PRIMARY KEY (Person_Id)
)
 
Foreign Key

1.  A Foreign key is a column in the table that is referenced to other table columns which is defined as primary key. The main theme behind the foreign key is referential integrity. Primary key’s table is called parent table and foreign key’s table is called child table.

2.  Foreign key can be having multiple null values.

3.  Foreign key allow duplicate values. It means that you can enter same value in same column in different rows.

4.  You can create multiple columns as foreign key which is related to different table with primary key.

5.  Foreign key creates Non-clustered index. Foreign key doesn’t create non-clustered index automatically. You have to create index explicitly.

6.  You can delete foreign key value from the table even though the column is referred to the primary key.

7.  You can insert a value in foreign key column that is not exist in parent table’s primary key column.

8.  You can not define foreign key in temporary table and table variable.

 

Example

CREATE TABLE Orders
(
  Order_Id int NOT NULL,
  OrderNo int NOT NULL,
  Person_Id int,
  PRIMARY KEY (O_Id),
  CONSTRAINT fk_PerOrders FOREIGN KEY (Person_Id)
  REFERENCES Persons(Person_Id)
)
Conclusion

So, Here We learned about primary key and foreign key. What is difference between both of them.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks.