PostgreSQL Constraints and Data Integrity

1. What You'll Learn

PRIMARY KEY (single-column / composite)
FOREIGN KEY (CASCADE / SET NULL / SET DEFAULT / NO ACTION / RESTRICT)
UNIQUE constraint
NOT NULL constraint
CHECK constraint (PostgreSQL feature: can reference other columns in a row)
EXCLUSION constraint (PostgreSQL feature: e.g., non-overlapping time ranges)
DEFERRABLE deferred constraint (PostgreSQL feature)
Constraint naming and management

2. The Story

Charlie is a database architect at a SaaS platform. He needs to enforce the following business rules at the database level:

Every order's amount must be > 0 (CHECK)
Meeting room bookings' time ranges must not overlap (EXCLUSION)
Deleting a customer should auto-clean their orders, but critical orders must be protected (FOREIGN KEY cascade strategy)
When bulk-importing data, references between rows may temporarily violate constraints, and should only be checked after the import completes (DEFERRABLE)

Charlie uses constraints instead of application-layer validation, making the database the last line of defense for data integrity.

3. Concept: PRIMARY KEY

(1) The Role of the Primary Key

The primary key uniquely identifies each row in a table, combining UNIQUE + NOT NULL. A table can have only one primary key.

Property	Description
Uniqueness	Duplicate values not allowed
Not null	NULL not allowed
Auto index	PostgreSQL automatically creates a B-Tree unique index for the primary key
One per table	Only one PRIMARY KEY can be defined

▶ Example: Single-Column Primary Key

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

TEXT

CREATE TABLE

▶ Example: Composite Primary Key

SQL

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

TEXT

CREATE TABLE

(2) Primary Key Column Type Comparison

Type	Storage	Range	Use case
SERIAL / BIGSERIAL	4/8 bytes	2B / 9.2×10¹⁸	Most business tables
UUID	16 bytes	Globally unique	Distributed systems
Natural key (e.g., email)	Variable	—	Rarely used; business-change risk
Composite primary key	Multiple columns	—	Junction tables, many-to-many bridge tables

▶ Example: UUID Primary Key

SQL

CREATE TABLE global_events (
  event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

TEXT

CREATE TABLE

4. Concept: FOREIGN KEY

(1) Foreign Key and the Five Cascade Strategies

A foreign key guarantees referential integrity: the referencing column value in the child table must exist in the parent table's primary/unique key.

Strategy	ON DELETE behavior	ON UPDATE behavior	Typical scenario
CASCADE	Cascade delete child rows	Cascade update child rows	Order items deleted with the order
SET NULL	Set child row to NULL	Set child row to NULL	Optional association; delete has no impact
SET DEFAULT	Set child row to default	Set child row to default	Rarely used
RESTRICT	Reject delete (immediate)	Reject update (immediate)	Protect critical data
NO ACTION	Reject delete (deferrable)	Reject update (deferrable)	Default behavior

▶ Example: CASCADE Cascade Delete

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2) NOT NULL
);

TEXT

CREATE TABLE

When a customer is deleted, all their orders are deleted automatically.

▶ Example: RESTRICT Protects Critical Data

SQL

CREATE TABLE invoices (
  invoice_id  BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL
    REFERENCES orders(order_id) ON DELETE RESTRICT,
  invoice_date DATE NOT NULL
);

TEXT

CREATE TABLE

If an order already has an invoice, deleting the order is rejected.

(2) CASCADE vs RESTRICT Comparison

Dimension	CASCADE	RESTRICT
Delete parent row	Child rows deleted too	Delete rejected
Data safety	Convenient but risky	Safe but needs manual cleanup
Use case	Ancillary data	Critical / financial data

▶ Example: Multi-Level Cascade

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(customer_id) ON DELETE CASCADE
);

CREATE TABLE order_items (
  item_id     BIGSERIAL PRIMARY KEY,
  order_id    BIGINT REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL
);

TEXT

CREATE TABLE

Delete customer → automatically delete orders → automatically delete order_items, a three-level cascade.

▶ Example: SET NULL

SQL

CREATE TABLE reviews (
  review_id   BIGSERIAL PRIMARY KEY,
  product_id  BIGINT REFERENCES products(product_id) ON DELETE SET NULL,
  content     TEXT NOT NULL
);

TEXT

CREATE TABLE

