Primary Keys, Foreign Keys, and Constraints

Primary keys, foreign keys, and constraints are how PostgreSQL turns a relational design into rules the database can enforce. Application code can validate forms and shape requests, but constraints protect the stored data itself. If two clients write at the same time, if a migration imports old data, or if an operator runs a manual statement, PostgreSQL still checks the declared rules before accepting the row.

In the relational design section of this PostgreSQL course, these features connect logical modeling to physical behavior. A primary key identifies one row. A foreign key connects one table to another. Other constraints describe valid values and combinations of values. By the end of this lesson, you should be able to choose the right constraint, predict when PostgreSQL checks it, read the error when it fails, and design tables whose relationships stay consistent as the database changes.

Purpose and Outcome

A well-designed table answers three questions. What makes a row distinct? Which other rows may it depend on? Which values are impossible for this domain? PostgreSQL answers those questions with constraints stored in the system catalog and enforced during inserts, updates, deletes, and some schema changes.

The outcome is not just cleaner DDL. It is a database that rejects impossible states: two customers with the same account number, an order pointing at a missing customer, a negative quantity, or a shipment whose status is not part of the agreed vocabulary. These are relational invariants. When they belong in the database, every client shares the same rules.

How PostgreSQL Enforces the Rules

A primary key is a table constraint that requires a column or group of columns to be both unique and not null. PostgreSQL implements that uniqueness with a unique B-tree index. The index is not only a lookup structure; it is also the mechanism that lets PostgreSQL detect duplicate key values efficiently while concurrent transactions are running.

A foreign key says that values in one table must match candidate key values in another table. The referencing table is often called the child table, and the referenced table is the parent table. PostgreSQL enforces foreign keys with internal triggers. When a child row is inserted or updated, PostgreSQL checks that the parent key exists. When a parent key is updated or deleted, PostgreSQL checks the child rows and applies the configured action, such as rejecting the change, cascading the change, setting the child column to null, or setting it to its default value.

A unique constraint prevents duplicate values but, unlike a primary key, it does not automatically make the columns the table identity and it allows nulls unless the columns are also declared not null. A check constraint evaluates a Boolean expression for each row. A not null constraint rejects missing values. An exclusion constraint, used with indexes such as GiST, prevents conflicting rows such as overlapping reservations. This lesson focuses on primary keys, foreign keys, unique constraints, check constraints, and not null constraints because they form the daily vocabulary of relational design.

Constraint timing matters. Most constraints are checked immediately at the end of each statement. Some foreign keys and unique constraints can be declared deferrable, which allows PostgreSQL to check them at transaction commit instead. Deferrable constraints are useful when two valid rows must be rearranged through a temporary state that would otherwise fail, but they make errors appear later and can complicate troubleshooting.

Syntax Anatomy

Constraints can be declared inline on one column or at table level. Column syntax is compact for simple not null, check, primary key, and references clauses. Table syntax is clearer for composite keys, named constraints, and constraints involving multiple columns. Naming constraints is worth the small effort because error messages and migration scripts become easier to understand.

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY,
    email text NOT NULL,
    status text NOT NULL DEFAULT 'active',
    CONSTRAINT customers_pkey PRIMARY KEY (customer_id),
    CONSTRAINT customers_email_key UNIQUE (email),
    CONSTRAINT customers_status_check CHECK (status IN ('active', 'suspended', 'closed'))
);

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY,
    customer_id bigint NOT NULL,
    order_number text NOT NULL,
    total_cents integer NOT NULL,
    CONSTRAINT orders_pkey PRIMARY KEY (order_id),
    CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT orders_order_number_key UNIQUE (order_number),
    CONSTRAINT orders_total_cents_check CHECK (total_cents BETWEEN 0 AND 100000000)
);

The identity column asks PostgreSQL to generate numeric values. The primary key constraint makes those values the row identity. The foreign key in orders points to customers(customer_id). ON DELETE RESTRICT means a customer with orders cannot be deleted until the dependent rows are handled deliberately. The check constraint keeps totals in a realistic nonnegative range.

Example 1: Identity and Uniqueness

The first example isolates primary keys and unique constraints. A primary key is the stable row identifier used by relationships. A unique email is a business rule, but it is not the row identity because emails can change.

