SQL Server Interview Question #35

What is a surrogate key?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

A surrogate key is an artificial identifier created specifically to identify a database row rather than representing business meaning. Common examples are an INT IDENTITY column or a UNIQUEIDENTIFIER.

Surrogate keys are often stable even when business attributes change. They can simplify relationships and foreign keys, particularly when the natural business key is long or consists of multiple columns.

However, business uniqueness requirements should still be enforced separately with UNIQUE constraints where necessary.

Code Example

CREATE TABLE Customers
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerNumber NVARCHAR(30) NOT NULL UNIQUE,
    Name NVARCHAR(150) NOT NULL
);