Table Constraints — Define constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE) (intro) - SQL

SECTION 1

Historical Context & Motivation

Before relational databases existed, programmers bore the full burden of enforcing data correctness in application code. Flat-file systems and hierarchical databases offered minimal guarantees about duplicate records or referential consistency, meaning that a single careless insertion could silently corrupt an entire dataset. The emergence of the relational model in the early 1970s introduced a fundamentally different philosophy: push integrity rules into the database itself through declarative constraints so that no application—regardless of language, framework, or developer discipline—could violate the structural invariants of the data.

1970 • Codd's Relational Model

Edgar F. Codd publishes A Relational Model of Data for Large Shared Data Banks, introducing the concepts of relations, keys, and normalization. The notion that each relation should have a unique identifier—later formalized as the primary key—is central from the beginning.

1974 • System R and SEQUEL

IBM's System R prototype implements the first SQL predecessor (SEQUEL), including early support for key constraints and referential integrity checks within the storage engine itself.

1986 • SQL-86 Standard (ANSI)

The first ANSI SQL standard formalizes PRIMARY KEY, UNIQUE, and FOREIGN KEY as part of the Data Definition Language (DDL), establishing a vendor-neutral syntax for constraint declaration.

1992 • SQL-92 Enhancements

SQL-92 extends referential actions with ON DELETE CASCADE and ON UPDATE SET NULL, giving developers fine-grained control over how constraint violations are resolved automatically.

2000s+ • Modern RDBMS Maturity

Systems like PostgreSQL, MySQL (InnoDB), Oracle, and SQL Server implement robust constraint enforcement with deferred checking, partial unique indexes, and composite foreign keys—constraints become a cornerstone of production schema design.

The central question these decades of development address is deceptively simple: how do we guarantee that the data stored in a relational database faithfully represents the real-world entities and relationships it models? Constraints are the answer—rules declared once in the schema and enforced automatically by the DBMS on every INSERT, UPDATE, and DELETE.

SECTION 2

Core Principles & Definitions

A table constraint is a declarative rule attached to a table's schema that the database management system enforces at the storage-engine level. Rather than scattering validation logic across application tiers, constraints centralize integrity guarantees inside the database, ensuring that every client—whether a web application, a batch script, or an interactive SQL session—operates against data that satisfies the same invariants. The three foundational constraint types introduced in this lesson each address a distinct category of integrity.

PRIMARY KEY

Uniquely identifies every row in a table. Combines entity integrity (no NULLs allowed) with uniqueness. Each table may declare at most one primary key, which may span one or more columns (composite key).

FOREIGN KEY

Enforces referential integrity by requiring that values in the referencing column(s) match an existing value in the referenced table's primary key or unique column(s). Prevents orphaned rows.

UNIQUE

Guarantees that all values in the constrained column(s) are distinct across the table. Unlike PRIMARY KEY, a UNIQUE constraint permits NULLs (behavior varies slightly by RDBMS) and a table may have multiple UNIQUE constraints.

Declarative vs. Procedural

Constraints are declarative: you state what must hold, not how to enforce it. The DBMS chooses indexes, locks, and validation strategies automatically.

✦

KEY TAKEAWAY

Think of constraints as the structural steel in a building. Application-level validations are like interior walls—useful for directing traffic, but if they fail, people can still walk into unsafe areas. Constraints, on the other hand, are load-bearing beams: they are embedded in the structure itself and cannot be bypassed no matter which door someone uses to enter. A PRIMARY KEY is the building's unique address, a FOREIGN KEY is the wiring that connects rooms to the correct circuit panel, and a UNIQUE constraint ensures no two rooms share the same number.

SECTION 3

Visual Explanation — Entity & Referential Integrity

Three tables illustrate the constraint types. The key icon (🔑) marks PRIMARY KEY columns, the diamond (◆) marks UNIQUE columns, and the pink arrows depict FOREIGN KEY references from child to parent tables.

