beginnerintermediateadvanced

PostgreSQL · Section 8

Constraints and Data Integrity

Level: intermediate
Read: 32 min
Concepts: 6

The five core constraint types and the distinct invariant each one enforces, composite keys/uniqueness as "the combination must be unique, not each column," DEFERRABLE constraints for multi-step atomic operations, ON DELETE/ON UPDATE's referential actions, and the central argument for why critical invariants belong in the schema rather than solely in application code.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK each enforce a distinct invariant, on every write.
A composite constraint enforces uniqueness on the combination of columns, not each column alone.
DEFERRABLE INITIALLY DEFERRED checks at COMMIT, enabling multi-step atomic operations like swapping unique values.
ON DELETE/ON UPDATE decide a child row's fate when its parent is deleted or changes — the default blocks it.
A database constraint protects every write, from any code path — application validation only protects the one it is written into.

What you will be able to do

Choose the right constraint type for a given data-integrity requirement
Design a composite key or unique constraint for a many-to-many junction table
Recognize when a DEFERRABLE constraint is needed for a multi-step atomic operation
Choose an appropriate ON DELETE/ON UPDATE action deliberately, rather than accepting the default by omission
Explain why a critical invariant belongs in the schema, not only in application code

From five constraints to one principle

Five constraint types

each enforces a different invariant

Referential action

ON DELETE/ON UPDATE — what happens to children

Enforced from any code path

not just the one that wrote the validation

The five constraint types

What each one enforces, and how a composite version changes the scope of uniqueness.

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK Constraints

corebeginner

PRIMARY KEY uniquely identifies a row and forbids NULL. FOREIGN KEY requires a value to reference an existing row elsewhere. UNIQUE forbids duplicate values (NULLs excepted). NOT NULL forbids a missing value. CHECK enforces an arbitrary boolean condition on a row. Each rejects a bad write outright rather than allowing it.

Think of it as

Every constraint answers the same underlying question — "what makes a row valid?" — for a different aspect of the data. PRIMARY KEY: can this row be uniquely found? FOREIGN KEY: does this reference actually point somewhere real? UNIQUE: does this value collide with another row's? NOT NULL: is this required fact actually present? CHECK: does this row satisfy an arbitrary business rule? None of them are optional documentation — PostgreSQL enforces every one of them on every write, rejecting the write if violated.

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    order_number TEXT UNIQUE NOT NULL,
    total NUMERIC NOT NULL CHECK (total >= 0)
);

What we're doing: Attempt several writes that each violate a different constraint, and observe PostgreSQL reject every one before anything is stored.

constraint_rejections.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    order_number TEXT UNIQUE NOT NULL,
    total NUMERIC NOT NULL CHECK (total >= 0)
);

INSERT INTO customers DEFAULT VALUES;

INSERT INTO orders (customer_id, order_number, total) VALUES (99, 'A1', 50);
-- FOREIGN KEY violation: no customer with id 99

INSERT INTO orders (customer_id, order_number, total) VALUES (1, NULL, 50);
-- NOT NULL violation: order_number cannot be NULL

INSERT INTO orders (customer_id, order_number, total) VALUES (1, 'A1', -10);
-- CHECK violation: total must be >= 0

10: No customer with id 99 exists — the foreign key constraint refuses to let this order reference a nonexistent customer.
13: order_number is declared NOT NULL — a NULL value here is rejected before it ever reaches storage.
16: CHECK (total >= 0) evaluates to false for -10 — the row is rejected regardless of every other column being valid.

Output

ERROR:  insert or update on table "orders" violates foreign key constraint
DETAIL:  Key (customer_id)=(99) is not present in table "customers".

ERROR:  null value in column "order_number" of relation "orders" violates not-null constraint

ERROR:  new row for relation "orders" violates check constraint "orders_total_check"
DETAIL:  Failing row contains (3, 1, A1, -10).

Why this works: Each constraint is checked independently at write time, and PostgreSQL reports specifically which one failed — the foreign key check happens before the row could ever reference a nonexistent customer, NOT NULL is checked before an incomplete row is stored, and CHECK is evaluated per row regardless of whether every other constraint would have passed. This is the concrete mechanism behind "the database enforces the rules you declare": none of these three bad rows ever make it into the table, so no later query can ever encounter them.

