Differences between Primary Key and Foreign Key

Primary Key and Foreign Key

In SQL Server, there are two keys - primary key and foreign key which seem identical, but actually, both are different in features and behaviors. A primary key acts as a unique ID for each record in a database, ensuring that every row is distinct. Consider it your table's technique of identifying individual rows, similar to how each person is assigned a unique Social Security number.

A foreign key is used to establish a connection between two tables. It functions similarly to a reference, ensuring that the value in one table corresponds to a valid value in the other. It contributes to data integrity between related tables. So, primary keys are unique identifiers, while foreign keys are table connectors!

In this SQL Server tutorial, we will see details about 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

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.

Read More - DBMS Interview Questions For Freshers

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

Department Table

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

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

Difference between Primary Key and Foreign Key

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.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

Take our Sqlserver skill challenge to evaluate yourself!

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

GET FREE CHALLENGE