Browse Tutorials
Differences between Primary Key and Foreign Key

Differences between Primary Key and Foreign Key

16 Mar 2024
Intermediate
38K Views
12 min read

Primary Key and Foreign Key: An Overview

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 SQL Server tutorial, we will see in detail the comparison between the primary key and the foreign key with a specific focus on the differences between the Primary Key and the Foreign Key.

Read More: SQL Server Interview Questions and Answers

What Is Primary Key?

A primary key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It can not accept null, or duplicate values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Example of Primary Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

In the above table, Emp_Id is the primary key. Each employee has a unique ID assigned to them, ensuring that no two employees share the same ID.

What Is Foreign Key?

A foreign key is an attribute that is a Primary key in its parent table but is included as an attribute in another host table. It is a column (or columns) that references a column (most often the primary key) of another table.

Example of Foreign Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

Department Table

Dept_IdDept_NameDesignation
101VideoVideo_Maker
201Search_EngineSEO
301ContentWriter

To establish a relationship between these tables, we can introduce a foreign key in the "Employee" table that references the primary key of the "Department" table. Let's add a column called "Dept_ID" as a foreign key in the "Employee" table.

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmailDept_Id
1SakshiDelhi123456789abc@xyz.com101
2SouravHazaribagh223365796jkl@gmail.com201
3PragatiNanded175468965ghi@yahoo.com301

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

Difference between Primary Key and Foreign Key

Comparison BasisPrimary KeyForeign Key
DefinitionA primary key is a unique identifier for each record in a table.A foreign key establishes a relationship between tables by referencing the primary key of another table.
BasicEnsures uniqueness and data integrity within a single table.Establishes relationships and maintains referential integrity between tables.
NULLThe primary key column value can never be NULL.The foreign key column can accept a NULL value
Count A table can have only one primary key. A table can have more than one foreign key.
Duplication No duplicate primary key values are allowed within the table. Can contain duplicate foreign key values, reflecting multiple records associated with the same reference.
Indexing Primary keys are automatically indexed to enhance data retrieval speed. Foreign keys can be indexed but are not automatically indexed.
Deletion The primary key value can't be removed from the table. If you want to delete it, then make sure the referencing foreign key does not contain its value. The foreign key value can be removed from the table without bothering that it refers to the primary key of another table.
Insertion Each new record must have a unique primary key value assigned. The foreign key can reference an existing primary key value or be NULL if the relationship is optional.
Temporary table Primary keys can be applied to temporary tables. Foreign keys can also be applied to temporary tables to establish relationships.
RelationshipPrimary keys define the basis for establishing relationships with other tables.Foreign keys establish relationships and connect data between related tables.

Read More: SQL Integrity Constraints

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:

  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 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 is 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 of 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. On the other hand, a foreign key is useful for maintaining the relationship between two table references.

I hope you will enjoy these tricks while programming with SQL Server. For practical experience, consider our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. What is the difference between primary key and foreign key and index?

Primary key and foreign key are used to define relationships between tables and maintain data integrity, indexes are used to optimize query performance by providing fast access to data.

Q2. What is a foreign key in a database?

A foreign key is an attribute that is a Primary key in its parent table but is included as an attribute in another host table. It is a column (or columns) that references a column (most often the primary key) of another table.

Q3. What is the difference between a primary key and a foreign key?

The main difference between them is that the primary key identifies each record in the table, whereas the foreign key links two tables together.

Q4. What is a primary key?

A primary key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It can not accept null, or duplicate values.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this