Relying on application code to enforce an invariant that could be a constraint

Wrong

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
-- application code checks "total >= 0" before every INSERT -- but only in that one code path

Better

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC CHECK (total >= 0));
-- enforced for every write, from any code path, including future ones nobody has written yet

What you see: A negative total appears in the database despite application code that "always validates before inserting" — traced eventually to a second code path (a script, a migration, a different service, a manual psql session) that never went through the original validation.

Why: Application-level validation only runs in the specific code path it was written for — it protects nothing written through a different path, and a growing system inevitably grows more than one path to the database over time. A CHECK constraint is enforced by PostgreSQL itself on every write regardless of which code, script, or tool performed it, which is why critical invariants belong in the schema rather than solely in application logic.

Five constraints, five different invariants

PRIMARY KEY

unique + not null identifier

FOREIGN KEY

must reference a real row

UNIQUE

no duplicate non-NULL values

NOT NULL

must be present

CHECK (expr)

arbitrary boolean rule

What each constraint actually rejects

What each constraint actually rejects
Constraint	Rejects a write that would...
PRIMARY KEY	duplicate an existing key value, or leave it NULL
FOREIGN KEY	reference a row that does not exist in the other table
UNIQUE	duplicate an existing non-NULL value in that column
NOT NULL	leave the column empty
CHECK (expr)	make expr evaluate to false for that row

Together

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku TEXT UNIQUE NOT NULL,
    price NUMERIC CHECK (price >= 0)
);

Remember: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK each enforce a different invariant, and all are checked on every write from any code path — application-level validation only protects the one path it was written for.

Composite Keys and Composite Unique Constraints

standardintermediate

A composite key or composite unique constraint spans more than one column — the uniqueness rule applies to the combination, not to each column individually. Two rows can share a value in one of the columns as long as the combination of all the columns together is still unique.

Think of it as

A single-column UNIQUE constraint asks "does this one value already exist?" A composite constraint asks "does this exact combination of values already exist?" — which is a fundamentally looser question that allows plenty of individual-column repetition, as long as the full tuple stays distinct. This is exactly the shape a junction table's primary key needs: (student_id, course_id) should be unique as a pair, even though the same student_id and the same course_id each legitimately repeat across many rows.

sql

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (student_id, course_id)
);

What we're doing: Show a composite primary key allowing the same student_id and course_id to each repeat individually, while rejecting an exact duplicate pair.

composite_key_demo.sqlsql

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments VALUES (1, 100), (1, 101), (2, 100);
-- student 1 appears twice, course 100 appears twice -- both fine, since the PAIRS differ

INSERT INTO enrollments VALUES (1, 100);
-- ERROR: duplicate key value violates unique constraint -- this exact pair already exists

7: Three rows, each a distinct (student_id, course_id) pair — student 1 and course 100 each repeat individually, which is fine.
9: Inserting the exact pair (1, 100) again violates the composite primary key, since that specific combination already exists.

Output

INSERT 0 3

ERROR:  duplicate key value violates unique constraint "enrollments_pkey"
DETAIL:  Key (student_id, course_id)=(1, 100) already exists.

Why this works: A composite primary key builds its uniqueness index across all the listed columns together, so the check is "does this exact combination already exist," not "does either column's value already exist somewhere." Student 1 already appears in the table (paired with course 101), and course 100 already appears (paired with student 2), but neither of those facts violates anything — only the specific pair (1, 100) is checked against, and only re-inserting that exact pair triggers the violation.

Using single-column UNIQUE constraints where a composite constraint was actually needed

Wrong

sql

CREATE TABLE enrollments (
    student_id INT UNIQUE,
    course_id INT UNIQUE
);
-- forces each student into at most ONE course, and each course to have at most ONE student

Better

sql

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

What you see: Attempting to enroll a second student in a course, or the same student in a second course, fails with a uniqueness violation, even though both operations should obviously be allowed.

Why: UNIQUE on student_id alone enforces "this value can appear at most once in the whole table," which is a far stronger and different constraint than "this value can repeat, but not paired with this exact course again." The composite constraint is the one that actually matches the real-world invariant — a specific enrollment can only be recorded once — while leaving both individual columns free to repeat across different rows.

