Column and Table Constraints

Learning Focus

Use this lesson to enforce data integrity with PostgreSQL constraints and to apply migration-friendly patterns such as NOT VALID + VALIDATE CONSTRAINT.

Concept Overview

Constraints are database-enforced rules that prevent invalid data from entering (or remaining in) your tables.

Two common ways to declare constraints:

• Column constraints: attached directly to a column (email text NOT NULL UNIQUE).
• Table constraints: declared separately (often needed for multi-column rules).

Constraints complement application validation: your app can provide a nice error message, but the database remains the final authority.

Why is it important?

• Data integrity: stop invalid states and impossible values
• Consistency: enforce rules across all writers (apps, scripts, BI tools)
• Simpler queries: reliable assumptions reduce defensive SQL
• Safer migrations: validate rules incrementally on large tables

Where does it fit?

Constraints are part of schema design and evolution:

• define rules at CREATE TABLE time
• add or tighten rules with ALTER TABLE
• validate existing data before making constraints strict

---

Syntax & Rules

Core Syntax

Common constraints:

CREATE TABLE invoices (
  invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  amount numeric(12,2) NOT NULL,
  issued_at timestamptz,
  paid_at timestamptz,

  CONSTRAINT invoices_amount_nonneg CHECK (amount >= 0),
  CONSTRAINT invoices_status_valid CHECK (status IN ('draft', 'issued', 'paid')),
  CONSTRAINT invoices_paid_after_issued CHECK (paid_at IS NULL OR issued_at IS NULL OR paid_at >= issued_at)
);

Add a constraint later:

ALTER TABLE invoices
ADD CONSTRAINT invoices_amount_nonneg
CHECK (amount >= 0);

Drop a constraint:

ALTER TABLE invoices
DROP CONSTRAINT invoices_amount_nonneg;

Available Options / Parameters

Constraint	Example	Meaning / Notes
NOT NULL	email text NOT NULL	column cannot be NULL
DEFAULT	created_at timestamptz DEFAULT now()	value used when column omitted
UNIQUE	UNIQUE (email)	prevents duplicates; allows multiple NULLs unless combined with NOT NULL
CHECK	CHECK (amount >= 0)	boolean rule that must be true
NOT VALID	... CHECK (...) NOT VALID	add FK/CHECK without validating existing rows immediately
VALIDATE	VALIDATE CONSTRAINT name	validate existing rows later

PostgreSQL notes:

• UNIQUE creates a unique B-tree index.
• CHECK constraints are evaluated on insert/update.
• NOT VALID / VALIDATE CONSTRAINT is supported for CHECK and FOREIGN KEY constraints.
• A UNIQUE constraint is not partial; if you need conditional uniqueness, use a unique index with WHERE.

Key Rules and Considerations

• UNIQUE allows multiple NULL values (because NULL is not equal to NULL).
• If you want uniqueness while ignoring soft-deleted rows, use a partial unique index.
• Prefer naming constraints so error messages are actionable.
• For large tables, enforce rules in phases: add constraint as NOT VALID, fix data, then validate.

---

Step-by-Step Examples

Use this setup for the examples:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text,
  status text,
  created_at timestamptz NOT NULL DEFAULT now()
);

Example 1: NOT NULL + DEFAULT (Beginner)

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

INSERT INTO users (email) VALUES ('a@example.com');

SELECT user_id, email, status
FROM users
ORDER BY user_id;

Expected output:

ALTER TABLE
ALTER TABLE
INSERT 0 1
 user_id |     email      | status
---------+----------------+--------
 1       | a@example.com  | active
(1 row)

Explanation:

• DEFAULT applies when the column is omitted.
• NOT NULL prevents missing values.

---

Example 2: UNIQUE Constraint and Duplicate Errors (Beginner)

ALTER TABLE users
ADD CONSTRAINT users_email_uniq UNIQUE (email);

INSERT INTO users (email, status) VALUES ('a@example.com', 'active');

Expected outcome:

ALTER TABLE
ERROR:  duplicate key value violates unique constraint "users_email_uniq"
DETAIL:  Key (email)=(a@example.com) already exists.

Explanation:

• Unique constraints surface clean, specific errors.

---

Example 3: CHECK Constraints for Domain Rules (Intermediate)

ALTER TABLE users
ADD CONSTRAINT users_status_valid
CHECK (status IN ('active', 'suspended', 'deleted'));

INSERT INTO users (email, status) VALUES ('b@example.com', 'unknown');

Expected outcome:

ALTER TABLE
ERROR:  new row for relation "users" violates check constraint "users_status_valid"
DETAIL:  Failing row contains (2, b@example.com, unknown, ...).

Fix:

INSERT INTO users (email, status) VALUES ('b@example.com', 'suspended');

Expected output:

INSERT 0 1

---

Example 4: Safe Rollout With NOT VALID + VALIDATE (Advanced)

Add a new rule without immediately validating all existing rows:

ALTER TABLE users
ADD CONSTRAINT users_email_has_at
CHECK (position('@' in email) > 1)
NOT VALID;

Expected output:

ALTER TABLE

Validate later (after cleanup/backfill):

ALTER TABLE users
VALIDATE CONSTRAINT users_email_has_at;

Expected output:

ALTER TABLE

Explanation:

• NOT VALID is useful on large tables: new rows must satisfy the rule immediately, while old rows are validated later.

---

Practical Use Cases

1) Enforce Allowed Status Values

Context: prevent typos and unknown states.

