Primary Keys and Unique Constraints

We saw in the preceding lessons that data normalization requires one or more columns to uniquely identify a row in a table. Only rarely would you design a table that has no primary key at all. When a primary key is defined, SQL Server enforces entity integrity by rejecting any insert or update that would duplicate an existing key value, or that would leave any primary-key column null.

In SQL Server 2025 (17.x), both PRIMARY KEY constraints and UNIQUE constraints enforce entity integrity the same basic way, by guaranteeing that no two rows share the same key value or values. They serve distinct logical roles in relational modeling, though, and SQL Server defaults each one to a different kind of physical storage underneath.

Feature / Behavior	PRIMARY KEY Constraint	UNIQUE Constraint
Logical purpose	Enforces the chosen candidate key, often a surrogate key like ProductID, that uniquely identifies every row.	Enforces alternate keys, typically natural business keys like SKU, SSN, or Email, that must also stay unique.
Maximum per table	1 per table (single-column, or a composite key of up to 32 columns).	Up to 999 per table, limited only by the maximum number of nonclustered indexes a table can hold.
NULL allowance	Strictly forbidden; every participating column must be NOT NULL.	Allowed, but NULL is treated as a value for uniqueness purposes, so a single-column UNIQUE constraint still permits at most one NULL row.
Default index type	A UNIQUE CLUSTERED index, unless NONCLUSTERED is specified or a clustered index already exists on the table.	A UNIQUE NONCLUSTERED index, unless CLUSTERED is specified.
Foreign key target	The default target for a FOREIGN KEY reference; a bare REFERENCES Schema.Table with no column list points at the primary key.	Can also be referenced by a FOREIGN KEY, provided the column or columns are explicitly named in the REFERENCES clause.
SQL Server 2025 requirement	A single-column primary key is mandatory on any table that will carry a DiskANN vector index (CREATE VECTOR INDEX) or a native JSON index (CREATE JSON INDEX).	Cannot serve as the base row locator for a SQL Server 2025 vector or JSON index.

Relational databases are built around the idea of relating data, so it's critical for nearly every table to carry a unique identifier for each row. That identifier lets a second table reference a given record accurately, which is exactly what forms a relation between the two tables in the first place. This is quite different from the record-at-a-time model used by older mainframe environments and the ISAM databases (dBase, FoxPro, and the like) of the 1980s and early 1990s, where you opened one table, scanned it record by record, and mixed in data from a second table yourself, programmatically, whenever you needed it.

Don't confuse a primary key, which uniquely identifies each row within one table, with a GUID, which identifies something across all space and time. A GUID can certainly serve as a primary key, but it carries overhead that usually isn't justified when you're only dealing with the contents of a single table. The one place a GUID earns its keep as a primary key is in a database environment involving replicated or otherwise distributed data, where two independent systems need to generate new keys without colliding.

Choosing the right primary key does more than satisfy entity integrity in SQL Server 2025; it directly shapes physical disk layout, index size, and whether a table is even eligible for the newer indexing features:

1. Physical row ordering and B-tree clustering. When a primary key is created as CLUSTERED, which is the default, the leaf level of its B-tree index is the table's actual data pages. Rows are physically sorted on 8 KB pages by the primary key value, so range scans and ordered reads against that key are naturally fast.
2. The clustering key as the pointer for every other index. Every nonclustered index on a clustered table stores the primary key value in its leaf nodes as the row locator, or bookmark, that points back to the base row. A narrow, static, monotonically increasing primary key, an INT IDENTITY or BIGINT IDENTITY column, keeps every nonclustered index on that table smaller and faster, since each one is carrying that same compact pointer rather than a wide, mutable natural key.
3. Prerequisite for vector and JSON indexes. Both the DiskANN vector index (CREATE VECTOR INDEX) and the native JSON index (CREATE JSON INDEX) introduced in SQL Server 2025 require the base table to have a single-column, clustered primary key, an INT or BIGINT column works well, so the engine's internal graph and auxiliary structures can map efficiently back to the base rows.
4. Fabric Mirroring and Change Event Streaming. SQL Server 2025 uses the primary key to track and replicate row-level UPDATE and DELETE operations into Microsoft Fabric OneLake and Azure Event Hubs, so a stable, well-chosen primary key also pays off downstream in analytics and eventing scenarios that have nothing to do with the original OLTP workload.