Single-column UNIQUE vs composite UNIQUE

Single-column UNIQUE vs composite UNIQUE
Constraint	What must be unique
UNIQUE (email)	each email value alone
UNIQUE (student_id, course_id)	the combination — student_id and course_id can each repeat separately

Together

sql

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

Remember: A composite constraint enforces uniqueness on the combination of columns, not each column individually — the standard shape for a junction table's primary key, where each foreign key legitimately repeats across different rows.

Timing and referential actions

Deferring a check to COMMIT, and deciding what happens to child rows on delete or update.

DEFERRABLE Constraints and Deferred Validation

standardadvanced

By default, PostgreSQL checks a constraint immediately after each statement. A DEFERRABLE constraint can instead be checked at the end of the transaction (COMMIT), which allows a transaction to pass through a temporarily "invalid" intermediate state as long as everything is consistent by the time it commits.

Think of it as

Most constraints ask "is this row valid right now, this instant?" A DEFERRABLE constraint set to INITIALLY DEFERRED asks instead "is this row valid by the time the whole transaction finishes?" — useful specifically when a multi-step change is only ever individually valid in a particular order, but the whole set is valid together. The classic case is swapping two rows' unique values: swapping A and B directly means one moment where both rows briefly hold the same value, which a normal UNIQUE constraint would reject mid-transaction.

sql

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    position INT,
    CONSTRAINT items_position_key UNIQUE (position) DEFERRABLE INITIALLY DEFERRED
);

What we're doing: Swap two rows' unique position values in one transaction — impossible without deferring the UNIQUE check, since one intermediate step would otherwise collide.

deferred_swap.sqlsql

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    position INT,
    CONSTRAINT items_position_key UNIQUE (position) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO items (position) VALUES (1), (2);

BEGIN;
UPDATE items SET position = 2 WHERE id = 1;  -- momentarily two rows both have position = 2
UPDATE items SET position = 1 WHERE id = 2;  -- now the swap resolves to a valid final state
COMMIT;

4: DEFERRABLE INITIALLY DEFERRED means this UNIQUE constraint is checked at COMMIT, not after each statement.
9: Right after this statement, two rows both hold position = 2 — a genuine, real intermediate violation of uniqueness.
10: This statement resolves it — by the time COMMIT runs, positions 1 and 2 are each held by exactly one row again.

Output

BEGIN
UPDATE 1
UPDATE 1
COMMIT

Why this works: With the constraint deferred to COMMIT, PostgreSQL does not check uniqueness after the first UPDATE, even though two rows briefly share position = 2 at that exact moment — it only checks once, at the very end of the transaction, by which point the second UPDATE has already restored a fully valid final state. A non-deferrable UNIQUE constraint would reject the first UPDATE immediately, making this two-step swap impossible to express as written — the whole reason DEFERRABLE exists is to allow a transaction to pass through a real, if temporary, invalid state as long as it resolves before commit.

Attempting a swap-like update without a deferrable constraint

Wrong

sql

-- items_position_key is a plain, non-deferrable UNIQUE constraint
BEGIN;
UPDATE items SET position = 2 WHERE id = 1;
-- ERROR: duplicate key value violates unique constraint "items_position_key"

Better

sql

ALTER TABLE items DROP CONSTRAINT items_position_key;
ALTER TABLE items ADD CONSTRAINT items_position_key
    UNIQUE (position) DEFERRABLE INITIALLY DEFERRED;
-- now the same two-statement swap succeeds

What you see: A two-statement swap of unique values fails on the very first statement, even though the transaction as a whole would have produced a perfectly valid final result.

Why: A non-deferrable constraint is checked immediately after the statement that would violate it, with no visibility into whether a later statement in the same transaction will resolve the violation — PostgreSQL has no way to know the swap's second UPDATE is coming. Declaring the constraint DEFERRABLE (with INITIALLY DEFERRED, or deferring it explicitly with SET CONSTRAINTS) is the only way to express "check this at the end, not after every step," which is exactly what a genuine multi-step atomic swap requires.

