Managing Database Constraints
1. Understanding Constraint Types
| Constraint | Enforces |
|---|---|
| NOT NULL | Mandatory value |
| UNIQUE | No duplicate values |
| PRIMARY KEY | UNIQUE + NOT NULL row identifier |
| FOREIGN KEY | Referential integrity |
| CHECK | Boolean validation rule |
| EXCLUSION (Postgres) | 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.
10. Documenting Constraint Business Rules
| Documentation | Content |
|---|---|
| COMMENT ON CONSTRAINT | Inline DB comment |
| Schema docs (SchemaSpy) | Auto-generated reference |
| ADR + business glossary | Rationale + owners |
| Naming convention | Embed intent (chk_age_nonneg) |