SQL-Primary vs Foreign Key

In this tutorial, you will see the difference between SQL Server Primary Key and foreign key.

1. PRIMARY KEY Constraint

The below are the some points which defines primary key:

1. A primary key must be unique and non-null, so they're the same from that standpoint. However, a table can only have one primary key, while you can have multiple unique, non-null keys .
2. A table can have only one primary key.
3. A primary key can be defined on one column or the combination of multiple columns known as a composite primary key.
4. A primary key cannot exceed 16 columns and a total key length of 900 bytes.
5. The primary key uniquely identifies each row in a table.
6. It is often defined on the identity column. The Primary key column do not allow NULL or duplicate values. It will raise an error if try to do so.
7. All columns defined within the primary key constraint must be defined as a NOT NULL column.

The following example create a table with constraints for the columns: FirstName when the Employee table is created:

CREATE TABLE Employee (
Eid int PRIMARY KEY IDENTITY(1,1),
FirstName varchar(100) NOT NULL,
LastName varchar(100),
Emailid varchar(100),
Age int,
Departmentid int
);

The IDENTITY property is used for the Eid column to automatically generate unique integer values.

2. FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables or A foreign key means that values in one table must also appear in another table. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

We will create two new tables to understand the FOREIGN KEY constraint functionality. The first table will act as the parent table with the ID column defined as a PRIMARY KEY column. The second table will act as the child table, with the ID column defined as the FOREIGN KEY column that references the ID column on the parent table.

Look at the following two tables:

CREATE TABLE ConstraintParentTable
(
ParentID INT PRIMARY KEY,
Name VARCHAR(100) NULL
)
GO
CREATE TABLE ConstraintChildTable
(
ChildID INT PRIMARY KEY,
ID INT FOREIGN KEY REFERENCES ConstraintParentTable(ParentID)
)

Difference between the Primary Key vs Foreign Key