SQL-Primary vs Foreign Key

In this tutorials, we will see the difference between Primary Key and Unique Key.

1. UNIQUE Constraint

By default, if you don't specify the UNIQUE constraint, a table column can not hold duplicate values.

Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.

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

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100) UNIQUE,
LastName varchar(100) UNIQUE,
Emailid varchar(100) NOT NULL,
Age int,
Departmentid int
);

2. 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.

Difference between the Primary Key vs Unique Key