After a product is deleted, the review's product_id is set to NULL, but the review is kept.

5. Concept: UNIQUE and NOT NULL

(1) UNIQUE Constraint

UNIQUE guarantees column values (or column combinations) are not duplicated, and allows NULL (multiple NULLs don't count as duplicates).

Dimension	PRIMARY KEY	UNIQUE
Count per table	Only one	Multiple allowed
NULL allowed	Not allowed	Allowed (multiple NULLs don't conflict)
Auto index	Yes	Yes
Semantics	Identifies the row	Guarantees uniqueness

▶ Example: Multi-Column UNIQUE Constraint

SQL

CREATE TABLE user_accounts (
  user_id BIGSERIAL PRIMARY KEY,
  email   VARCHAR(255) UNIQUE,
  phone   VARCHAR(20),
  UNIQUE (email, phone)
);

TEXT

CREATE TABLE

email unique on its own + (email, phone) unique as a combination.

(2) NOT NULL Constraint

NOT NULL forbids a column from storing NULL values; it's the most basic integrity constraint.

Writing	Description
`col TYPE NOT NULL`	Column-level constraint
`CONSTRAINT nn_col CHECK (col IS NOT NULL)`	Equivalent form

▶ Example: NOT NULL Combination

SQL

CREATE TABLE products (
  product_id   BIGSERIAL PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  unit_price   DECIMAL(10,2) NOT NULL,
  description  TEXT
);

TEXT

CREATE TABLE

description allows NULL; the other key columns don't.

6. Concept: CHECK Constraint

(1) CHECK Constraint Basics

A CHECK constraint requires a column value to satisfy a given boolean expression. PostgreSQL's CHECK can reference other columns in the same row (SQL standard supports this too, but many databases don't).

Property	Description
Row-level check	Can only reference the current row's columns
PostgreSQL feature	Can reference other rows (via subquery, with limits)
Table-level CHECK	Can constrain multiple columns at once
NO INHERIT	Not propagated to child tables (PG feature)

▶ Example: Amount Must Be Positive

SQL

CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount > 0),
  discount    DECIMAL(12,2) DEFAULT 0 CHECK (discount >= 0)
);

TEXT

CREATE TABLE

▶ Example: Multi-Column CHECK Constraint

SQL

CREATE TABLE campaigns (
  campaign_id BIGSERIAL PRIMARY KEY,
  start_date  DATE NOT NULL,
  end_date    DATE NOT NULL,
  budget      DECIMAL(12,2) NOT NULL CHECK (budget > 0),
  CONSTRAINT chk_date_range CHECK (end_date >= start_date),
  CONSTRAINT chk_budget_limit CHECK (budget <= 1000000)
);

TEXT

CREATE TABLE

(2) Common CHECK Patterns

Business rule	CHECK expression
Amount positive	`CHECK (amount > 0)`
Discount range	`CHECK (discount BETWEEN 0 AND 1)`
Date order	`CHECK (end_date >= start_date)`
Enum values	`CHECK (status IN ('active', 'inactive', 'pending'))`
String length	`CHECK (LENGTH(phone) >= 10)`
Percentage	`CHECK (rate >= 0 AND rate <= 100)`

▶ Example: Named Constraints for Easy Management

SQL

CREATE TABLE subscriptions (
  sub_id    BIGSERIAL PRIMARY KEY,
  plan      VARCHAR(50) NOT NULL,
  price     DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_price_positive CHECK (price > 0),
  CONSTRAINT chk_plan_valid CHECK (plan IN ('free', 'basic', 'pro', 'enterprise'))
);

TEXT

CREATE TABLE

▶ Example: Add a CHECK to an Existing Table

SQL

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive CHECK (total_amount > 0);

TEXT

-- SQL statement executed successfully

7. Concept: EXCLUSION Constraint

(1) How EXCLUSION Works

An EXCLUSION constraint guarantees: if two rows are "equal" on specified columns (compared with the = operator), they don't "overlap" on the specified dimension (compared with an overlap operator). This is a PostgreSQL-specific feature.

Scenario	Constraint	Operator
Time ranges don't overlap	Same resource's times can't cross	`=, &&`
No duplicate seats	Same session's seat numbers don't repeat	`=, =` (equivalent to UNIQUE)

▶ Example: Meeting Room Bookings Don't Overlap in Time

SQL

