No two rows match operator (e.g., overlapping ranges)
DEFAULT
Auto value when omitted (not strictly a constraint)
2. Creating Named Constraints
Pattern
Example
Inline
CONSTRAINT chk_price CHECK (price > 0)
Table-level
CONSTRAINT uq_email UNIQUE(email)
Naming convention
pk_/fk_/uq_/chk_/idx_ prefixes
Note: Always name constraints — auto-generated names make migrations and error messages opaque.
3. Implementing Referential Integrity
Action
Behavior
ON DELETE CASCADE
Delete child rows automatically
ON DELETE SET NULL
Nullify child FK
ON DELETE RESTRICT
Prevent parent delete if children exist
ON UPDATE CASCADE
Propagate PK changes (rarely needed with surrogate keys)
MATCH FULL / SIMPLE
How NULL FK columns are treated
4. Deferring Constraint Checking
Mode
Behavior
NOT DEFERRABLE (default)
Checked per statement
DEFERRABLE INITIALLY IMMEDIATE
Per-statement, but can defer per tx
DEFERRABLE INITIALLY DEFERRED
Checked at COMMIT
SET CONSTRAINTS ALL DEFERRED
Defer all in current tx
Example: Circular FK with Deferral
BEGIN;SET CONSTRAINTS ALL DEFERRED;INSERT INTO a(id, b_id) VALUES (1, 1);INSERT INTO b(id, a_id) VALUES (1, 1);COMMIT;
5. Implementing Domain Constraints
Mechanism
Example
DOMAIN type
CREATE DOMAIN age_t AS INT CHECK (VALUE BETWEEN 0 AND 150);
ENUM type
CREATE TYPE color AS ENUM ('red','green','blue');
CHECK with regex
CHECK (phone ~ '^\+\d{8,15}$')
Lookup table + FK
Most flexible — supports dynamic values
6. Using Cascading Actions
Scenario
Recommended Action
Order → OrderItems
CASCADE (items meaningless without order)
User → Posts
SET NULL or soft-delete (preserve content)
Category → Products
RESTRICT (don't lose products silently)
Audit logs
NO ACTION (never cascade-delete history)
7. Implementing Business Rule Constraints
Mechanism
Use Case
CHECK
Single-row rules (price > 0)
EXCLUSION constraint
No overlapping bookings: EXCLUDE USING gist (room WITH =, when WITH &&)
Trigger
Multi-row/multi-table invariants
Stored procedure + tx
Complex workflows, atomic enforcement
Application layer
Rules requiring external data
8. Handling Constraint Violations
SQLSTATE
Meaning
23502
NOT NULL violation
23503
Foreign key violation
23505
Unique violation
23514
Check violation
23P01
Exclusion violation
Example: ON CONFLICT (Postgres UPSERT)
INSERT INTO users(email, name) VALUES ('a@x.com','Ann')ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
9. Disabling and Enabling Constraints
Operation
SQL
Postgres FK
ALTER TABLE t DISABLE TRIGGER ALL; (FK is via trigger)
MySQL FK
SET FOREIGN_KEY_CHECKS = 0;
Oracle
ALTER TABLE t DISABLE CONSTRAINT name;
SQL Server
ALTER TABLE t NOCHECK CONSTRAINT name;
Validate later
ALTER TABLE t VALIDATE CONSTRAINT name;
Warning: Disabling constraints during bulk loads is faster, but always re-validate before resuming traffic — corrupt data is much worse than slow loads.