A candidate key is any set of one or more columns that could, on its own, uniquely identify a row in a table. Every candidate key has to satisfy two requirements: its values must be unique across the table, and none of its participating columns can allow NULL, since a NULL would immediately violate the uniqueness guarantee. During design, you identify every candidate key a table has, and then select exactly one of them to serve as the primary key; the rest remain candidate keys and are typically enforced with UNIQUE constraints instead.

Consider a table of employees with EmployeeID, SSN, and Email columns. All three are plausible candidate keys; each one, on its own, uniquely identifies an employee. A team would typically choose EmployeeID as the primary key, since a compact, surrogate, IDENTITY-based integer makes for a smaller and faster clustering key than a 9-digit SSN or a variable-length email address, while still enforcing uniqueness on SSN and Email as alternate keys:

CREATE TABLE HumanResources.Employees (
    EmployeeID INT IDENTITY(1,1) NOT NULL,
    SSN VARCHAR(11) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    EmployeeName VARCHAR(255) NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID),
    CONSTRAINT UQ_Employees_SSN UNIQUE (SSN),
    CONSTRAINT UQ_Employees_Email UNIQUE (Email)
);

Here, EmployeeID is the primary key, while SSN and Email are enforced as unique constraints, representing the table's other candidate keys. Identifying every candidate key up front, and only then choosing the best one as the primary key, is what keeps the rest of the design, indexing, foreign keys, and later query performance, built on a sound foundation rather than an arbitrary one.

A primary key doesn't have to be a single column. A composite primary key, one built from two or more columns together, is appropriate when no single column is unique on its own but a combination of them is, an order line table keyed on (OrderID, LineNumber), for example, where neither column alone identifies a row but the pair always does. SQL Server allows up to 32 columns in a composite primary key, though in practice a narrow composite key of two or three columns is far more common, and far friendlier to every nonclustered index that has to carry it as a row locator.

A table frequently uses a compact surrogate key, an EmployeeID INT IDENTITY column, say, as its primary key, purely for efficient joins and small indexes. The real-world entity behind that row, though, usually still carries natural business identifiers, a national ID number, a badge code, a username, that must never be duplicated either. A unique constraint enforces that second layer of uniqueness on those alternate keys. A unique constraint is similar to a primary key constraint, except that it doesn't have to uniquely identify a row all by itself, it simply disallows duplicate values for its column or columns, and, unlike a primary key, it can tolerate NULL. You can also define as many unique constraints on a table as you need, where a table can only ever have one primary key. When you create a unique constraint, SQL Server automatically backs it with a unique index, which is the actual mechanism that enforces it.

An SSN column is the classic example. It can't be the primary key, because it may genuinely be unknown at the time a row is first entered, and a primary key column can never be null. A unique constraint handles this correctly: it still enforces that no two non-null SSN values collide, while tolerating rows where the value isn't known yet.

Three purposes stand out for unique constraints in current SQL Server:

1. Protecting natural and alternate keys. They prevent a duplicate real-world entity from being inserted under a different surrogate key value, catching the mistake at the database layer rather than relying on application code to check for it first.
2. Giving the query optimizer a cardinality guarantee. When the optimizer sees a trusted UNIQUE constraint on a column used in a WHERE or JOIN clause, it knows at compile time that an equality lookup, WHERE SKU = 'ABC-100', can return at most one row. That lets it choose a lightweight singleton lookup or index seek instead of planning for multiple matches, and skip an unnecessary DISTINCT or GROUP BY sort that would otherwise exist purely to deduplicate rows the constraint already guarantees are unique.
3. Handling a nullable column that still needs to be unique when populated. A plain UNIQUE constraint allows only one NULL value total, because SQL Server indexes NULL as if it were a literal value. If the business rule is "PhoneNumber must be unique when provided, but any number of customers can have an unknown PhoneNumber," pair the constraint with a filtered unique nonclustered index instead, one built WHERE PhoneNumber IS NOT NULL, which lets every NULL row through while still catching real duplicates among the rows that do have a value.

