Surrogate Keys: A Stable Identifier for Each Row

A surrogate key is an identifier introduced to identify a row, rather than derived from the business facts that row describes. For example, GameSessionID can identify a session independently of who played, when it started, or whether it has finished. Generated integers and UUIDs are common implementations.

A natural key uses an identifier or combination already meaningful in the business domain. Either kind can be chosen as the primary key. A composite key^[1] contains multiple columns; that describes its structure, not whether it is natural or surrogate. Combining existing business values into a string does not automatically make them a surrogate key.

Decide what a row represents before selecting its identifier. The logical schema can include a surrogate identifier; the physical design then chooses its data type, generation mechanism, and indexes.

• PersonID repeats. It identifies the person, not the session.
• EndedAt is unavailable at insertion. A primary key cannot depend on a value that remains NULL until the session finishes.
• Distinct sample times do not prove uniqueness. A valid candidate key must follow from a rule that applies to all permitted rows.

The pair (PersonID, StartedAt) could be a candidate key if the application guarantees that one person can start at most one session at a recorded instant. That is an explicit assumption, not something inferred from these three rows. If concurrent sessions or timestamp collisions are legitimate, the pair is not unique and must not be constrained as though it were.

Date-time columns are not inherently unsuitable as keys. Here, a separate identifier is useful because start times may need correction and references to a session should remain stable. Correcting a descriptive timestamp should not require changing every reference to the session.

Assign GameSessionID when the row is created. The sample sessions receive IDs 1001, 1002, and 1003. Session 1003 already has an identity while it is in progress. Its ID remains the same when EndedAt and Outcome are filled in.

Another table, such as SessionEvent, can reference GameSessionID without copying PersonID and the start timestamp into its foreign key. This simplifies references, but does not eliminate the need to validate the session's business data.

CREATE TABLE dbo.Person (
    PersonID INT NOT NULL PRIMARY KEY,
    DisplayName NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.GameSession (
    GameSessionID BIGINT IDENTITY(1001, 1) NOT NULL,
    PersonID INT NOT NULL,
    StartedAt DATETIME2(0) NOT NULL,
    EndedAt DATETIME2(0) NULL,
    PlayArea NVARCHAR(100) NOT NULL,
    Outcome VARCHAR(10) NULL,
    CONSTRAINT PK_GameSession PRIMARY KEY (GameSessionID),
    CONSTRAINT FK_GameSession_Person FOREIGN KEY (PersonID)
        REFERENCES dbo.Person (PersonID),
    CONSTRAINT UQ_GameSession_PersonStart UNIQUE (PersonID, StartedAt),
    CONSTRAINT CK_GameSession_Time CHECK (
        EndedAt IS NULL OR EndedAt >= StartedAt
    ),
    CONSTRAINT CK_GameSession_Completion CHECK (
        (EndedAt IS NULL AND Outcome IS NULL)
        OR
        (EndedAt IS NOT NULL AND Outcome IS NOT NULL
         AND Outcome IN ('Win', 'Loss', 'Draw'))
    )
);

Suppose an application accidentally submits the first session twice. A generator could give the second insert a different GameSessionID even though PersonID and StartedAt are identical. The primary key alone would accept two distinct IDs. In this design, UQ_GameSession_PersonStart rejects the duplicate pair instead.

What each rule accepts or rejects

Attempt	Expected result
A new session for person 1 at a different start time	Allowed if all other constraints are satisfied
A second row for person 1 at the same StartedAt	Rejected by UNIQUE, even with a different session ID
A session with both EndedAt and Outcome NULL	Allowed: the session is in progress
An end time earlier than StartedAt	Rejected by the time CHECK
A PersonID absent from Person	Rejected by the foreign key

Enforce a natural or business key when a valid one exists. Do not invent one merely because a surrogate key has been introduced. If the source system supplies an event identifier or retry token, that may provide a better duplicate-detection rule. Names and birth dates are not reliable substitutes for a person's identifier.

A format CHECK does not enforce uniqueness across rows. A trigger that simply checks whether a matching row exists can also race with another transaction. Prefer declarative unique constraints for rules they can express; more complex rules require deliberate concurrency handling.

• Generated integer: identity or sequence mechanisms can supply compact identifiers. Keep a PRIMARY KEY or UNIQUE constraint; the generator and the constraint serve different purposes.
• UUID: useful when independently operating systems need to generate identifiers. Storage, index locality, generation method, and collision handling still need consideration. It is not inherently better or worse than an integer for every workload.
• Concurrency: use a supported generator rather than computing MAX(ID) + 1, which can give concurrent transactions the same proposed value.
• Gaps: generated numbers need not be consecutive. Failed or rolled-back operations can leave gaps; an ID is not a row count or a gap-free document number.
• Stability: avoid changing or recycling identifiers as routine cleanup. Old references, logs, and external systems may still contain them.
• Performance: a narrow key can reduce index and foreign-key storage, but performance depends on the workload and index design. Adding a surrogate often also requires another unique index to preserve a business key.

A stable natural key can be a sound primary key. A surrogate is helpful when business identifiers are absent, wide, mutable, or awkward to reference. Evaluate both choices instead of assuming every table must use the same strategy.

[1] Composite key: A key containing more than one attribute. The term does not imply string concatenation and does not by itself identify the key as natural or surrogate.