Entity Integrity, Keys, and Uniqueness

Learning outcomes

Entity integrity ensures that every row representing an entity is uniquely and reliably identifiable. Primary keys are central, but complete integrity also requires alternate-key uniqueness, non-null identifiers, and an understanding of how SQL treats nulls in unique constraints.

The primary key identifies each row

For a relation representing Asset:

PRIMARY KEY (asset_id)

the database guarantees that no two rows share the same asset_id and that the key is not null. This provides row identity for references and updates.

A surrogate key does not prove business uniqueness

This table allows duplicate real-world assets if no business uniqueness rule is present:

CREATE TABLE asset (    asset_id       INTEGER PRIMARY KEY,    manufacturer_id INTEGER,    serial_number  TEXT);

Rows 100 and 101 can have identical manufacturer and serial number. The database considers them distinct because asset_id differs.

Preserve alternate candidate keys

If manufacturer + serial number is guaranteed unique:

UNIQUE (manufacturer_id, serial_number)

This preserves the candidate key even when asset_id is the chosen primary key.

Composite uniqueness expresses scope

Suppose usernames are unique per tenant rather than globally:

UNIQUE (tenant_id, username)

This directly encodes the uniqueness scope. A global UNIQUE(username) would impose a stronger rule than the business requires.

Nulls and uniqueness require care

SQL implementations differ in details, but many databases permit multiple nulls under a standard unique constraint because null means unknown rather than equal to another null. If the business says “when serial_number is present it must be unique within manufacturer,” a partial/filtered unique index or database-specific strategy may be appropriate.

Required candidate keys

If an alternate candidate key truly identifies every entity instance, its columns should normally be NOT NULL in addition to unique. A nullable “candidate key” cannot identify rows where the value is absent.

Primary key stability

Primary keys should be stable because many foreign keys may reference them. Surrogate keys are often chosen specifically to avoid propagating changes in business identifiers. However, stability does not remove the need to validate business keys.

Do not use mutable names as keys

Company names, product descriptions, and person names can change and may not be unique. Using them as primary keys causes cascading updates and semantic ambiguity.

Generated IDs and import duplicates

Consider an external import:

INSERT INTO customer (customer_id, external_customer_code, legal_name)VALUES (...)

If external_customer_code is not constrained uniquely within its source system, replaying the import can create duplicate logical customers. A surrogate primary key alone does not make ingestion idempotent.

Source-system identifiers

For multi-source integration, use scoped uniqueness:

UNIQUE (source_system_id, external_customer_id)

The same external ID may legitimately appear in two different source systems.

Association entity integrity

For ArticleTag, the pair itself may be the primary key:

PRIMARY KEY (article_id, tag_id)

This prevents the same tag from being attached to the same article twice.

For WorkOrderAssignment, repeated assignments may be valid, so the key must match the row grain:

UNIQUE (work_order_id, technician_id, started_at)

Natural-key corrections

Suppose a serial number was entered incorrectly. Correcting it should not change asset_id if the physical asset remains the same entity. This illustrates why internal identity and business identifiers can be separate.

Duplicate detection before constraints

Adding a unique constraint to existing data may fail because duplicates already exist. Migration steps often include:

1. identify duplicates;
2. decide which records represent the same entity;
3. merge or correct references;
4. add the uniqueness constraint;
5. monitor future violations.

WorkshopHub key design

Practice: identify missing integrity

CREATE TABLE user_account (    id INTEGER PRIMARY KEY,    tenant_id INTEGER NOT NULL,    username TEXT,    email TEXT);

Summary and next lesson

Entity integrity depends on stable row identity and preserved business uniqueness. Surrogate IDs make references convenient but do not replace candidate keys. The next lesson connects entities together safely through referential integrity and deliberate cascading actions.

References

• E. F. Codd, “A Relational Model of Data for Large Shared Data Banks,” 1970.
• C. J. Date, Database Design and Relational Theory.
• PostgreSQL and SQLite documentation for primary and unique constraints.