Database Keys: Types and Differences

Keys in SQL Server

A key is one or more columns used to identify rows, enforce uniqueness, or create relationships between tables.

The main key types are:

• Primary key

• Unique key

• Candidate key

• Composite key

• Foreign key

1. Primary Key

What is a primary key?

A primary key uniquely identifies every row in a table.

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Email VARCHAR(150)
);

Here, EmployeeId uniquely identifies each employee.

Why do we need it?

• To give every row a unique identity

• To prevent duplicate records

• To prevent NULL identifiers

• To reference rows from other tables

• To maintain entity integrity

Rules

• Values must be unique.

• It cannot contain NULL.

• A table can have only one primary key constraint.

• The primary key may contain one or multiple columns.

• SQL Server automatically creates a unique index to enforce it.

Example

INSERT INTO Employees VALUES (1, 'Arun', 'arun@example.com');
INSERT INTO Employees VALUES (2, 'Bala', 'bala@example.com');

The following statements fail:

-- Duplicate primary key
INSERT INTO Employees VALUES (1, 'Kumar', 'kumar@example.com');

-- NULL primary key
INSERT INTO Employees VALUES (NULL, 'Ravi', 'ravi@example.com');

Important point

A primary key is clustered by default in SQL Server only when another clustered index does not already exist. A primary key does not always have to be clustered.

CONSTRAINT PK_Employees
PRIMARY KEY NONCLUSTERED (EmployeeId)

2. Unique Key

What is a unique key?

A unique key ensures that duplicate values cannot be stored in a column or combination of columns.

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Email VARCHAR(150) UNIQUE
);

Here:

• EmployeeId is the primary key.

• Email is a unique key.

Therefore, two employees cannot have the same email address.

Why do we need it?

A table may have multiple columns that must be unique even though they are not the primary identifier.

Examples include:

• Email address

• Passport number

• Aadhaar number

• Employee code

• Username

Rules

• It prevents duplicate values.

• A table can have multiple unique constraints.

• In SQL Server, a regular unique constraint allows one NULL.

• SQL Server creates a unique index to enforce the constraint.

• It can be referenced by a foreign key.

Example

INSERT INTO Employees
VALUES (1, 'Arun', 'arun@example.com');

INSERT INTO Employees
VALUES (2, 'Bala', 'bala@example.com');

The following statement fails because the email already exists:

INSERT INTO Employees
VALUES (3, 'Kumar', 'arun@example.com');

Allowing multiple NULL values

A regular SQL Server unique constraint permits only one NULL. If you need multiple NULL values while enforcing uniqueness for non-NULL values, use a filtered unique index:

CREATE UNIQUE INDEX UX_Employees_Email
ON Employees(Email)
WHERE Email IS NOT NULL;

3. Candidate Key

What is a candidate key?

A candidate key is any minimal column or minimal combination of columns capable of uniquely identifying a row.

Consider the following employee data:

If all three columns are guaranteed to be unique, the candidate keys are:

• EmployeeId

• EmployeeCode

• Email

One candidate key is selected as the primary key. The remaining candidate keys are called alternate keys.

For example:

• Primary key: EmployeeId

• Alternate candidate keys: EmployeeCode and Email

Why do we need candidate keys?

Candidate keys help database designers:

• Identify all possible unique identifiers

• Choose the best primary key

• Recognize other columns that require unique constraints

• Design normalized tables correctly

What does “minimal” mean?

A candidate key must not contain unnecessary columns.

Suppose EmployeeId alone is unique:

(EmployeeId)

Then this is not a candidate key:

(EmployeeId, EmployeeName)

Although the combination is unique, EmployeeName is unnecessary because EmployeeId alone already identifies the row.

Such a combination is called a superkey, but it is not a candidate key because it is not minimal.

Is CANDIDATE KEY SQL syntax?

No. Candidate key is mainly a database-design concept.

It is normally implemented using:

• PRIMARY KEY

• UNIQUE constraints

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeCode VARCHAR(20) UNIQUE,
    Email VARCHAR(150) UNIQUE
);

All three are logically candidate keys because each can uniquely identify an employee.

4. Composite Key

What is a composite key?

A composite key is a key made from two or more columns.

The combination of the columns uniquely identifies a row.

Consider an OrderItems table:

Neither OrderId nor ProductId is unique individually:

• One order can contain several products.

• One product can appear in several orders.

But this combination is unique:

(OrderId, ProductId)

Why do we need it?

A composite key is useful when a row is naturally identified by a combination of values.

Common examples:

• Order and product

• Student and course

• Employee and project

• Invoice and item

• Country and phone number

• Tenant and user code

Example

CREATE TABLE OrderItems
(
    OrderId INT,
    ProductId INT,
    Quantity INT NOT NULL,

    CONSTRAINT PK_OrderItems
        PRIMARY KEY (OrderId, ProductId)
);

This is allowed:

INSERT INTO OrderItems VALUES (101, 1, 2);
INSERT INTO OrderItems VALUES (101, 2, 5);

This fails because the combination already exists:

INSERT INTO OrderItems VALUES (101, 1, 4);

Important clarification

“Composite” describes the number of columns, not a separate constraint type.

A composite key can be:

• A composite primary key

• A composite unique key

• A composite candidate key

• A composite foreign key

Example of a composite unique key:

CONSTRAINT UQ_Bookings
UNIQUE (RoomId, BookingDate)

