Primary key vs unique constraint, candidate keys, foreign keys, and constraint design

Overview

Primary keys, unique constraints, candidate keys, foreign keys, and constraint design are about making relational data trustworthy. These constraints tell the database which rows are unique, which business values must not duplicate, and which child rows must reference valid parent rows.

A primary key is the chosen main identifier for a table. A unique constraint enforces another uniqueness rule, often a business key such as email, SKU, tenant code, or external reference number. A candidate key is any column or set of columns that could uniquely identify a row. A foreign key enforces a relationship from a child table to a candidate key in a parent table.

This topic matters because many production bugs are not caused by missing SQL syntax knowledge. They are caused by weak schema contracts: duplicate users, orphan orders, optional relationships modeled as required, required relationships modeled as nullable, soft-delete uniqueness bugs, or foreign keys that exist but are not indexed.

For interviews, strong candidates can explain more than "primary key is unique." They can describe why a table has one primary key but may have many unique constraints, why foreign keys reference primary or unique keys, why SQL Server does not automatically index foreign key columns, and how constraint choices affect integrity, performance, migrations, and API behavior.

Core Concepts

Primary Key

A primary key identifies each row in a table. In SQL Server, a table can have only one primary key constraint. The primary key columns must be unique and not nullable.

CREATE TABLE dbo.Customers
(
    CustomerId BIGINT IDENTITY(1, 1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    DisplayName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

The primary key is commonly used by:

• Foreign keys from child tables.
• Joins.
• API routes and internal references.
• ORM identity tracking.
• Clustered or nonclustered indexes, depending on design.

The primary key should be stable, unique, and always present. It should not change just because a business value changes.

Unique Constraint

A unique constraint enforces that values in one column or combination of columns do not repeat.

ALTER TABLE dbo.Customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

Use unique constraints for business uniqueness rules:

• Email must be unique.
• SKU must be unique.
• Username must be unique.
• Tenant slug must be unique.
• External provider and external subject must be unique together.

CREATE TABLE dbo.Users
(
    UserId BIGINT IDENTITY(1, 1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    ExternalProvider NVARCHAR(50) NOT NULL,
    ExternalSubject NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Users PRIMARY KEY (UserId),
    CONSTRAINT UQ_Users_Email UNIQUE (Email),
    CONSTRAINT UQ_Users_ExternalIdentity
        UNIQUE (ExternalProvider, ExternalSubject)
);

A table can have many unique constraints. This is how a table can protect multiple candidate keys while still choosing one primary key.

Primary Key vs Unique Constraint

Both primary keys and unique constraints enforce uniqueness. The differences are design intent and restrictions.

Example design:

CREATE TABLE dbo.Products
(
    ProductId BIGINT IDENTITY(1, 1) NOT NULL,
    Sku NVARCHAR(50) NOT NULL,
    Name NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku)
);

ProductId is the main row identity. Sku is still a real business key and must also be protected.

Candidate Key

A candidate key is any column or combination of columns that can uniquely identify a row. A table can have multiple candidate keys.

Example candidates for Users:

• UserId
• Email
• (ExternalProvider, ExternalSubject)

One candidate becomes the primary key. Other candidate keys should usually be enforced with unique constraints or unique indexes.

If you use a surrogate primary key but forget unique constraints on business keys, the database can accept duplicate business records:

-- Bad schema: duplicate email is possible.
CREATE TABLE dbo.Users
(
    UserId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL
);

Better:

CREATE TABLE dbo.Users
(
    UserId BIGINT IDENTITY(1, 1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_Users PRIMARY KEY (UserId),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

Natural Key vs Surrogate Key

A natural key is based on real business data. A surrogate key is generated by the system and has no business meaning.

Natural key example:

CONSTRAINT PK_Countries PRIMARY KEY (CountryCode)

Surrogate key example:

CountryId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY

Natural keys can be good when they are stable, short, and truly unique. Surrogate keys are common when the business value can change, is long, is composite, or comes from an external system.

Practical design often uses both:

CREATE TABLE dbo.Countries
(
    CountryId INT IDENTITY(1, 1) NOT NULL,
    CountryCode CHAR(2) NOT NULL,
    Name NVARCHAR(100) NOT NULL,

    CONSTRAINT PK_Countries PRIMARY KEY (CountryId),
    CONSTRAINT UQ_Countries_CountryCode UNIQUE (CountryCode)
);

The surrogate key makes foreign keys simple. The unique constraint protects the real business code.

Composite Keys

A composite key uses multiple columns.

CREATE TABLE dbo.StudentCourses
(
    StudentId BIGINT NOT NULL,
    CourseId BIGINT NOT NULL,
    EnrolledAtUtc DATETIME2(3) NOT NULL,

    CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId)
);

Composite keys are useful for:

• Many-to-many join tables.
• Multi-tenant uniqueness such as (TenantId, Slug).
• Natural keys that are only unique in combination.

Trade-offs:

• They make foreign keys more verbose.
• They can make indexes wider.
• They may complicate ORM mapping and API URLs.
• They can be the clearest model when the combination is the real identity.

Foreign Key

A foreign key enforces a relationship from a child table to a parent table.

CREATE TABLE dbo.Orders
(
    OrderId BIGINT IDENTITY(1, 1) NOT NULL,
    CustomerId BIGINT NOT NULL,
    OrderedAtUtc DATETIME2(3) NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES dbo.Customers(CustomerId)
);

This prevents inserting an order for a customer that does not exist.

Foreign keys enforce referential integrity:

• Child rows must reference valid parent rows.
• Parent deletes or key updates may be restricted.
• Optional relationships can be modeled with nullable foreign keys.
• Required relationships should use NOT NULL foreign key columns.

Foreign Keys Can Reference Unique Constraints

A foreign key does not have to reference only a primary key. It can reference a candidate key enforced by a primary key or unique constraint.

CREATE TABLE dbo.Products
(
    ProductId BIGINT IDENTITY(1, 1) NOT NULL,
    Sku NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku)
);

CREATE TABLE dbo.InventoryAdjustments
(
    AdjustmentId BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ProductSku NVARCHAR(50) NOT NULL,

    CONSTRAINT FK_InventoryAdjustments_Products_Sku
        FOREIGN KEY (ProductSku)
        REFERENCES dbo.Products(Sku)
);

This can be useful, but surrogate-key references are often simpler and more stable. Referencing natural keys means key changes have wider impact.

Nullable Foreign Keys

A nullable foreign key means the relationship is optional.

CREATE TABLE dbo.Orders
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    SalesPersonId BIGINT NULL,

    CONSTRAINT FK_Orders_SalesPeople
        FOREIGN KEY (SalesPersonId)
        REFERENCES dbo.SalesPeople(SalesPersonId)
);

If SalesPersonId is NULL, no parent row is required. If it is non-null, it must reference a valid salesperson.

Use nullable foreign keys only when the relationship is genuinely optional. If every order must have a customer, CustomerId should be NOT NULL.

Cascading Actions

Foreign keys can define what happens when a parent key is updated or deleted.

Common actions:

• NO ACTION: reject parent change if child rows exist.
• CASCADE: update or delete child rows when parent changes.
• SET NULL: set child foreign key values to null.
• SET DEFAULT: set child foreign key values to their default.

Example:

CREATE TABLE dbo.OrderLines
(
    OrderId BIGINT NOT NULL,
    LineNumber INT NOT NULL,
    ProductId BIGINT NOT NULL,

    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderId, LineNumber),
    CONSTRAINT FK_OrderLines_Orders
        FOREIGN KEY (OrderId)
        REFERENCES dbo.Orders(OrderId)
        ON DELETE CASCADE
);

