Primary Keys and Unique Keys

Can a Table Have Multiple Primary or Unique Keys?

Short answer

• Primary key: A table can have only one primary key constraint.

• Unique key: A table can have multiple unique constraints.

• The single primary key can contain multiple columns, called a composite primary key.

1. Can a Table Have Multiple Primary Keys?

No. A table can have only one primary key constraint.

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

EmployeeId is the table’s primary key.

The following definition is invalid:

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

SQL Server returns an error because the table already has a primary key.

Why is only one primary key allowed?

The primary key represents the table’s main identity. Although several columns might uniquely identify a row, only one candidate key is selected as the primary key.

For example:

All three columns may uniquely identify an employee:

• EmployeeId

• EmployeeCode

• Email

These are candidate keys. However, only one is selected as the primary key:

EmployeeId → Primary key
EmployeeCode → Unique alternate key
Email → Unique alternate key

2. Can a Primary Key Contain Multiple Columns?

Yes. One primary key constraint can contain multiple columns. This is called a composite primary key.

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

    CONSTRAINT PK_OrderItems
        PRIMARY KEY (OrderId, ProductId)
);

The table still has only one primary key, but that key contains two columns:

Primary key = (OrderId, ProductId)

Example data

OrderId can repeat, and ProductId can repeat. However, their combination cannot repeat.

This fails:

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

The (101, 1) combination already exists.

Important distinction

Two primary keys                 ❌ Not allowed
One primary key with two columns ✅ Allowed

3. Can a Table Have Multiple Unique Keys?

Yes. A table can have multiple unique constraints.

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeCode VARCHAR(20) NOT NULL,
    Email VARCHAR(150) NOT NULL,
    PassportNumber VARCHAR(20),

    CONSTRAINT UQ_Employees_EmployeeCode
        UNIQUE (EmployeeCode),

    CONSTRAINT UQ_Employees_Email
        UNIQUE (Email),

    CONSTRAINT UQ_Employees_Passport
        UNIQUE (PassportNumber)
);

This table contains:

• One primary key: EmployeeId

• Unique key 1: EmployeeCode

• Unique key 2: Email

• Unique key 3: PassportNumber

Why are multiple unique keys needed?

A record may have several business values that must not be duplicated.

For an employee:

• EmployeeId is the main identifier.

• EmployeeCode must be unique.

• Email must be unique.

• PassportNumber must be unique.

Only EmployeeId is the primary key, but the other fields still require uniqueness.

4. Can a Unique Key Contain Multiple Columns?

Yes. This is called a composite unique key or composite unique constraint.

Example: Course registration

CREATE TABLE StudentCourses
(
    RegistrationId INT PRIMARY KEY,
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    RegistrationDate DATE NOT NULL,

    CONSTRAINT UQ_StudentCourses
        UNIQUE (StudentId, CourseId)
);

This prevents a student from registering for the same course more than once.

These rows are valid:

INSERT INTO StudentCourses VALUES (1, 101, 10, '2026-09-17');
INSERT INTO StudentCourses VALUES (2, 101, 20, '2026-09-17');
INSERT INTO StudentCourses VALUES (3, 102, 10, '2026-09-17');

This fails:

INSERT INTO StudentCourses VALUES (4, 101, 10, '2026-09-18');

The (StudentId = 101, CourseId = 10) combination already exists.

Primary Key vs Multiple Unique Keys

The default index behavior can be explicitly changed:

PRIMARY KEY NONCLUSTERED (EmployeeId)

UNIQUE CLUSTERED (EmployeeCode)

Therefore, it is incorrect to say that a primary key is always clustered or a unique key is always nonclustered.

Practical Example

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1),

    CustomerCode VARCHAR(20) NOT NULL,
    Email VARCHAR(150) NOT NULL,
    CountryCode VARCHAR(5) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,

    CONSTRAINT PK_Customers
        PRIMARY KEY (CustomerId),

    CONSTRAINT UQ_Customers_CustomerCode
        UNIQUE (CustomerCode),

    CONSTRAINT UQ_Customers_Email
        UNIQUE (Email),

    CONSTRAINT UQ_Customers_Country_Phone
        UNIQUE (CountryCode, PhoneNumber)
);

Constraints in this table

The table has one primary key and three unique keys.

What Happens Behind the Scenes?

SQL Server creates a unique index to enforce each primary key or unique constraint.

For the previous table, SQL Server creates indexes similar to:

Unique index for CustomerId
Unique index for CustomerCode
Unique index for Email
Unique index for CountryCode + PhoneNumber

Because indexes consume storage and add work to INSERT, UPDATE, and DELETE operations, unique constraints should be created only when required by data-integrity or business rules.

Key Points

• A table can have only one primary key constraint.

• A primary key can contain one or multiple columns.

• A multi-column primary key is called a composite primary key.

• A table can have multiple unique constraints.

• A unique constraint can also contain multiple columns.

• Multiple possible unique identifiers are called candidate keys.

• One candidate key is chosen as the primary key.

• Other candidate keys are normally enforced through unique constraints.

• Primary keys cannot contain NULL.

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

• Primary and unique constraints create unique indexes internally.

Interview answer

A table can have only one primary key constraint, but that primary key may contain multiple columns, forming a composite primary key. A table can have multiple unique constraints, and each unique constraint can also contain one or more columns. The primary key is the main row identifier, while unique keys enforce additional business uniqueness.