5. Foreign Key

What is a foreign key?

A foreign key is a column or combination of columns that refers to a primary key or unique candidate key in another table.

It creates a relationship between tables.

Example relationship

Departments
    DepartmentId — Primary Key

Employees
    DepartmentId — Foreign Key

Table creation

CREATE TABLE Departments
(
    DepartmentId INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL
);

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentId INT,

    CONSTRAINT FK_Employees_Departments
        FOREIGN KEY (DepartmentId)
        REFERENCES Departments(DepartmentId)
);

Why do we need it?

A foreign key:

• Creates relationships between tables

• Maintains referential integrity

• Prevents invalid references

• Reduces inconsistent data

• Supports normalized database design

Valid example

INSERT INTO Departments
VALUES (10, 'Information Technology');

INSERT INTO Employees
VALUES (1, 'Arun', 10);

This works because department 10 exists.

Invalid example

INSERT INTO Employees
VALUES (2, 'Bala', 99);

This fails if department 99 does not exist.

Can foreign keys contain duplicate values?

Yes. Many employees can belong to the same department:

Employee 1 → Department 10
Employee 2 → Department 10
Employee 3 → Department 10

Therefore, foreign keys do not have to be unique.

Can foreign keys contain NULL?

Yes, if the column is nullable:

DepartmentId INT NULL

A NULL value can mean that the employee has not yet been assigned to a department.

To make the relationship mandatory:

DepartmentId INT NOT NULL

Delete and update behavior

SQL Server supports actions such as:

FOREIGN KEY (DepartmentId)
REFERENCES Departments(DepartmentId)
ON DELETE CASCADE
ON UPDATE CASCADE

ON DELETE CASCADE means deleting a parent row automatically deletes its related child rows. It must be used carefully because it can remove a large amount of related data.

Complete Practical Example

CREATE TABLE Departments
(
    DepartmentId INT,
    DepartmentCode VARCHAR(10) NOT NULL,

    CONSTRAINT PK_Departments
        PRIMARY KEY (DepartmentId),

    CONSTRAINT UQ_Departments_Code
        UNIQUE (DepartmentCode)
);

CREATE TABLE Employees
(
    EmployeeId INT,
    EmployeeCode VARCHAR(20) NOT NULL,
    Email VARCHAR(150) NOT NULL,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentId INT NOT NULL,

    CONSTRAINT PK_Employees
        PRIMARY KEY (EmployeeId),

    CONSTRAINT UQ_Employees_Code
        UNIQUE (EmployeeCode),

    CONSTRAINT UQ_Employees_Email
        UNIQUE (Email),

    CONSTRAINT FK_Employees_Departments
        FOREIGN KEY (DepartmentId)
        REFERENCES Departments(DepartmentId)
);

CREATE TABLE Projects
(
    ProjectId INT PRIMARY KEY,
    ProjectName VARCHAR(100) NOT NULL
);

CREATE TABLE EmployeeProjects
(
    EmployeeId INT,
    ProjectId INT,
    AssignedDate DATE NOT NULL,

    CONSTRAINT PK_EmployeeProjects
        PRIMARY KEY (EmployeeId, ProjectId),

    CONSTRAINT FK_EmployeeProjects_Employees
        FOREIGN KEY (EmployeeId)
        REFERENCES Employees(EmployeeId),

    CONSTRAINT FK_EmployeeProjects_Projects
        FOREIGN KEY (ProjectId)
        REFERENCES Projects(ProjectId)
);

In this example:

• Departments.DepartmentId → Primary key

• DepartmentCode → Unique key and candidate key

• Employees.EmployeeId → Selected candidate key and primary key

• EmployeeCode and Email → Unique keys and alternate candidate keys

• Employees.DepartmentId → Foreign key

• (EmployeeId, ProjectId) → Composite primary key

• EmployeeProjects.EmployeeId and ProjectId → Foreign keys

Key Differences

Relationship Between the Keys

Suppose a table contains three minimal unique identifiers:

EmployeeId
EmployeeCode
Email

All three are candidate keys.

After selecting EmployeeId as the primary key:

Candidate keys
├── EmployeeId   → Primary key
├── EmployeeCode → Alternate key, enforced with UNIQUE
└── Email        → Alternate key, enforced with UNIQUE

If a candidate key requires multiple columns, it is also a composite candidate key.

Another table can store EmployeeId as a foreign key to reference the employee.

Key Points

• A primary key is the selected main identifier for a table.

• A unique key prevents duplicate values in additional columns.

• A candidate key is any minimal set of columns capable of uniquely identifying a row.

• The candidate key selected as the main identifier becomes the primary key.

• Unselected candidate keys are called alternate keys.

• A composite key contains two or more columns.

• A foreign key creates and enforces a relationship between tables.

• A primary or unique key can be composite.

• A foreign key can contain duplicate values because many child rows may reference one parent.

• A foreign key does not automatically create an index in SQL Server; indexing it is often beneficial for joins and parent-row updates or deletions.

• Primary keys are not automatically IDENTITY; IDENTITY must be configured separately.

EmployeeId INT IDENTITY(1,1) PRIMARY KEY

Interview answer

A primary key is the candidate key selected to uniquely identify each row. A unique key enforces uniqueness on other candidate columns. A candidate key is any minimal possible unique identifier. A composite key consists of multiple columns, while a foreign key references a primary or unique key to enforce relationships and referential integrity between tables.