CHECK (status IN ('draft', 'submitted', 'approved', 'rejected'))

2) Enforce Cross-Column Rules

Context: a paid invoice must have a paid_at timestamp.

CHECK (status <> 'paid' OR paid_at IS NOT NULL)

3) Tenant-Scoped Uniqueness

Context: email must be unique per tenant.

UNIQUE (tenant_id, email)

4) Conditional Uniqueness (Soft Deletes)

Context: allow reusing an email after a user is soft-deleted.

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE status <> 'deleted';

5) Make Scripts Idempotent

Context: add a constraint only if missing (typically handled by migrations/tools).

-- Many teams handle this at the migration tool layer rather than raw SQL.

---

Common Mistakes & Troubleshooting

Mistake 1: Assuming UNIQUE Blocks Multiple NULLs

Wrong assumption: UNIQUE(email) means only one NULL.

What happens: multiple NULLs are allowed.

Fix options:

-- Option A: disallow NULLs
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Option B: partial unique index (only non-null emails)
CREATE UNIQUE INDEX users_email_nonnull_uniq
ON users (email)
WHERE email IS NOT NULL;

---

Mistake 2: Using varchar(n) as Validation

What happens: you end up doing frequent schema changes when requirements change.

Fix: use text + a CHECK constraint for validation.

---

Mistake 3: Adding Strict Constraints Before Backfilling

What happens: the migration fails because existing data violates the new rule.

Fix: phase it:

ALTER TABLE t ADD CONSTRAINT rule CHECK (...) NOT VALID;
-- backfill/cleanup data
ALTER TABLE t VALIDATE CONSTRAINT rule;

Debugging tips:

1. Inspect constraints with \d+ table_name in psql.
2. When a check fails, the error names the constraint; validate your constraint naming.
3. Use targeted queries to find violating rows (mirror the constraint predicate).
4. For uniqueness issues, query duplicates with GROUP BY ... HAVING COUNT(*) > 1.

---

Best Practices

• ✅ Name constraints (users_status_valid); ❌ leave constraints unnamed.
• ✅ Prefer text + CHECK for domain rules; ❌ treat length limits as business validation.
• ✅ Use NOT VALID + VALIDATE for large-table rollouts; ❌ add strict rules that scan huge tables during peak traffic.
• ✅ Use partial unique indexes for conditional uniqueness; ❌ contort UNIQUE constraints into doing what they cannot.
• ✅ Keep constraints aligned with business invariants; ❌ enforce rules in SQL that your product does not actually require.

---

Hands-On Practice

Use this setup for the exercises:

DROP TABLE IF EXISTS practice_accounts;

CREATE TABLE practice_accounts (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text,
  plan text,
  monthly_cost numeric(12,2)
);

Exercise 1 (Easy): Add NOT NULL + DEFAULT

Task: Make plan NOT NULL and default it to free.

-- Your SQL here

Solution:

ALTER TABLE practice_accounts
ALTER COLUMN plan SET DEFAULT 'free';

ALTER TABLE practice_accounts
ALTER COLUMN plan SET NOT NULL;

---

Exercise 2 (Medium): Add a CHECK constraint

Task: Enforce that monthly_cost is non-negative.

-- Your SQL here

Solution:

ALTER TABLE practice_accounts
ADD CONSTRAINT practice_accounts_cost_nonneg
CHECK (monthly_cost IS NULL OR monthly_cost >= 0);

---

Exercise 3 (Advanced): Conditional uniqueness

Task: Ensure email is unique for rows where plan <> 'cancelled'.

-- Your SQL here

Solution:

CREATE UNIQUE INDEX practice_accounts_email_active_uniq
ON practice_accounts (email)
WHERE plan <> 'cancelled' AND email IS NOT NULL;

---

Connection to Other Concepts

Concept	Why it matters
Primary/foreign keys	PK/FK are constraints that enforce identity/relationships
Indexes	UNIQUE constraints create indexes; conditional uniqueness uses unique indexes
Data types and casting	correct types reduce invalid states and simplify checks
ALTER TABLE	most constraint changes happen via ALTER TABLE
Migrations	constraints should be rolled out in safe, repeatable steps

---

Visual Learning Diagram

flowchart TD
  A[Incoming INSERT/UPDATE] --> B{Constraints}
  B --> C[NOT NULL]
  B --> D[UNIQUE]
  B --> E[CHECK]
  B --> F[DEFAULT]
  C --> G[Accept or Error]
  D --> G
  E --> G
  F --> G

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Relying on app validation only	bad data from other writers	enforce invariants with DB constraints
Assuming UNIQUE blocks NULL duplicates	unexpected duplicates	add NOT NULL or use partial unique index
Adding strict constraints without cleanup	failed migrations	phase with NOT VALID + backfill + validate
Overusing varchar(n)	frequent migrations	use text + CHECK where appropriate
Unnamed constraints	confusing errors	always name constraints

---

Quick Reference

ALTER TABLE t ALTER COLUMN c SET NOT NULL
ALTER TABLE t ALTER COLUMN c SET DEFAULT 'x'
ALTER TABLE t ADD CONSTRAINT name UNIQUE (a, b)
ALTER TABLE t ADD CONSTRAINT name CHECK (expr) NOT VALID
ALTER TABLE t VALIDATE CONSTRAINT name

What's Next

• Previous: Primary and Foreign Keys - Define identity and relationships.
• Next: Index Strategy and Maintenance - Design indexes to match your real query patterns.
• Module Overview - Return to the Constraints and Indexes overview.