In the diagram above, students.student_id serves as the primary key—no two students may share the same ID, and no row may leave this column NULL. The email column carries a UNIQUE constraint, enforcing that while email is not the row's primary identifier, each student still possesses a distinct email address. Meanwhile, the enrollments table contains a FOREIGN KEY on student_id that points back to students.student_id. This referential link prevents an enrollment record from citing a student who does not exist—an attempt to INSERT student_id = 9999 when no such student exists would be rejected by the DBMS immediately.

SECTION 4

How Constraints Work Under the Hood

When you declare a constraint in a CREATE TABLE statement, the DBMS registers the rule in its system catalog (also called the information schema or data dictionary). Every subsequent DML operation—INSERT, UPDATE, or DELETE—triggers a validation check against the relevant constraints before the modification is committed. If a violation is detected, the DBMS raises an error and the transaction is rolled back or the statement is rejected, depending on the isolation level and autocommit settings.

PRIMARY KEY Enforcement

Declaring a PRIMARY KEY implicitly creates a unique B-tree index on the specified column(s). On INSERT, the engine performs an index lookup in O(log n) time to verify uniqueness. Additionally, a NOT NULL check is applied to each column in the key before the row is written to disk. In many systems (e.g., InnoDB in MySQL), the primary key also determines the clustered index, meaning the physical row order on disk follows the primary key order—a detail with significant performance implications for range queries.

FOREIGN KEY Enforcement

A FOREIGN KEY constraint triggers cross-table lookups. When a row is inserted into the child table (or an FK column is updated), the engine verifies that the referenced value exists in the parent table's PRIMARY KEY or UNIQUE index. Conversely, when a row in the parent table is deleted or its key is updated, the engine checks whether any child rows reference the old value. The behavior upon finding a match is governed by referential actions:

Action Clause	On DELETE Behavior	On UPDATE Behavior
`RESTRICT / NO ACTION`	Reject the DELETE if child rows exist (default)	Reject the UPDATE if child rows reference the old value
`CASCADE`	Delete all matching child rows automatically	Propagate the new key value to all child rows
`SET NULL`	Set the FK column in child rows to NULL	Set the FK column in child rows to NULL
`SET DEFAULT`	Set the FK column to its default value	Set the FK column to its default value

Referential actions for FOREIGN KEY constraints

UNIQUE Enforcement

Like PRIMARY KEY, a UNIQUE constraint creates a unique index on the target column(s). The critical difference is that UNIQUE columns are permitted to contain NULL values. The SQL standard specifies that NULLs are not considered equal to one another for uniqueness purposes, so multiple NULLs are allowed in a UNIQUE column. However, this behavior is RDBMS-dependent—SQL Server's default UNIQUE index, for instance, permits only a single NULL unless a filtered index is used. A table may have any number of UNIQUE constraints, whereas only one PRIMARY KEY is allowed.

SECTION 5

DDL Syntax — Column-Level vs. Table-Level Constraints

SQL offers two syntactic positions for declaring constraints: column-level (inline with the column definition) and table-level (declared after all columns, at the end of the CREATE TABLE statement). Column-level syntax is concise and works well for single-column constraints, while table-level syntax is required for composite constraints that span multiple columns. Both approaches produce identical behavior at runtime; the choice is primarily one of readability and necessity.

The left panel demonstrates inline (column-level) constraint syntax, while the right panel shows the equivalent table-level declarations. Both produce identical schema behavior; table-level is mandatory for composite constraints spanning multiple columns.

💡

Naming Your Constraints

Always use the CONSTRAINT constraint_name prefix when declaring constraints (e.g., CONSTRAINT pk_students PRIMARY KEY (student_id)). If you omit the name, the DBMS auto-generates one like SYS_C007832, making later ALTER TABLE ... DROP CONSTRAINT operations unnecessarily difficult to write and debug.

SECTION 6

Worked Example — University Course Registration Schema

Let us design a small schema for a university course registration system. The domain has three entities: departments, instructors, and courses. We will declare PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints to enforce entity and referential integrity across these tables.