INSERT INTO customers (email, status)
VALUES ('ada@example.com', 'active')
RETURNING customer_id, email, status;

INSERT INTO customers (email, status)
VALUES ('ada@example.com', 'active');

The first statement returns one generated customer_id with the supplied email and status. The exact identifier depends on the sequence state, but the shape is deterministic: one row is inserted and returned. The second statement fails with a unique-violation error naming customers_email_key. PostgreSQL rejects the duplicate before the table can contain two active rows with the same email.

Example 2: Relationship Integrity

The second example shows the foreign key. A child row may reference only an existing parent row. This is stronger than a join convention; PostgreSQL refuses the write if the relationship would be broken.

INSERT INTO orders (customer_id, order_number, total_cents)
VALUES (1, 'ORD-1001', 2599)
RETURNING order_number, total_cents;

INSERT INTO orders (customer_id, order_number, total_cents)
VALUES (999999, 'ORD-1002', 5000);

If customer 1 exists, the first insert succeeds and returns ORD-1001 with 2599. The second insert fails unless a customer with ID 999999 exists. The error identifies orders_customer_fk and reports that the key is not present in the referenced table. That message tells you the child row is invalid, not that the parent table is locked or unavailable.

Example 3: Composite Rules

Real designs often need rules across more than one column. The next table stores one membership per user per workspace. The surrogate key makes individual rows easy to reference, while the unique constraint models the domain rule that the pair must not repeat.

CREATE TABLE workspace_memberships (
    membership_id bigint GENERATED ALWAYS AS IDENTITY,
    workspace_id bigint NOT NULL,
    user_id bigint NOT NULL,
    role text NOT NULL,
    CONSTRAINT workspace_memberships_pkey PRIMARY KEY (membership_id),
    CONSTRAINT workspace_memberships_pair_key UNIQUE (workspace_id, user_id),
    CONSTRAINT workspace_memberships_role_check CHECK (role IN ('owner', 'admin', 'member'))
);

INSERT INTO workspace_memberships (workspace_id, user_id, role)
VALUES (10, 42, 'owner');

INSERT INTO workspace_memberships (workspace_id, user_id, role)
VALUES (10, 42, 'member');

The first membership succeeds. The second fails because (workspace_id, user_id) already exists, even though membership_id would be different. This is a common design choice: use a generated primary key for references and auditing, then add a unique constraint for the natural domain rule.

Example 4: Deferred Relationship Checks

Most tables should use immediate constraints. Deferrable foreign keys are useful when a transaction must temporarily pass through an order that looks invalid statement by statement but is valid at commit. The constraint is still enforced; the check is delayed.