Constraint timing options

Constraint timing options
Declaration	When checked
NOT DEFERRABLE (default)	immediately after each statement
DEFERRABLE INITIALLY IMMEDIATE	immediately by default, but can be deferred per-transaction with SET CONSTRAINTS
DEFERRABLE INITIALLY DEFERRED	at COMMIT, unless changed per-transaction

Together

sql

ALTER TABLE items ADD CONSTRAINT items_position_key
    UNIQUE (position) DEFERRABLE INITIALLY DEFERRED;

Remember: A DEFERRABLE constraint set to INITIALLY DEFERRED checks at COMMIT instead of after each statement — the standard technique for a multi-step operation (like swapping two unique values) that is only individually valid in a particular order but valid as a whole.

ON DELETE and ON UPDATE Behavior

coreintermediate

ON DELETE and ON UPDATE tell PostgreSQL what to do to child rows when the parent row they reference is deleted or its key changes. The default, if unspecified, is NO ACTION — reject the delete/update if any child row still references it.

Think of it as

A foreign key by itself only prevents a child from referencing a nonexistent parent — it says nothing about what should happen when the parent stops existing. ON DELETE/ON UPDATE fill in that missing rule explicitly: propagate the change (CASCADE), sever the link (SET NULL), refuse the parent-side change outright (RESTRICT/NO ACTION), or reset to a fallback (SET DEFAULT). Without picking one deliberately, the default (NO ACTION) simply blocks the delete — which is often the safest default, but not always the intended behavior.

sql

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE RESTRICT
);

What we're doing: Compare CASCADE and the default NO ACTION on two different foreign keys within the same delete attempt.

on_delete_demo.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY);
CREATE TABLE products (id SERIAL PRIMARY KEY);
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES products(id)  -- default NO ACTION
);

INSERT INTO orders DEFAULT VALUES;
INSERT INTO products DEFAULT VALUES;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);

DELETE FROM orders WHERE id = 1;
-- succeeds -- CASCADE deletes the order_items row too

DELETE FROM products WHERE id = 1;
-- fails -- no order_items row references it anymore (it was cascaded away), so this actually succeeds too

5: order_id cascades: deleting an order takes its line items with it.
6: product_id has no ON DELETE clause, defaulting to NO ACTION — a product referenced by a line item cannot be deleted.
12: Deleting the order succeeds and, via CASCADE, also removes the order_items row referencing it.

Output

DELETE 1
DELETE 1

Why this works: Deleting the order triggers CASCADE on order_id, which removes the referencing order_items row as part of the same statement — after that, no row references product 1 anymore, so deleting the product also succeeds, but only because the order_items row was already gone by then. If the product delete had been attempted first, before the order was removed, NO ACTION would have rejected it outright, since a live order_items row would still be referencing that product.

Leaving ON DELETE unspecified where CASCADE was actually intended

Wrong

sql

CREATE TABLE order_items (order_id INT REFERENCES orders(id));
-- defaults to NO ACTION -- deleting an order now fails whenever it still has line items

Better

sql

CREATE TABLE order_items (order_id INT REFERENCES orders(id) ON DELETE CASCADE);
-- deleting an order correctly removes its line items too

What you see: A delete that should logically remove an order and everything that belongs to it fails with a foreign key violation, forcing application code to manually delete every dependent row first, in the correct order, before the parent delete can succeed.

Why: The default NO ACTION exists because it is the safer choice when the schema author has not made an explicit decision — silently deleting dependent data is a bigger risk than blocking an unexpected delete. But when a child table's rows genuinely have no independent meaning without their parent (line items without an order), CASCADE expresses that relationship directly in the schema, removing the need for application code to orchestrate a correct multi-table deletion order by hand.

CASCADE vs SET NULL — two very different answers to "the parent was deleted"

ON DELETE CASCADE

+child row is deleted along with the parent
+appropriate when the child has no meaning without its parent
+e.g. order_items when the order itself is deleted

ON DELETE SET NULL

−child row survives, its reference is cleared
−appropriate when the child is meaningful independently
−e.g. an employee's manager_id, when the manager leaves

The five ON DELETE options