Cascades are useful when child rows have no independent life without the parent. They are risky when accidental parent deletion would remove important history.

Constraint Design Principles

Good constraint design starts from business invariants:

• What identifies a row?
• What values must be unique?
• Which relationships are required?
• Which relationships are optional?
• What should happen when a parent row is deleted?
• Are soft-deleted rows included in uniqueness?
• Are keys tenant-scoped or globally unique?
• Can business keys change?

Example multi-tenant uniqueness:

CREATE TABLE dbo.Projects
(
    ProjectId BIGINT IDENTITY(1, 1) NOT NULL,
    TenantId BIGINT NOT NULL,
    Slug NVARCHAR(100) NOT NULL,
    Name NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Projects PRIMARY KEY (ProjectId),
    CONSTRAINT UQ_Projects_Tenant_Slug UNIQUE (TenantId, Slug)
);

Slug may repeat across tenants, but not within the same tenant.

Soft Delete and Unique Constraints

Soft delete marks rows as deleted instead of physically removing them.

DeletedAtUtc DATETIME2(3) NULL

If Email is unique, should a deleted user still reserve the email? The answer depends on the business.

If only active rows must be unique, use a filtered unique index in SQL Server:

CREATE UNIQUE INDEX UX_Users_Email_Active
ON dbo.Users(Email)
WHERE DeletedAtUtc IS NULL;

This is not the same as a normal unique constraint. It enforces conditional uniqueness for active rows only.

Indexing Foreign Keys

In SQL Server, creating a foreign key does not automatically create an index on the child foreign key columns.

Add indexes when foreign key columns are used for:

• Joins.
• Child lookups.
• Parent delete or update checks.
• Common filters.
• Cascading actions.

Example:

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);

Not every foreign key needs a separate index, especially on small tables, but important relationships should be reviewed. Missing foreign key indexes can cause slow joins and blocking during parent-row changes.

Constraints and Application Validation

Application validation gives fast user feedback. Database constraints protect the source of truth.

Example race condition:

• Request A checks whether email exists.
• Request B checks whether email exists.
• Both see no row.
• Both insert the same email.

Only a database unique constraint reliably prevents the duplicate.

Applications should handle constraint violations and translate them into meaningful API errors, such as conflict for duplicate business keys or bad request for invalid relationships.

Common Mistakes

Common mistakes include:

• Using a surrogate primary key but forgetting unique constraints on business keys.
• Treating a primary key and unique constraint as identical concepts.
• Making optional relationships required accidentally.
• Making required relationships nullable accidentally.
• Assuming SQL Server automatically indexes foreign key columns.
• Using cascade delete on important historical data without review.
• Using index keys instead of real constraints for documentation and integrity.
• Ignoring soft-delete uniqueness requirements.
• Using natural keys as foreign keys when they can change.
• Adding constraints to dirty existing data without a cleanup plan.

Best Practices

Best practices include:

• Choose a stable primary key.
• Enforce every real candidate key.
• Use unique constraints or filtered unique indexes for business uniqueness.
• Use foreign keys for important relationships.
• Make required foreign keys NOT NULL.
• Index foreign keys when they support joins, filters, or parent-row changes.
• Use cascading actions deliberately.
• Name constraints consistently.
• Validate existing data before adding constraints.
• Keep database constraints aligned with domain rules and API validation.