Building a Three-Table Schema with Constraints

1
Step 1 — Identify Entities and Keys

We identify three entities. Each department has a numeric ID and a unique name. Each instructor has an ID, belongs to exactly one department, and has a unique employee number. Each course has a code, title, credit count, and is taught by one instructor.

Primary keys: dept_id, instructor_id, course_code
2
Step 2 — Create the departments Table

We write the DDL for the parent table first since child tables will reference it. The dept_name column receives a UNIQUE constraint because no two departments should share a name: CREATE TABLE departments ( dept_id INT, dept_name VARCHAR(80) NOT NULL, building VARCHAR(50), CONSTRAINT pk_dept PRIMARY KEY (dept_id), CONSTRAINT uq_dept_name UNIQUE (dept_name) );

Table created with named PK and UNIQUE constraints.
3
Step 3 — Create the instructors Table with a Foreign Key

Instructors belong to departments, so dept_id is a FOREIGN KEY referencing departments(dept_id). We also declare employee_no as UNIQUE: CREATE TABLE instructors ( instructor_id INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, employee_no CHAR(10) NOT NULL, dept_id INT NOT NULL, CONSTRAINT pk_instr PRIMARY KEY (instructor_id), CONSTRAINT uq_emp_no UNIQUE (employee_no), CONSTRAINT fk_instr_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE RESTRICT ON UPDATE CASCADE );

The FK with ON UPDATE CASCADE means if a department's ID changes, all linked instructors update automatically.
4
Step 4 — Create the courses Table with a Foreign Key

Courses reference an instructor. Here we use a natural key (course_code like 'CS301') as the primary key instead of a surrogate integer, demonstrating that primary keys need not be auto-incrementing integers: CREATE TABLE courses ( course_code CHAR(8), title VARCHAR(120) NOT NULL, credits INT NOT NULL, instructor_id INT, CONSTRAINT pk_course PRIMARY KEY (course_code), CONSTRAINT fk_course_instr FOREIGN KEY (instructor_id) REFERENCES instructors (instructor_id) ON DELETE SET NULL );

ON DELETE SET NULL means if an instructor is removed, the course remains but its instructor_id becomes NULL (course temporarily unassigned).
5
Step 5 — Verify Constraint Enforcement

We can test referential integrity by attempting an INSERT that violates the FK: INSERT INTO instructors (instructor_id, first_name, last_name, employee_no, dept_id) VALUES (1, 'Ada', 'Lovelace', 'EMP0000001', 999); If dept_id = 999 does not exist in departments, the DBMS will return an error such as: ERROR: insert or update on table "instructors" violates foreign key constraint "fk_instr_dept".

Constraint enforcement confirmed — the invalid row is rejected, preserving referential integrity.

SECTION 7

Strengths, Limitations & Constraint Comparison

Characteristic	PRIMARY KEY	UNIQUE	FOREIGN KEY
Max per table	Exactly 1	Many	Many
Allows NULLs?	No	Yes (RDBMS-dependent)	Yes (NULL = no reference)
Creates index?	Unique index (often clustered)	Unique index (non-clustered)	Recommended (not always auto)
Composite support?	Yes	Yes	Yes
Referential actions?	N/A	N/A	CASCADE, SET NULL, RESTRICT, etc.
Integrity category	Entity integrity	Domain integrity	Referential integrity

Comparison of the three foundational table constraints

Limitations to Keep in Mind

Performance overhead: Every INSERT and UPDATE against a constrained column incurs an index lookup. On high-throughput write systems, poorly indexed foreign keys can become bottlenecks during bulk loads.
Circular references: If table A has a FK to table B and table B has a FK to table A, you must use deferred constraint checking or ALTER TABLE to add one FK after both tables are created.
Schema evolution friction: Dropping or modifying a column that participates in a constraint requires first dropping the constraint, a consideration that makes migration scripts more complex.
NULL semantics in UNIQUE: The handling of multiple NULLs in UNIQUE columns differs across PostgreSQL (allows multiples), SQL Server (forbids by default), and Oracle (allows multiples for single-column UNIQUE). Always test on your target RDBMS.