The five ON DELETE options
Option	Effect on child rows when the parent is deleted
NO ACTION (default)	delete rejected if any child still references the parent
RESTRICT	same as NO ACTION for most purposes — rejected immediately
CASCADE	child rows are deleted too, automatically
SET NULL	child's foreign key column becomes NULL
SET DEFAULT	child's foreign key column resets to its DEFAULT value

Together

sql

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id) ON DELETE CASCADE
);
-- deleting an order automatically deletes its line items too

Remember: ON DELETE/ON UPDATE decide what happens to child rows when a parent is deleted or its key changes — CASCADE propagates the delete, SET NULL severs the link, NO ACTION (the default) blocks it outright. Pick deliberately; the default is not always the intended behavior.

Why it belongs in the schema

The argument for enforcing invariants in the database, and the three failure modes constraints prevent.

Why Critical Invariants Belong in the Database

standardintermediate

Application code only enforces a rule for requests that go through that specific code path — a database constraint enforces it for every write, from any code path, forever, including ones that do not exist yet. That is the entire argument for putting critical invariants in the schema rather than relying solely on application-level checks.

Think of it as

A system almost never has exactly one way data gets written to it for its entire lifetime — there is the main application, then eventually a migration script, an admin tool, a data-fix one-liner run in production, a second service added later, a bulk import. Each of those is a separate code path, and an invariant enforced only in the original application's validation logic protects none of them. A database constraint sits underneath all current and future code paths at once, because it is enforced by the storage layer itself, not by any particular caller.

sql

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT valid_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

What we're doing: Simulate a second write path (a raw psql session standing in for a migration script or admin tool) bypassing application-level validation, and show a database constraint still catching the bad write.

invariant_in_db.sqlsql

CREATE TABLE accounts (id SERIAL PRIMARY KEY, balance NUMERIC NOT NULL CHECK (balance >= 0));

INSERT INTO accounts (balance) VALUES (100);

-- imagine an application-layer check "balance >= 0" exists in the main app's code --
-- but this UPDATE is run directly, e.g. by an ops script that never goes through that app code
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- rejected anyway, because the CHECK constraint lives in the database, not the bypassed app code

1: CHECK (balance >= 0) is declared once, in the schema — not duplicated into every tool that might write to this table.
7: This UPDATE represents a write path that never touches the main application's validation code at all.

Output

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, -400).

Why this works: The CHECK constraint is evaluated by PostgreSQL itself as part of processing the UPDATE, regardless of what code issued that UPDATE — an ops script running raw SQL gets exactly the same protection as the main application, because the rule lives in the table definition, not in any particular caller's logic. If balance >= 0 had only been checked in the main application's code, this UPDATE would have succeeded, silently producing a negative balance that nobody's validation logic ever saw.

Trusting that "the application always validates this" is a durable guarantee

Wrong

sql

CREATE TABLE accounts (id SERIAL PRIMARY KEY, balance NUMERIC);
-- relies entirely on application code to prevent balance < 0

Better

sql

CREATE TABLE accounts (id SERIAL PRIMARY KEY, balance NUMERIC NOT NULL CHECK (balance >= 0));
-- enforced regardless of how the write happens, now or in the future

What you see: A genuinely critical invariant holds for months or years, then breaks the moment a new integration, script, or team member writes to the table through a path the original validation logic never anticipated.

Why: "The application always validates this" is a claim about the current, known set of code paths — it says nothing about paths that do not exist yet, and systems accumulate new write paths over time almost without exception (a second service, an admin panel, a one-off fix). A database constraint is not vulnerable to this kind of scope creep, because it is attached to the data itself rather than to any particular piece of code that touches the data.

Application validation vs a database constraint

Application validation vs a database constraint
Property	Application-level validation	Database constraint
Protects	only the code path it is written into	every write, from any code path
Discovered by a new team member	by reading application code	by reading the schema directly
Failure mode when bypassed	bad data silently stored	write rejected immediately, loudly

Together

sql

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
-- protects every future write, including code that does not exist yet

Remember: Application-level validation only protects the code path it is written into; a database constraint protects every write, from any code path, including ones that do not exist yet — that is why critical invariants belong in the schema.

Using Constraints to Prevent Duplicate, Orphaned or Invalid Data

