Difference between Primary Key and Foreign Key

31 Aug 2022

Intermediate

35.4K Views

4 min read

In SQL Server, there are two keys - primary key and foreign key which seem identical, but actually, both are different in features and behaviors. In this article, I would like to share the key differences between primary key and foreign key.

A primary key is generally focusing on the uniqueness of the table records. And it is a column or a set of columns that uniquely identifies each row of the database. It means that the table should not have any duplicate value for the specific column across the table. Apart from that, the table's primary key column does not contain any NULL value. whereas the foreign key is primarily used to build a relationship or connection between the two different tables. The primary usage of the foreign key is to sustain data integrity between two separate instances of an entity at a time.

For more information about the keys, please refer to the article Different Types of SQL Keys.

Difference between Primary Key and Foreign Key

Primary Key

Foreign Key

The primary key uniquely identifies a record in the table.

A foreign key is a field in the table that is the primary key in another table.

Primary Key can't accept null values.

A foreign key can accept multiple null values.

By default, the Primary key is clustered index, and data in the database table is physically organized in the sequence of the clustered index.

Foreign keys do not automatically create an index, clustered or non-clustered. You can manually create an index on a foreign key.

We can have only one Primary key in a table.

We can have more than one foreign key on a table.

Defining 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)
 )

Important Note

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

Foreign keys do not automatically create an index, clustered or non-clustered. You must manually create an index on foreign keys.
There are actually 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.
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.

Dropping Database Tables

 
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]

Creating Indexes on Tables

 
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

While working with relational database management systems, the keys are the most important aspect to maintaining the relationship between two tables or uniquely identifying data or records from the database tables. The primary key is used to identify data uniquely therefore two rows of a table can’t have the same primary key values. The primary condition is that It can’t be null. And on the other hand, a foreign key is useful for maintaining the relationship between two tables references. The primary key of a one-table act as the foreign key in the other tables of the database. Any foreign key of a table enforces the referential integrity constraint. It can be more than once on the table.

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.