✦

KEY TAKEAWAY

Constraints are like unit tests for your data—but better, because they run on every single write operation and cannot be skipped by lazy callers. Just as a well-tested codebase catches regressions, a well-constrained schema catches data corruption before it propagates. The trade-off is write performance, which is why understanding indexing strategies and referential action choices matters as schemas scale.

SECTION 8

Connection to Advanced Constraint Concepts

The three constraints covered in this lesson—PRIMARY KEY, FOREIGN KEY, and UNIQUE—are the foundational trio, but SQL's constraint system extends considerably further. Understanding where these basics connect to advanced features will help you architect more robust schemas as your projects grow in complexity.

This Lesson (Intro)	Advanced Extension
`PRIMARY KEY (col)`	Composite primary keys: `PRIMARY KEY (col_a, col_b)` — used in junction/bridge tables for many-to-many relationships
`FOREIGN KEY ... REFERENCES`	Self-referencing FKs (e.g., `manager_id REFERENCES employees(emp_id)`), deferred constraint checking, and composite FKs
`UNIQUE (col)`	Partial/filtered unique indexes (PostgreSQL: `CREATE UNIQUE INDEX ... WHERE condition`) for conditional uniqueness
Column-level NOT NULL (implicit in PK)	`CHECK (expression)` constraints for domain validation (e.g., `CHECK (gpa >= 0.0 AND gpa <= 4.0)`)
Declarative constraints	Triggers and stored procedures for complex business rules that cannot be expressed declaratively

From introductory constraints to advanced integrity mechanisms

In subsequent lessons, you will encounter CHECK constraints that enforce arbitrary Boolean expressions on column values, ASSERTION constraints (defined in the SQL standard but rarely implemented by vendors) that span multiple tables, and EXCLUSION constraints (a PostgreSQL extension) that prevent overlapping ranges. These advanced mechanisms build directly on the declarative philosophy established by PRIMARY KEY, FOREIGN KEY, and UNIQUE—the difference is merely in the complexity of the predicate being enforced.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain the fundamental difference between a PRIMARY KEY constraint and a UNIQUE constraint. Under what circumstances would you use a UNIQUE constraint instead of making a column the primary key?

Show answer

A PRIMARY KEY enforces both uniqueness and NOT NULL, and a table may have at most one. A UNIQUE constraint enforces uniqueness but generally permits NULL values, and a table may have multiple UNIQUE constraints. You would use UNIQUE when a column must contain distinct values but is not the row's primary identifier—for example, an email column on a users table where a surrogate user_id already serves as the PK. The UNIQUE constraint on email prevents duplicates while the PK on user_id provides the stable, non-nullable identifier used in foreign key references.

Question 2: BASIC CALCULATION

Write a CREATE TABLE statement for a table called products with the following columns: product_id (INT, primary key), sku (CHAR(12), unique), name (VARCHAR(200), not null), and category_id (INT, foreign key referencing categories(category_id)). Use named table-level constraints.

Show answer

CREATE TABLE products (
  product_id   INT,
  sku          CHAR(12)      NOT NULL,
  name         VARCHAR(200)  NOT NULL,
  category_id  INT,
  CONSTRAINT pk_products PRIMARY KEY (product_id),
  CONSTRAINT uq_sku UNIQUE (sku),
  CONSTRAINT fk_prod_cat FOREIGN KEY (category_id)
    REFERENCES categories (category_id)
);

Note that we explicitly added NOT NULL to sku even though it has a UNIQUE constraint, because UNIQUE alone does not prevent NULLs. The constraint names follow a readable convention: pk_ for primary keys, uq_ for unique, and fk_ for foreign keys.

Question 3: INTERMEDIATE

Consider two tables: authors(author_id PK, name) and books(isbn PK, title, author_id FK → authors). The FK uses ON DELETE CASCADE. Describe exactly what happens in the database if you execute DELETE FROM authors WHERE author_id = 42; and there are five books with author_id = 42.

