Relational Data Model

The relational model organizes data as relations — unordered sets of rows sharing the same columns — and lets you describe the result you want instead of walking pointer chains to fetch it. Codd's 1970 insight still defines every SQL database: a relation is a set, and SQL is applied set theory.

Intermediate relational-model keys constraints foreign-keys schema-design Created 2026-05-29 · Updated 2026-05-29

Key Components

Relation (table): A set of tuples (rows) that all share the same named attributes (columns). The formal name is "relation"; SQL calls it a table.
Tuple & attribute: A tuple is one row (a single data point); an attribute is one named column. A column's domain is the set of allowed values — its data type plus constraints.
Key hierarchy: Superkey ⊇ candidate key (a minimal superkey) → one chosen as the primary key (UNIQUE + NOT NULL). A composite key spans several columns; a foreign key references another table's key.
Constraint: A rule the engine enforces on every write: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. Constraints push correctness down into the database where it holds for all concurrent writers.
Integrity rules: Entity integrity: every row is identifiable (PK is NOT NULL). Referential integrity: a foreign key may only point at a row that actually exists.

Concrete Example

A single CREATE TABLE packs most of the model into a few lines: a primary key (entity integrity), a unique natural key kept separate from the PK, a non-null business rule enforced with a CHECK, and a server-supplied DEFAULT.

CREATE TABLE products (
    id     BIGINT        PRIMARY KEY,
    sku    VARCHAR(64)   NOT NULL UNIQUE,
    price  NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock  INT           NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

Here id is a surrogate primary key — a meaningless, system-generated number. The meaningful sku is a natural key, but it is kept as a plain UNIQUE constraint rather than the PK, so that a future SKU rename never has to cascade through every foreign key that points here.

Now model a many-to-many relationship. The relational model has no direct M:N representation, so it is decomposed into two one-to-many links through a junction table with a composite primary key:

CREATE TABLE enrollments (
    student_id  BIGINT REFERENCES students(id),
    course_id   BIGINT REFERENCES courses(id),
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id)   -- composite PK
);

The two REFERENCES clauses are foreign keys enforcing referential integrity — you cannot enroll a student that does not exist. The composite PK (student_id, course_id) guarantees a student can't enroll in the same course twice. And enrolled_at shows how junction tables often grow their own attributes, becoming first-class entities.

Visual Model

Picture three tables as three boxes. The trick is that nothing physically connects them — there are no pointers. A row in one table "relates" to a row in another only because it stores a value that matches a key column elsewhere. Foreign keys are the rule that says "this value must match a real one over there." Walk the steps to see how a primary key becomes a foreign key, and how a junction table resolves a many-to-many link.

Step 1 of N

Deeper — Edge Cases & Gotchas

SQL is not the relational model. The relational model is Codd's mathematical theory; SQL is a concrete language that implements it imperfectly. Nearly every SQL "gotcha" is a spot where SQL deliberately bent a relational rule:

Property	Relational model	SQL
Duplicate rows	Forbidden (a relation is a set)	Allowed (tables are bags / multisets)
NULLs	Absent from Codd's original model	Allowed, with three-valued logic
Column order	Logically irrelevant	Defined and ordered
Row order	No inherent order	No guarantee without ORDER BY

Anti-pattern: relying on row order. A relation has no inherent order, so this is a coin flip:

-- "give me the latest signup" — WRONG
SELECT * FROM users LIMIT 1;        -- returns *some* row, not the newest

Postgres returns heap/scan order, which changes after every UPDATE or VACUUM. Always sort explicitly: ORDER BY created_at DESC LIMIT 1.

Anti-pattern: a natural key as the primary key. Using email as the PK looks tidy until a user changes their email:

UPDATE users SET email = 'new@x.com' WHERE email = 'old@x.com';
-- every FK that referenced the old email is now dangling (or must cascade)

Changing a PK ripples through every foreign key that references it. Use a stable surrogate key (BIGINT sequence or UUID) as the PK and keep email as a UNIQUE constraint.

Gotcha: NULL = NULL is UNKNOWN, not true. Because SQL uses three-valued logic, a standard UNIQUE constraint treats two NULLs as not equal — so it permits multiple NULL rows:

CREATE TABLE t (code VARCHAR UNIQUE);
INSERT INTO t VALUES (NULL), (NULL);   -- both succeed!

If you need at most one missing value, you need a different tool (e.g. NULLS NOT DISTINCT in modern Postgres, or a partial unique index).

Referential actions decide what happens to children when a parent row is deleted: RESTRICT/NO ACTION (default) blocks the delete, CASCADE deletes the children too (powerful but one delete can wipe a large subtree), and SET NULL orphans children by nulling their FK.

Atomicity (1NF): each cell should hold a single atomic value, not a list. tags = "red,blue,green" violates this in spirit; Postgres arrays and JSONB deliberately bend the rule as a denormalization tradeoff.

Test Yourself

The relational model says a relation is a set, yet SQL tables can hold duplicate rows. Which feature lets you opt back into set semantics for storage?

Which key is defined as a minimal superkey — one where removing any column breaks uniqueness?

Model posts and tags where a post has many tags and a tag has many posts. What does this require?

A many-to-many relationship, which the relational model can't represent directly — decompose it into two one-to-many links via a junction table:

CREATE TABLE post_tags (
    post_id BIGINT REFERENCES posts(id),
    tag_id  BIGINT REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

The composite primary key prevents tagging the same post with the same tag twice, and the two foreign keys enforce that both the post and the tag exist.

Why does a UNIQUE column sometimes allow two NULL values?