CREATE TABLE room_bookings (
  booking_id BIGSERIAL PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

TEXT

CREATE TABLE

▶ Example: Conflicting Insert

SQL

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 09:00, 2025-07-13 11:00)', 'Alice');

INSERT INTO room_bookings (room_id, time_range, booked_by)
VALUES (1, '[2025-07-13 10:00, 2025-07-13 12:00)', 'Bob');

TEXT

ERROR: conflicting key value violates exclusion constraint "excl_room_no_overlap"
DETAIL: Key (room_id, time_range)=(1, [2025-07-13 10:00,2025-07-13 12:00))
conflicts with existing key (room_id, time_range)=(1, [2025-07-13 09:00,2025-07-13 11:00)).

(2) EXCLUSION vs UNIQUE Comparison

Dimension	UNIQUE	EXCLUSION
Comparison operator	Only `=`	Custom (`=`, `&&`, `<->`, etc.)
Range overlap	Not supported	Supported
Index type	B-Tree	GiST / SP-GiST
Flexibility	Low	High
Use case	Discrete value uniqueness	Non-overlapping ranges, distance constraints

▶ Example: Price Ranges Don't Overlap

SQL

CREATE TABLE discount_tiers (
  tier_id    BIGSERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  price_range NUMRANGE NOT NULL,
  discount   DECIMAL(5,4) NOT NULL,
  CONSTRAINT excl_price_no_overlap
    EXCLUDE USING GiST (product_id WITH =, price_range WITH &&)
);

TEXT

CREATE TABLE

8. Concept: DEFERRABLE Deferred Constraint

(1) Deferred Constraint Mechanism

By default, constraints are checked immediately after each statement. A DEFERRABLE constraint lets the check be deferred until transaction commit, solving the circular-reference problem in bulk operations.

Mode	Check timing	Syntax
IMMEDIATE	After each statement	Default behavior
DEFERRABLE INITIALLY IMMEDIATE	After each statement (switchable)	DEFERRABLE INITIALLY IMMEDIATE
DEFERRABLE INITIALLY DEFERRED	At transaction commit	DEFERRABLE INITIALLY DEFERRED

▶ Example: Solution for Circular References

SQL

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  manager_id INT
);

ALTER TABLE departments
ADD CONSTRAINT fk_dept_manager
  FOREIGN KEY (manager_id) REFERENCES departments(dept_id)
  DEFERRABLE INITIALLY DEFERRED;

TEXT

CREATE TABLE

▶ Example: Operations Inside a Transaction

SQL

BEGIN;

INSERT INTO departments (dept_id, name, manager_id)
VALUES (1, 'Engineering', 2);

INSERT INTO departments (dept_id, name, manager_id)
VALUES (2, 'QA', 1);

COMMIT;

TEXT

INSERT 0 1

Executing the first INSERT alone would fail (manager_id=2 doesn't exist yet), but DEFERRABLE defers the check until COMMIT, so once both INSERTs complete the validation passes.

(2) Dynamically Switching Check Timing

▶ Example: SET CONSTRAINTS

SQL

BEGIN;
SET CONSTRAINTS fk_dept_manager DEFERRED;

INSERT INTO departments (dept_id, name, manager_id) VALUES (3, 'Sales', 4);
INSERT INTO departments (dept_id, name, manager_id) VALUES (4, 'Marketing', 3);

SET CONSTRAINTS fk_dept_manager IMMEDIATE;
COMMIT;

TEXT

INSERT 0 1

(3) IMMEDIATE vs DEFERRED Comparison

Dimension	IMMEDIATE	DEFERRED
Check timing	After each statement	At transaction commit
Performance	Faster (immediate feedback)	Slightly slower (batch check)
Circular reference	Can't solve	Solvable
Risk	Low	Violation only discovered at end of transaction
Use case	Most constraints	Circular references, bulk import

9. Concept: Constraint Naming and Management

(1) Constraint Naming Convention

Good naming makes it easy to locate problems and run operations.

Constraint type	Recommended prefix	Example
PRIMARY KEY	pk_	pk_orders
FOREIGN KEY	fk_	fk_orders_customer
UNIQUE	uq_	uq_users_email
CHECK	chk_	chk_amount_positive
EXCLUSION	excl_	excl_room_no_overlap

▶ Example: Explicitly Name All Constraints

SQL

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE CASCADE,
  total_amount DECIMAL(12,2)  CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20)    CONSTRAINT chk_status_valid
    CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
  order_date   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