CREATE TABLE departments (
    department_id bigint PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE employees (
    employee_id bigint PRIMARY KEY,
    department_id bigint NOT NULL,
    CONSTRAINT employees_department_fk FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
        DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO employees (employee_id, department_id) VALUES (7, 30);
INSERT INTO departments (department_id, name) VALUES (30, 'Support');
COMMIT;

This transaction commits because the missing department exists by the time PostgreSQL checks the deferred foreign key. If the second insert is omitted, COMMIT fails and the transaction rolls back. The trade-off is that the failing statement may not be the line where the invalid reference first appeared.

Design Choices and Trade-Offs

Choose primary keys for stability, narrowness, and lack of meaning. A generated bigint identity key is compact and efficient. A natural key, such as an ISO code or externally assigned account number, can be appropriate when it is truly stable and already unique. Avoid primary keys whose values may need correction, contain personal data, or encode multiple meanings.

Foreign key actions should match ownership. ON DELETE RESTRICT is safer when child rows have business value, such as orders or invoices. ON DELETE CASCADE is useful for rows that exist only as details of the parent, such as order line items. ON DELETE SET NULL fits optional relationships, but only when the child column allows nulls and the application has a clear meaning for an unassigned reference.

Indexing is another important choice. PostgreSQL automatically creates an index for primary key and unique constraints. It does not automatically index the referencing side of a foreign key. Add an index on child foreign key columns when parent deletes or updates must check many child rows, or when joins commonly filter by that foreign key. Without it, deletes from the parent can be slow because PostgreSQL must search the child table for dependent rows.

Failure Modes and Troubleshooting

Symptom: an insert fails with duplicate key value violates unique constraint. Cause: the proposed primary key or unique value already exists, or an identity sequence was manually set behind the table’s current maximum value. Diagnosis: query the conflicting value and inspect the named constraint. For identity sequence drift, compare the maximum key to the sequence’s next value. Correction: use the existing row, choose a new unique value, or reset the sequence to a value beyond the current maximum.

Symptom: an order insert fails with violates foreign key constraint. Cause: the parent customer row is missing, the wrong key was supplied, or the parent insert is in another uncommitted transaction. Diagnosis: run a direct select against the parent key in the same transaction context and confirm the referenced columns match the foreign key definition. Correction: insert the parent first, fix the referenced identifier, or commit the parent transaction before inserting the child.

Symptom: deleting a parent row hangs or is unexpectedly slow. Cause: PostgreSQL is checking the child table for dependent rows and there is no useful index on the child foreign key column, or another transaction is holding row locks. Diagnosis: inspect indexes on the child table and check active sessions waiting on locks. Correction: create an index on the referencing columns and resolve the blocking transaction according to local operational policy.

Symptom: a migration adding a constraint fails on an existing table. Cause: old rows violate the new rule. Diagnosis: write a select that finds rows outside the proposed rule, such as nulls, duplicates, or unmatched foreign keys. Correction: clean or backfill the data, then add the constraint. For large tables, add constraints in a way that minimizes blocking and validate after cleanup.

Security, Performance, and Reliability

Constraints improve reliability because they make invalid states unrepresentable in committed data. They also reduce security exposure caused by inconsistent authorization data. For example, a role assignment that references a missing user can lead to confusing access decisions. A foreign key makes that orphaned assignment impossible.

Constraints are not free. Unique checks and foreign key checks touch indexes and may wait on concurrent transactions. Check constraints evaluate expressions for each affected row. The cost is usually small compared with the cost of repairing corrupt relational data, but high-write systems should still measure insert and update paths with representative data. Good indexing, narrow keys, and clear ownership rules keep the cost predictable.

Hands-On Lab

Prerequisites: a PostgreSQL database you can create and drop tables in, plus a SQL client such as psql. Run the lab in a scratch schema or disposable database.

  1. Create the customer and order tables from the syntax section.
  2. Insert one customer: INSERT INTO customers (email) VALUES ('lab@example.com') RETURNING customer_id;
  3. Use the returned identifier to insert one order with a positive total.
  4. Try to insert a second customer with the same email and confirm PostgreSQL reports customers_email_key.
  5. Try to insert an order with a nonexistent customer_id and confirm PostgreSQL reports orders_customer_fk.
  6. Create an index on the child key with CREATE INDEX orders_customer_id_idx ON orders (customer_id);
  7. Verify the constraints by querying pg_constraint for the two table names.
SELECT conname, contype, conrelid::regclass AS table_name
FROM pg_constraint
WHERE conrelid IN ('customers'::regclass, 'orders'::regclass)
ORDER BY table_name::text, conname;

Verification should show primary key constraints with type p, unique constraints with type u, a foreign key with type f, and check constraints with type c. Cleanup is simple in a scratch database: drop the child table first, then the parent table.

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

Assessment Exercises

  1. A table has user_id, workspace_id, and role. Explain when you would use a composite primary key and when you would use a generated primary key plus a unique constraint.
  2. An application wants to delete customers and keep their historical invoices. Which foreign key action would you choose for invoices, and why?
  3. A migration adding UNIQUE (email) fails. Write the diagnostic query you would use to find duplicate emails before retrying the migration.
  4. Why does PostgreSQL create an index for a primary key but not automatically create one for the child side of every foreign key?
  5. Design a check constraint for an order status column and describe one future change that might make a lookup table a better design.

Summary

Primary keys define row identity, foreign keys preserve relationships, and constraints encode valid data rules where PostgreSQL can enforce them for every writer. Use generated or natural keys deliberately, name constraints, choose foreign key actions to match ownership, index child keys when relationship checks need it, and troubleshoot from the named constraint in the error message. Good relational design is not only about table shape; it is about making invalid database states difficult or impossible to commit.