Please enable Javascript to correctly display the contents on Dot Net Tricks!

Difference between Primary Key and Foreign Key

  Author : Shailendra Chauhan
Posted On : 26 Sep 2012
Total Views : 341,022   
Updated On : 24 Sep 2016
 

In SQL Server, there are two keys - primary key and foreign key which seems identical, but actually both are different in features and behaviours. In this article, I would like to share the key difference between primary key and foreign key. For more help about keys in SQL Server refer the article Different Types of SQL Keys.

Difference between Primary Key & Foreign Key
Primary Key
Foreign Key
Primary key uniquely identify a record in the table.
Foreign key is a field in the table that is primary key in another table.
Primary Key can't accept null values.
Foreign key can accept multiple null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have only one Primary key in a table.
We can have more than one foreign key in a table.

Define Primary key and Foreign key

 --Create Parent Table 
CREATE TABLE Department 
 (
 DeptID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 Address varchar(100) NULL 
)
 GO 
 --Create Child Table 
CREATE TABLE Employee 
 (
 EmpID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 Salary int NULL,
 --define foreign key
 DeptID int FOREIGN KEY REFERENCES Department(DeptID)
 ) 

Note

As @Marc Jellinek suggested, I would like to add the below points about foreign key :

  1. Foreign keys do not automatically create an index, clustered or non-clustered. You must manually create an index on foreign keys.

  2. There are actual advantages to having a foreign key be supported with a clustered index, but you get only one per table. What's the advantage? If you are selecting the parent plus all child records, you want the child records next to each other. This is easy to accomplish using a clustered index.

  3. Having a null foreign key is usually a bad idea. In the example below, the record in [dbo].[child] is what would be referred to as an "orphan record". Think long and hard before doing this.

 IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'child') 
 DROP TABLE [dbo].[child]
 IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'parent') DROP TABLE [dbo].[parent]
 CREATE TABLE [dbo].[parent]
( 
[id] [int] IDENTITY NOT NULL, 
[name] [varchar](250) NOT NULL, 
CONSTRAINT [PK_dbo__parent] PRIMARY KEY NONCLUSTERED ([id])
)
 CREATE TABLE [dbo].[child]
(
[id] [int] IDENTITY NOT NULL, [parent_id] [int] NULL, 
[name] [varchar](250) NOT NULL, 
CONSTRAINT [PK_dbo__child] PRIMARY KEY NONCLUSTERED ([id]), 
CONSTRAINT [FK_dbo__child__dbo__parent] FOREIGN KEY ([parent_id]) REFERENCES [dbo].[parent]([id])
)
 --Insert data
INSERT INTO [dbo].[parent] ([name]) VALUES ('parent1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(1, 'child 1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(NULL, 'child 2') 
--Select data 
SELECT * FROM [dbo].[child] 
Summary

I hope you will enjoy these tricks while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.



Free Interview Books
 
COMMENTS (0)
2 JUN
Hadoop Development cum Administration (Online)

7:00 AM-9:00AM IST

Know More
28 MAY
ASP.NET Core with Angular4 Development (Classroom)

10:30 AM-11:30 AM Weekend

Know More
27 MAY
ASP.NET MVC with Angular4 Development (Classroom)

4:00 PM-5:30 PM Weekend

Know More
25 MAY
ASP.NET MVC with Angular4 Development (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

Know More
20 MAY
Angular2 and Angular4 Development (Online)

08:00 AM-10:00 AM IST(+5.30 GMT) Weekend

20 MAY
NodeJS with Angular4 Development (Classroom)

5:00 PM-6:30 PM Weekend

8 MAY
ASP.NET MVC with Angular4 Development (Online)

07:00 AM-09:00 AM IST(+5:30 GMT) M/W/F

2 MAY
ASP.NET Core Development (Online)

07:00 AM - 09:00 AM IST( TUS, THR)

29 APR
ASP.NET MVC with Angular2 Development (Classroom)

9:30 AM-11:00 AM

22 APR
MEAN Stack 2 Development (Classroom)

11:00 AM-12:30 PM Weekend

10 APR
Master Class ASP.NET MVC 5 with Angular2 Plus Angular4 Development (Online)

09:00 PM - 11:00 PM IST (M/W/F)

25 MAR
Xamarin Forms : Build Cross-platform Apps (Classroom)

8:00 AM-9:30AM Weekend

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+