TEXT

CREATE TABLE

(2) Constraint Management Operations

Operation	Syntax
Add constraint	`ALTER TABLE t ADD CONSTRAINT name CHECK (...)`
Drop constraint	`ALTER TABLE t DROP CONSTRAINT name`
View constraints	`SELECT * FROM pg_constraint WHERE conrelid = 't'::regclass`
Disable triggers (indirectly disable constraints)	`ALTER TABLE t DISABLE TRIGGER ALL`

▶ Example: View All Constraints on a Table

SQL

SELECT
  conname AS constraint_name,
  contype AS type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

TEXT

 constraint_name       | type | definition
-----------------------+------+--------------------------------------------
 pk_orders             | p    | PRIMARY KEY (order_id)
 fk_orders_customer    | f    | FOREIGN KEY (customer_id) REFERENCES ...
 chk_amount_positive   | c    | CHECK ((total_amount > 0))
 chk_status_valid      | c    | CHECK ((status = ANY (ARRAY['pending'::...

10. Comprehensive Example

Charlie's SaaS platform constraint scheme—covering primary key, foreign-key cascade, CHECK, EXCLUSION, DEFERRABLE:

SQL

CREATE TABLE customers (
  customer_id BIGSERIAL    CONSTRAINT pk_customers PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) CONSTRAINT uq_customers_email UNIQUE,
  credit_limit DECIMAL(12,2) DEFAULT 0
    CONSTRAINT chk_credit_non_negative CHECK (credit_limit >= 0)
);

CREATE TABLE orders (
  order_id     BIGSERIAL      CONSTRAINT pk_orders PRIMARY KEY,
  customer_id  BIGINT NOT NULL CONSTRAINT fk_orders_customer
    REFERENCES customers(customer_id) ON DELETE RESTRICT,
  total_amount DECIMAL(12,2) NOT NULL
    CONSTRAINT chk_amount_positive CHECK (total_amount > 0),
  status       VARCHAR(20) DEFAULT 'pending'
    CONSTRAINT chk_status CHECK (status IN ('pending','shipped','delivered','cancelled')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE room_bookings (
  booking_id BIGSERIAL   CONSTRAINT pk_bookings PRIMARY KEY,
  room_id    INT NOT NULL,
  time_range TSTZRANGE NOT NULL,
  booked_by  VARCHAR(100) NOT NULL,
  CONSTRAINT excl_room_no_overlap
    EXCLUDE USING GiST (room_id WITH =, time_range WITH &&)
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_deferred
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE RESTRICT
  DEFERRABLE INITIALLY IMMEDIATE;

11. Execution Flow

Where constraint checking sits in the execution of a DML statement:

flowchart TD
    A[INSERT/UPDATE/DELETE] --> B{NOT NULL check}
    B -->|pass| C{CHECK check}
    B -->|fail| Z[Error, roll back]
    C -->|pass| D{UNIQUE check}
    C -->|fail| Z
    D -->|pass| E{PRIMARY KEY check}
    D -->|fail| Z
    E -->|pass| F{EXCLUSION check}
    E -->|fail| Z
    F -->|pass| G{Constraint timing?}
    F -->|fail| Z
    G -->|IMMEDIATE| H[Check FK immediately]
    G -->|DEFERRED| I[Defer FK check to COMMIT]
    H -->|pass| J[Statement complete]
    I --> K[COMMIT]
    K --> L[Check all DEFERRED FK]
    L -->|pass| M[Transaction committed]
    L -->|fail| Z

    style Z fill:#ffccbc
    style J fill:#c8e6c9
    style M fill:#c8e6c9

❓ FAQ

Q What's the difference between RESTRICT and NO ACTION?

A Functionally almost identical—both reject deletes/updates. The difference is that NO ACTION can be combined with DEFERRABLE to defer the check, while RESTRICT always checks immediately.

Q Does a UNIQUE constraint allow multiple NULLs?

A In PostgreSQL, a UNIQUE column allows multiple NULLs, because NULL != NULL. If you need NULL to also be unique, add a NOT NULL constraint.

Q Can a CHECK constraint reference rows in other tables?

A You can write a subquery, but PostgreSQL's CHECK only guarantees the constraint for the current row—it does not guarantee cross-row consistency (another session may modify the referenced data). For cross-table consistency, use a foreign key.

Q What index does an EXCLUSION constraint need?

A It needs a GiST or SP-GiST index for support. PostgreSQL automatically creates the corresponding index for an EXCLUSION constraint.

Q Can DEFERRABLE be used for CHECK constraints?

A No. DEFERRABLE applies only to FOREIGN KEY and UNIQUE constraints. CHECK and NOT NULL are always checked immediately.

Q Does a large CASCADE delete affect performance?

A Yes. Cascade deletes execute row by row and may generate heavy locking and I/O. For large batch deletes, it's recommended to manually delete child-table data first, then delete the parent row, to avoid long transactions.

📖 Summary

PRIMARY KEY = UNIQUE + NOT NULL, one per table, auto-creates an index
FOREIGN KEY has five cascade strategies: CASCADE / SET NULL / SET DEFAULT / RESTRICT / NO ACTION
CASCADE is convenient but risky; RESTRICT is safe but needs manual cleanup
UNIQUE allows multiple NULLs; PRIMARY KEY allows no NULL at all
CHECK constraint can reference other columns in the same row, ideal for multi-column logic validation
EXCLUSION constraint (PG feature) guarantees non-overlapping ranges, e.g., meeting room bookings
DEFERRABLE deferred constraint (PG feature) solves circular references and bulk-import problems
Explicitly naming constraints aids management and operations; the pk_/fk_/uq_/chk_/excl_ prefixes are recommended

📝 Exercises

⭐ Create customers and orders tables, define the primary and foreign keys (ON DELETE CASCADE), and add a CHECK constraint ensuring order amount > 0.
⭐ Create a products table with a UNIQUE SKU column and a CHECK constraint ensuring price > 0 and discount <= price.
⭐⭐ Use an EXCLUSION constraint to create a room_bookings table, ensuring the same meeting room's time ranges don't overlap, and test a conflicting insert.
⭐⭐ Create two mutually referencing tables (employees references departments, and departments' manager_id references employees), and use DEFERRABLE to solve the circular reference.
⭐⭐⭐ Design a complete SaaS tenant data model with: a tenants table, a users table (foreign-key cascade), a subscriptions table (CHECK validating dates and amounts), a resource-booking table (EXCLUSION preventing time overlap), and name all constraints.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Constraints and Data Integrity

1. What You'll Learn

2. The Story

3. Concept: PRIMARY KEY

(1) The Role of the Primary Key

▶ Example: Single-Column Primary Key

▶ Example: Composite Primary Key

(2) Primary Key Column Type Comparison

▶ Example: UUID Primary Key

4. Concept: FOREIGN KEY

(1) Foreign Key and the Five Cascade Strategies

▶ Example: CASCADE Cascade Delete

▶ Example: RESTRICT Protects Critical Data

(2) CASCADE vs RESTRICT Comparison

▶ Example: Multi-Level Cascade

▶ Example: SET NULL

5. Concept: UNIQUE and NOT NULL

(1) UNIQUE Constraint

▶ Example: Multi-Column UNIQUE Constraint

(2) NOT NULL Constraint

▶ Example: NOT NULL Combination

6. Concept: CHECK Constraint

(1) CHECK Constraint Basics

▶ Example: Amount Must Be Positive

▶ Example: Multi-Column CHECK Constraint

(2) Common CHECK Patterns

▶ Example: Named Constraints for Easy Management

▶ Example: Add a CHECK to an Existing Table

7. Concept: EXCLUSION Constraint

(1) How EXCLUSION Works

▶ Example: Meeting Room Bookings Don't Overlap in Time

▶ Example: Conflicting Insert

(2) EXCLUSION vs UNIQUE Comparison

▶ Example: Price Ranges Don't Overlap

8. Concept: DEFERRABLE Deferred Constraint

(1) Deferred Constraint Mechanism

▶ Example: Solution for Circular References

▶ Example: Operations Inside a Transaction

(2) Dynamically Switching Check Timing

▶ Example: SET CONSTRAINTS

(3) IMMEDIATE vs DEFERRED Comparison

9. Concept: Constraint Naming and Management

(1) Constraint Naming Convention

▶ Example: Explicitly Name All Constraints

(2) Constraint Management Operations

▶ Example: View All Constraints on a Table

10. Comprehensive Example

11. Execution Flow

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好