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.
PRIMARY KEY, UNIQUE, and FOREIGN KEY as part of the Data Definition Language (DDL), establishing a vendor-neutral syntax for constraint declaration.ON DELETE CASCADE and ON UPDATE SET NULL, giving developers fine-grained control over how constraint violations are resolved automatically.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.
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
FOREIGN KEY
UNIQUE
Declarative vs. Procedural
Visual Explanation — Entity & Referential Integrity
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.
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 |
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.
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.
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.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.
dept_id, instructor_id, course_codedept_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)
);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
);ON UPDATE CASCADE means if a department's ID changes, all linked instructors update automatically.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).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".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 |
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.
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 |
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.
Practice Problems
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.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.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.Lesson 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.