Both kinds of constraint can also be added after a table already exists, which matters in practice since most tables aren't designed perfectly on the first try. The syntax is the same ALTER TABLE ... ADD CONSTRAINT pattern for either one:

ALTER TABLE HumanResources.Employees
    ADD CONSTRAINT UQ_Employees_BadgeCode UNIQUE (BadgeCode);

ALTER TABLE HumanResources.Employees
    ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

There's one practical catch worth knowing about either statement: SQL Server has to validate the existing data before it will accept the new constraint. Adding a primary key or unique constraint to a table that already contains duplicate values, or null values in the case of a primary key, fails immediately with a constraint violation error, and the table is left exactly as it was. Cleaning up the offending rows, typically by finding and resolving the duplicates with a GROUP BY ... HAVING COUNT(*) > 1 query first, has to happen before the constraint can be added successfully.

The script below defines a primary key, a single-column unique constraint, a composite unique constraint, and a filtered unique index for a nullable column that still needs enforced uniqueness once populated, and layers in two of SQL Server 2025's native data types along the way:

CREATE TABLE Catalog.Articles (
    -- Surrogate primary key, single-column, clustered by default.
    -- Required by SQL Server 2025 to support DiskANN vector indexes and JSON indexes.
    ArticleID INT IDENTITY(1,1)
        CONSTRAINT PK_Articles PRIMARY KEY CLUSTERED,

    -- Single-column unique constraint: an alternate natural key.
    Slug VARCHAR(150) NOT NULL
        CONSTRAINT UQ_Articles_Slug UNIQUE NONCLUSTERED,

    -- Columns participating in a composite unique constraint.
    PublicationID INT NOT NULL,
    IssueNumber INT NOT NULL,

    -- Nullable column where multiple NULLs should be allowed.
    DOI VARCHAR(100) NULL,

    -- SQL Server 2025 native JSON and vector columns.
    Metadata JSON NOT NULL,
    ContentEmbedding VECTOR(1536) NULL,

    -- IssueNumber can repeat across the table, but never within the same PublicationID.
    CONSTRAINT UQ_Articles_Publication_Issue UNIQUE NONCLUSTERED (PublicationID, IssueNumber)
);
GO

-- Enforces uniqueness only where DOI is populated, allowing any number of NULL rows.
CREATE UNIQUE NONCLUSTERED INDEX UIX_Articles_DOI_NotNull
ON Catalog.Articles(DOI)
WHERE DOI IS NOT NULL;
GO

-- Native JSON index; relies on the table's single-column primary key.
CREATE JSON INDEX JIX_Articles_Metadata
ON Catalog.Articles(Metadata) FOR ('$');
GO

Notice that PublicationID and IssueNumber are each unremarkable on their own, issue numbers restart every year, and plenty of publications share the same issue number, but the composite constraint guarantees the pair never repeats, which is exactly the shape of rule a single-column constraint can't express.

Earlier versions of SQL Server enforced a primary key and its sibling unique constraints by holding exclusive key locks across the base table and every unique nonclustered B-tree for the full duration of the transaction. SQL Server 2025's Optimized Locking changes that mechanism:

• Every row carries a Transaction ID (TID) identifying whichever transaction last modified it.
• When an INSERT or UPDATE validates uniqueness against a primary key or unique constraint's B-tree, the engine takes an exclusive lock on that row's TID and releases the ordinary page or key lock as soon as the write itself completes.
• If a second, concurrent session tries to insert the same key value before the first transaction commits, it detects the active TID on that index record and waits specifically on that transaction, rather than on a broader page or key lock that could block unrelated activity on the same page.

The practical effect is that uniqueness is still enforced exactly as strictly as before, while the lock manager's memory footprint and contention under heavy concurrent write workloads both drop substantially, a meaningful win for tables with a hot primary key range, an ever-increasing IDENTITY column under high insert volume, for instance, where many sessions are inserting near the same end of the index at once.

Now that you've learned how to enforce entity integrity with primary keys and unique constraints, in the next lesson you will learn how to enforce relational integrity with foreign keys.