Show answer

When the DELETE statement is executed, the DBMS first removes the row from authors where author_id = 42. Because the FK on books.author_id specifies ON DELETE CASCADE, the engine automatically deletes all five rows in books that reference author 42. This cascade is atomic—if any part of the operation fails (e.g., a trigger on books raises an exception), the entire transaction rolls back, leaving both tables unchanged. CASCADE is powerful but dangerous: a single parent-row deletion can propagate through multiple levels of dependent tables, so it should be used judiciously.

Question 4: APPLIED

You are designing a schema for a hospital scheduling system. The table appointments must reference both a patients table and a doctors table. When a doctor leaves the hospital, existing appointments should have their doctor_id set to NULL so that they can be reassigned. When a patient is removed from the system, their appointments should be deleted. Write the CREATE TABLE appointments statement with appropriate constraints.

Show answer

CREATE TABLE appointments (
  appt_id      INT,
  patient_id   INT          NOT NULL,
  doctor_id    INT,
  appt_date    DATE         NOT NULL,
  appt_time    TIME         NOT NULL,
  reason       VARCHAR(500),
  CONSTRAINT pk_appt PRIMARY KEY (appt_id),
  CONSTRAINT fk_appt_patient FOREIGN KEY (patient_id)
    REFERENCES patients (patient_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_appt_doctor FOREIGN KEY (doctor_id)
    REFERENCES doctors (doctor_id)
    ON DELETE SET NULL
);

The patient_id FK uses CASCADE so that deleting a patient also removes their appointments. The doctor_id FK uses SET NULL so that appointments survive a doctor's departure—note that doctor_id must allow NULLs (no NOT NULL) for SET NULL to work. This models the real-world workflow where appointments need reassignment rather than deletion.

Question 5: CRITICAL THINKING

A colleague argues that foreign key constraints should be omitted in production databases because they slow down bulk INSERT operations, and that referential integrity should instead be enforced entirely in application code. Construct a detailed counterargument, addressing at least three specific risks of application-only enforcement. Are there any scenarios where temporarily disabling FKs might be legitimate?

Show answer

Relying solely on application code for referential integrity introduces at least three critical risks. First, bypassed validation: any client that accesses the database directly—ad-hoc SQL scripts, data migration tools, a new microservice—circumvents the application layer entirely, creating orphaned rows. Second, race conditions: without FK locks, a concurrent DELETE on the parent table can succeed between an application's existence check and its child INSERT, producing inconsistencies that application-level code cannot reliably prevent. Third, code duplication and drift: every application or service that writes to the database must independently re-implement the same validation logic, and over time these implementations diverge, leading to subtle data corruption. That said, temporarily disabling FK checks is legitimate during controlled ETL (Extract-Transform-Load) operations where data is loaded in dependency-violating order and then validated post-load, or during large-scale migrations where constraint re-validation is performed as a final step. The key is that these are bounded, auditable operations—not a permanent architectural choice.

SUMMARY

Lesson Summary

Summary

Table constraints are declarative rules embedded in a table's schema that the DBMS enforces automatically on every write operation. A PRIMARY KEY uniquely identifies each row (no NULLs, one per table, creates a unique index). A UNIQUE constraint guarantees distinct values in a column or column set (NULLs typically permitted, multiple per table). A FOREIGN KEY enforces referential integrity by requiring that values in the child column(s) correspond to existing values in a parent table's PK or UNIQUE column(s), with configurable referential actions (CASCADE, SET NULL, RESTRICT, SET DEFAULT) that control behavior on parent-row modification.

Constraints can be declared at column level (inline) or table level (after all column definitions); table-level syntax is required for composite keys. Always name your constraints explicitly to simplify future ALTER TABLE operations. Together, these three constraint types form the foundation of data integrity in relational databases—guaranteeing that your schema accurately mirrors the entity relationships and business rules of the domain it models.