standardintermediate

Three common data-quality problems each map to a specific constraint: duplicates are prevented by UNIQUE (or a composite UNIQUE), orphaned rows are prevented by FOREIGN KEY, and invalid values are prevented by CHECK. Recognizing which problem is at hand points directly at which constraint to reach for.

Think of it as

These are three distinct failure modes, not one general "bad data" category, and each has its own dedicated constraint because the mechanism needed to prevent each one is genuinely different. A duplicate is a violation within one table (does this value already exist here?). An orphan is a violation across tables (does the thing I reference still exist?). An invalid value is a violation of a business rule about one row's own content (does this row make sense on its own terms?).

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),   -- prevents orphaned rows
    order_number TEXT UNIQUE NOT NULL,                     -- prevents duplicates
    total NUMERIC NOT NULL CHECK (total >= 0)              -- prevents invalid values
);

What we're doing: Combine all three constraint types on one table and trigger each failure mode independently to confirm each constraint catches exactly its own problem.

three_failure_modes.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    order_number TEXT UNIQUE NOT NULL,
    total NUMERIC NOT NULL CHECK (total >= 0)
);

INSERT INTO customers DEFAULT VALUES;
INSERT INTO orders (customer_id, order_number, total) VALUES (1, 'A1', 50);

INSERT INTO orders (customer_id, order_number, total) VALUES (1, 'A1', 75);
-- duplicate order_number -- UNIQUE catches it

INSERT INTO orders (customer_id, order_number, total) VALUES (999, 'A2', 75);
-- orphaned reference -- FOREIGN KEY catches it

INSERT INTO orders (customer_id, order_number, total) VALUES (1, 'A3', -75);
-- invalid value -- CHECK catches it

11: order_number 'A1' already exists — UNIQUE rejects the duplicate.
14: customer 999 does not exist — FOREIGN KEY rejects the orphaned reference.
17: total is negative — CHECK rejects the invalid value.

Output

ERROR:  duplicate key value violates unique constraint "orders_order_number_key"

ERROR:  insert or update on table "orders" violates foreign key constraint
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

ERROR:  new row for relation "orders" violates check constraint "orders_total_check"

Why this works: Each rejected INSERT fails for a genuinely different structural reason — UNIQUE checks against other rows in the same table, FOREIGN KEY checks against a different table entirely, and CHECK evaluates a condition using only the row's own column values, with no reference to any other row or table at all. Recognizing which of the three a given data-quality complaint actually is (duplicate, orphan, or invalid value) is what points directly at which constraint fixes it, rather than reaching for validation logic that reimplements what the database already does natively.

Writing an application-side duplicate check instead of a UNIQUE constraint

Wrong

sql

-- app code: SELECT * FROM customers WHERE email = ? -- then INSERT if nothing found
-- a race condition: two concurrent requests can both pass the check before either inserts

Better

sql

CREATE TABLE customers (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);
-- the database itself is the single source of truth for uniqueness, no race condition possible

What you see: Two near-simultaneous signups with the same email both succeed, producing two customer rows with identical emails, despite application code that checks for existing emails before inserting.

Why: A check-then-insert pattern in application code has an inherent race condition: two concurrent requests can both run their SELECT before either has committed its INSERT, so both see "no existing row" and both proceed. A UNIQUE constraint has no such window — PostgreSQL enforces it atomically as part of each INSERT itself, so the second of two concurrent inserts is guaranteed to fail cleanly rather than silently succeeding alongside the first.

Data-quality problem → constraint that prevents it

Data-quality problem → constraint that prevents it
Problem	Constraint	Example
Duplicate row	UNIQUE	UNIQUE (email) — no two customers share an email
Orphaned row	FOREIGN KEY	order.customer_id must reference a real customer
Invalid value	CHECK	CHECK (age >= 0) — no negative ages

Together

sql

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    age INT CHECK (age >= 0)
);

Remember: Three data-quality problems, three constraints: UNIQUE prevents duplicates, FOREIGN KEY prevents orphaned rows, CHECK prevents invalid values — and each is enforced atomically by the database, avoiding the race conditions an application-side check-then-insert pattern is exposed to.