Database Management SystemsLogical Design

Logical Design: From Concepts to Schemas

LevelIntermediate

Duration90 mins

TopicLogical Design

4 / 5

Constraint Specification

Constraints: The Guardians of Data Integrity

Constraints are the rules that govern what data can exist in a database. They encode business logic, prevent invalid states, and ensure that no matter how data enters the system—through applications, imports, manual SQL, or integrations—it conforms to defined requirements.

Without constraints, databases become wastelands of inconsistent, invalid, and orphaned data. Applications assume data quality and fail mysteriously when that assumption breaks. Debugging becomes archaeology—sifting through historical data to understand how corruption occurred.

The Defense-in-Depth Principle:

Smart systems validate at multiple layers:

UI Layer: Immediate feedback, user experience
Application Layer: Business logic, complex validations
Database Layer: Last line of defense, canonical enforcement

The database layer is critical because it's the single source of truth. Every access path—web apps, mobile apps, batch jobs, admin scripts, future integrations—passes through the database. Constraints defined here are universally enforced.

Learning Objectives

This page covers the complete constraint taxonomy: domain constraints, key constraints, entity and referential integrity, semantic constraints (CHECK, triggers), and implementation patterns. You'll learn to translate business rules into precise, enforceable database constraints.

Constraint Taxonomy

Database constraints form a hierarchy from simple data type restrictions to complex multi-table business rules. Understanding this taxonomy enables systematic constraint specification.

Classification by Scope:

Attribute Constraints: Apply to single attributes (columns)
- Data type, NOT NULL, DEFAULT, CHECK on single column
Tuple Constraints: Apply across attributes within a single row
- Multi-column CHECK constraints (e.g., end_date >= start_date)
Relation Constraints: Apply across all tuples in a relation
- PRIMARY KEY, UNIQUE, single-table constraints
Database Constraints: Apply across multiple relations
- FOREIGN KEY, cross-table assertions, triggers

Classification by Enforcement Mechanism:

Implicit: Enforced automatically by the DBMS (data types, NOT NULL)
Explicit Declarative: Specified in DDL (PK, FK, UNIQUE, CHECK)
Procedural: Enforced via triggers or stored procedures
Application-Level: Enforced by application code (least reliable)

Complete Constraint Taxonomy
Constraint Type	Scope	SQL Mechanism	Example
Data Type	Attribute	Column definition	age INT
NOT NULL	Attribute	Column modifier	name VARCHAR(100) NOT NULL
DEFAULT	Attribute	Column modifier	status VARCHAR(20) DEFAULT 'active'
CHECK (single)	Attribute	CHECK constraint	CHECK (age >= 0 AND age <= 150)
CHECK (multi)	Tuple	CHECK constraint	CHECK (end_date >= start_date)
PRIMARY KEY	Relation	PRIMARY KEY	PRIMARY KEY (employee_id)
UNIQUE	Relation	UNIQUE constraint	UNIQUE (email)
FOREIGN KEY	Database	FOREIGN KEY	FOREIGN KEY (dept_id) REFERENCES ...
Assertion	Database	CREATE ASSERTION (rarely supported)	Complex cross-table rules
Trigger	Database	CREATE TRIGGER	Arbitrary procedural logic

Prefer Declarative Constraints

Declarative constraints (PRIMARY KEY, FOREIGN KEY, CHECK) are prefereable to procedural ones (triggers). The optimizer understands them, they're self-documenting, they're always enforced, and they require no maintenance. Use triggers only when declarative constraints are insufficient.

Domain and Attribute Constraints

Domain constraints restrict the values an attribute can hold. They are the first line of validation and should be as tight as business requirements allow.

Data Type Selection:

The choice of data type is the most basic constraint. Select types that:

Reject obviously invalid data (can't insert 'hello' into INTEGER)
Minimize storage while accommodating all valid values
Support the operations you need (dates for date arithmetic, etc.)

NOT NULL Constraint:

NOT NULL is critically underused. Ask for every column: "Can this logically be unknown or missing?" If not, make it NOT NULL. This:

Prevents application bugs from creating incomplete records
Eliminates NULL handling complexity in queries
Documents the data model (this value is always present)

DEFAULT Values:

Defaults simplify inserts and establish sensible initial states. Use them for:

Timestamps (CURRENT_TIMESTAMP)
Status fields ('pending', 'active')
Counters (0)
Boolean flags (FALSE)

domain_attribute_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
-- ==================================================
-- DOMAIN AND ATTRIBUTE CONSTRAINTS
-- ==================================================
 
-- Example: User account table with comprehensive attribute constraints
 
CREATE TABLE UserAccount (
    -- Surrogate primary key
    UserID          SERIAL,
    
    -- Username: required, length-constrained, pattern-validated
    Username        VARCHAR(50)     NOT NULL
                    CHECK (LENGTH(Username) >= 3)
                    CHECK (Username ~ '^[a-zA-Z][a-zA-Z0-9_-]*$'),
    
    -- Email: required, standard length, unique
    Email           VARCHAR(254)    NOT NULL,  -- RFC 5321 max length
    
    -- Password hash: required, fixed length (bcrypt outputs 60 chars)
    PasswordHash    CHAR(60)        NOT NULL,
    
    -- Account status: enumerated domain, default value
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
                    CHECK (Status IN ('pending', 'active', 'suspended', 'deleted')),
    
    -- Timestamps: required, automatic defaults
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LastLoginAt     TIMESTAMP,      -- NULL allowed (never logged in)
    
    -- Security counters: required, bounded range
    FailedLoginCount INT            NOT NULL DEFAULT 0
                    CHECK (FailedLoginCount >= 0 AND FailedLoginCount <= 100),
    
    -- Optional profile fields
    DisplayName     VARCHAR(100),
    DateOfBirth     DATE            CHECK (DateOfBirth <= CURRENT_DATE - INTERVAL '13 years'),
    
    -- Keys
    PRIMARY KEY (UserID),
    UNIQUE (Username),
    UNIQUE (Email)
);
 
-- ==================================================
-- CUSTOM DOMAINS (PostgreSQL)
-- ==================================================
 
-- Create reusable domain types
CREATE DOMAIN EmailAddress AS VARCHAR(254)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
CREATE DOMAIN PositiveDecimal AS DECIMAL(19,4)
    CHECK (VALUE > 0);
 
CREATE DOMAIN NonNegativeDecimal AS DECIMAL(19,4)
    CHECK (VALUE >= 0);
 
CREATE DOMAIN PhoneNumberE164 AS VARCHAR(16)
    CHECK (VALUE ~ '^\+[1-9][0-9]{6,14}$');  -- E.164 format
 
CREATE DOMAIN URLString AS VARCHAR(2048)
    CHECK (VALUE ~ '^https?://');
 
CREATE DOMAIN Percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Use domains in table definitions
CREATE TABLE Product (
    ProductID       SERIAL          PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL,
    Price           PositiveDecimal NOT NULL,
    DiscountRate    Percentage      DEFAULT 0,
    ProductURL      URLString
);
 
-- ==================================================
-- ENUM TYPES (Alternative to CHECK constraints)
-- ==================================================
 
-- PostgreSQL enums are type-safe and self-documenting
CREATE TYPE OrderStatus AS ENUM (
    'cart',
    'pending',
    'confirmed',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'refunded'
);
 
CREATE TABLE Orders (
    OrderID         SERIAL          PRIMARY KEY,
    Status          OrderStatus     NOT NULL DEFAULT 'cart',
    -- Enum benefits: type-safe, ordered, storage-efficient
    -- Drawbacks: harder to add values in some positions
    ShippedAt       TIMESTAMP,
    DeliveredAt     TIMESTAMP
);
 
-- ==================================================
-- COMPUTED/GENERATED COLUMNS
-- ==================================================
 
-- Store derived values that depend on other columns
CREATE TABLE OrderLine (
    LineID          SERIAL          PRIMARY KEY,
    OrderID         INT             NOT NULL,
    ProductID       INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice       DECIMAL(10,2)   NOT NULL CHECK (UnitPrice >= 0),
    Discount        DECIMAL(5,2)    NOT NULL DEFAULT 0 CHECK (Discount BETWEEN 0 AND 100),
    
    -- Generated column: always consistent, no update anomaly
    LineTotal       DECIMAL(12,2)   GENERATED ALWAYS AS (
        Quantity * UnitPrice * (1 - Discount / 100)
    ) STORED
);

Validate at the Right Level

Complex pattern matching (regex) in CHECK constraints works but can be slow and database-specific. Consider: simpler database checks for fundamentals, application-layer checks for complex formats, and consistent validation across both layers.

Key Constraints: PRIMARY KEY and UNIQUE

Key constraints enforce tuple uniqueness—ensuring no two rows have the same identifying values. They are fundamental to relational integrity.

PRIMARY KEY:

The primary key constraint combines two guarantees:

Uniqueness: No two rows may have the same PK value(s)
NOT NULL: Primary key columns cannot be NULL

Every table should have exactly one primary key. The choice between natural keys (meaningful business values) and surrogate keys (system-generated) is a design decision with trade-offs:

Natural Keys:

Self-explanatory (ISBN identifies books)
May be compound (slower joins)
Subject to change (SSN policies, rebranding)
May have complex business rules

Surrogate Keys:

Simple integers (fast joins)
Never change (stable references)
Meaningless (requires lookup for understanding)
Require additional unique constraints on business keys

UNIQUE Constraints:

UNIQUE enforces uniqueness but allows NULL values (in most databases, NULLs are not considered equal). Use UNIQUE for:

Alternate candidate keys (email when using surrogate PK)
Business identifiers that must be unique but aren't the PK
Preventing duplicates on non-key columns

key_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- ==================================================
-- PRIMARY KEY AND UNIQUE CONSTRAINTS
-- ==================================================
 
-- Example 1: Surrogate key with natural key as UNIQUE
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,  -- Surrogate PK
    CustomerCode    VARCHAR(20)     NOT NULL UNIQUE,  -- Natural key
    Email           VARCHAR(254)    NOT NULL UNIQUE,  -- Another candidate key
    Name            VARCHAR(100)    NOT NULL,
    Phone           VARCHAR(20)     UNIQUE  -- NULL allowed, but if present must be unique
);
 
-- Example 2: Composite primary key
CREATE TABLE Enrollment (
    StudentID       INT             NOT NULL,
    CourseID        INT             NOT NULL,
    Semester        CHAR(6)         NOT NULL,  -- e.g., '2024SP'
    Grade           DECIMAL(3,2),
    EnrollmentDate  DATE            NOT NULL,
    
    -- Composite PK: student can enroll in course once per semester
    PRIMARY KEY (StudentID, CourseID, Semester),
    
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
 
-- Example 3: Composite UNIQUE (business rule enforcement)
CREATE TABLE EmployeeAssignment (
    AssignmentID    SERIAL          PRIMARY KEY,
    EmployeeID      INT             NOT NULL,
    ProjectID       INT             NOT NULL,
    StartDate       DATE            NOT NULL,
    EndDate         DATE,
    Role            VARCHAR(50)     NOT NULL,
    
    -- Business rule: Employee can't have overlapping assignments to same project
    -- This partial constraint prevents same employee on same project with same start
    UNIQUE (EmployeeID, ProjectID, StartDate),
    
    -- Note: Preventing actual date range overlap requires triggers or exclusion constraints
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)
);
 
-- ==================================================
-- PARTIAL UNIQUE CONSTRAINTS (PostgreSQL)
-- ==================================================
 
-- Only one 'primary' contact per customer
CREATE TABLE CustomerContact (
    ContactID       SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL REFERENCES Customer(CustomerID),
    ContactType     VARCHAR(20)     NOT NULL,  -- 'phone', 'email', 'address'
    ContactValue    VARCHAR(200)    NOT NULL,
    IsPrimary       BOOLEAN         NOT NULL DEFAULT FALSE
);
 
-- Partial unique index: at most one primary contact per customer per type
CREATE UNIQUE INDEX idx_customer_primary_contact 
ON CustomerContact (CustomerID, ContactType) 
WHERE IsPrimary = TRUE;
 
-- ==================================================
-- EXCLUSION CONSTRAINTS (PostgreSQL) - Range overlap prevention
-- ==================================================
 
-- Prevent double-booking: room can't have overlapping reservations
CREATE TABLE RoomReservation (
    ReservationID   SERIAL          PRIMARY KEY,
    RoomID          INT             NOT NULL REFERENCES Room(RoomID),
    StartTime       TIMESTAMP       NOT NULL,
    EndTime         TIMESTAMP       NOT NULL,
    ReservedBy      INT             NOT NULL REFERENCES Employee(EmployeeID),
    
    CHECK (EndTime > StartTime),
    
    -- Exclusion constraint: same room can't have overlapping time ranges
    EXCLUDE USING GIST (
        RoomID WITH =,
        TSRANGE(StartTime, EndTime) WITH &&  -- && means "overlaps"
    )
);
 
-- Requires: CREATE EXTENSION btree_gist;
 
-- ==================================================
-- AUTO-INCREMENT AND SEQUENCES
-- ==================================================
 
-- PostgreSQL SERIAL example
CREATE TABLE Invoice (
    InvoiceID       SERIAL          PRIMARY KEY,  -- Auto-generated
    InvoiceNumber   VARCHAR(20)     NOT NULL UNIQUE,  -- Business identifier
    Amount          DECIMAL(12,2)   NOT NULL
);
 
-- Using IDENTITY (SQL standard, PostgreSQL 10+)
CREATE TABLE Payment (
    PaymentID       INT             GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Amount          DECIMAL(12,2)   NOT NULL
);
 
-- With specific sequence settings
CREATE TABLE Product (
    ProductID       INT             GENERATED BY DEFAULT AS IDENTITY (
                                        START WITH 1000
                                        INCREMENT BY 1
                                    ) PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL
);

UUID vs. Auto-Increment

For distributed systems or when hiding record counts matters, consider UUIDs as primary keys. Trade-offs: larger storage, no natural ordering, potential index fragmentation. Modern UUID versions (v7) are time-ordered to mitigate fragmentation.

Referential Integrity: FOREIGN KEY Constraints

Referential integrity ensures that relationships between tables remain consistent. A foreign key value must either be NULL or match an existing primary/unique key value in the referenced table.

FOREIGN KEY Syntax Components:

The referencing columns: Columns in the child table
The referenced table: Parent table being pointed to
The referenced columns: Primary or unique key in parent (often implicit if PK)
Referential actions: What happens when parent row changes

Referential Actions (ON DELETE / ON UPDATE):

RESTRICT (or NO ACTION): Prevent the parent operation if children exist. Default in most databases.
CASCADE: Apply the same operation to children. Delete parent → delete children.
SET NULL: Set foreign key to NULL in children. Requires nullable FK column.
SET DEFAULT: Set foreign key to its default value in children.

Choosing the right action requires understanding the relationship semantics.

Referential Action Selection Guide
Relationship Type	Recommended ON DELETE	Recommended ON UPDATE	Rationale
Identifying (Weak Entity)	CASCADE	CASCADE	Child has no meaning without parent
Dependent but important	RESTRICT	CASCADE	Don't lose data, but propagate key changes
Optional association	SET NULL	CASCADE	Preserve child, remove association
Lookup/reference data	RESTRICT	RESTRICT	Don't delete categories with products
Audit/log records	SET NULL	SET NULL	Keep logs even if referenced entity deleted

referential_integrity.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
-- ==================================================
-- FOREIGN KEY CONSTRAINTS WITH ACTIONS
-- ==================================================
 
-- Example 1: Identifying relationship (CASCADE)
-- Dependent entity: Order lines have no meaning without their order
CREATE TABLE Orders (
    OrderID         SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL,
    OrderDate       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
);
 
CREATE TABLE OrderLine (
    OrderLineID     SERIAL          PRIMARY KEY,
    OrderID         INT             NOT NULL,
    ProductID       INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice       DECIMAL(10,2)   NOT NULL,
    
    -- CASCADE: delete order → delete its lines
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- RESTRICT: can't delete product if it's on an order
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- Example 2: Optional association (SET NULL)
-- Employee may or may not have a manager; manager leaving doesn't delete employee
CREATE TABLE Employee (
    EmployeeID      SERIAL          PRIMARY KEY,
    Name            VARCHAR(100)    NOT NULL,
    ManagerID       INT,            -- Nullable: CEO has no manager
    DepartmentID    INT             NOT NULL,
    
    -- SET NULL: manager leaves → employee has no manager
    FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    
    -- RESTRICT: can't delete department with employees
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- Example 3: Audit preservation (SET NULL with history)
-- Logs reference users, but we keep logs even if user is deleted
CREATE TABLE AuditLog (
    LogID           SERIAL          PRIMARY KEY,
    Action          VARCHAR(50)     NOT NULL,
    PerformedAt     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UserID          INT,            -- Nullable to allow SET NULL
    UserNameSnapshot VARCHAR(100)   NOT NULL,  -- Preserve name at time of action
    Details         JSONB,
    
    -- SET NULL: user deleted → log preserved with NULL UserID
    FOREIGN KEY (UserID) REFERENCES UserAccount(UserID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);
 
-- Example 4: SET DEFAULT for lookup tables
-- Default category when specific category is removed
CREATE TABLE Product (
    ProductID       SERIAL          PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL,
    CategoryID      INT             NOT NULL DEFAULT 1,  -- Default: 'Uncategorized'
    
    -- SET DEFAULT: category deleted → product moves to default category
    FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);
 
-- The default category must exist and typically has a well-known ID
INSERT INTO Category (CategoryID, CategoryName) VALUES (1, 'Uncategorized');
 
-- ==================================================
-- SELF-REFERENCING FOREIGN KEYS
-- ==================================================
 
-- Hierarchical structures
CREATE TABLE Category (
    CategoryID      SERIAL          PRIMARY KEY,
    CategoryName    VARCHAR(100)    NOT NULL,
    ParentID        INT,            -- NULL = root category
    
    -- Prevent category from being its own parent
    CHECK (CategoryID != ParentID),
    
    -- RESTRICT: can't delete category with subcategories
    FOREIGN KEY (ParentID) REFERENCES Category(CategoryID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- ==================================================
-- DEFERRABLE CONSTRAINTS (for complex inserts)
-- ==================================================
 
-- When circular references need insertion in same transaction
CREATE TABLE Author (
    AuthorID        SERIAL          PRIMARY KEY,
    Name            VARCHAR(100)    NOT NULL,
    FavoriteBookID  INT             -- Circular: refers to Book
);
 
CREATE TABLE Book (
    BookID          SERIAL          PRIMARY KEY,
    Title           VARCHAR(200)    NOT NULL,
    PrimaryAuthorID INT             NOT NULL  -- Refers to Author
);
 
-- Add constraints as DEFERRABLE
ALTER TABLE Author
ADD CONSTRAINT fk_author_favorite_book 
FOREIGN KEY (FavoriteBookID) REFERENCES Book(BookID)
DEFERRABLE INITIALLY DEFERRED;
 
ALTER TABLE Book
ADD CONSTRAINT fk_book_author
FOREIGN KEY (PrimaryAuthorID) REFERENCES Author(AuthorID)
DEFERRABLE INITIALLY DEFERRED;
 
-- Now both can be inserted in same transaction:
-- BEGIN;
-- INSERT INTO Author (AuthorID, Name, FavoriteBookID) VALUES (1, 'Alice', 101);
-- INSERT INTO Book (BookID, Title, PrimaryAuthorID) VALUES (101, 'Great Book', 1);
-- COMMIT;  -- Constraints checked here, both now exist

Cascading Deletes: Use with Caution

CASCADE DELETE is powerful but dangerous. Deleting one parent row can trigger deletion of millions of child rows, potentially causing long locks and unexpected data loss. Use CASCADE only for true compositional relationships where children are meaningless without parents.

CHECK Constraints: Business Rule Enforcement

CHECK constraints encode arbitrary boolean conditions that must be true for every row. They bridge the gap between simple type constraints and complex business rules.

Scope of CHECK Constraints:

Column-level CHECK: References only the column being constrained
- Defined inline with column definition
- Cleaner syntax, self-documenting
Table-level CHECK: Can reference multiple columns
- Defined separately from column definitions
- Required for multi-column conditions

Effective CHECK Patterns:

Range validation: age BETWEEN 0 AND 150
Enumeration: status IN ('draft', 'published', 'archived')
Pattern matching: code ~ '^[A-Z]{2}[0-9]{4}$'
Multi-column logic: end_date >= start_date
Conditional requirements: (type = 'external' AND vendor_id IS NOT NULL) OR type != 'external'

Limitations:

CHECK constraints cannot:

Reference other tables (use triggers or application logic)
Use subqueries (not in standard SQL, some DBs allow)
Call user-defined functions (database-dependent)

check_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- ==================================================
-- CHECK CONSTRAINTS: COMPREHENSIVE EXAMPLES
-- ==================================================
 
-- Example 1: Column-level CHECK constraints
CREATE TABLE Employee (
    EmployeeID      SERIAL          PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL CHECK (LENGTH(FirstName) >= 1),
    LastName        VARCHAR(50)     NOT NULL CHECK (LENGTH(LastName) >= 1),
    
    -- Range constraint
    Age             INT             CHECK (Age BETWEEN 18 AND 100),
    
    -- Positive values only
    Salary          DECIMAL(12,2)   NOT NULL CHECK (Salary > 0),
    
    -- Enumerated values
    EmploymentType  VARCHAR(20)     NOT NULL 
                    CHECK (EmploymentType IN ('full-time', 'part-time', 'contractor', 'intern')),
    
    -- Pattern constraint (employee ID format)
    EmployeeCode    CHAR(8)         NOT NULL 
                    CHECK (EmployeeCode ~ '^E[0-9]{7}$')  -- E + 7 digits
);
 
-- Example 2: Table-level CHECK constraints (multi-column)
CREATE TABLE Project (
    ProjectID       SERIAL          PRIMARY KEY,
    ProjectName     VARCHAR(200)    NOT NULL,
    StartDate       DATE            NOT NULL,
    EndDate         DATE,
    Budget          DECIMAL(15,2)   NOT NULL CHECK (Budget > 0),
    SpentAmount     DECIMAL(15,2)   NOT NULL DEFAULT 0,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'planning',
    
    -- Multi-column: end after start
    CHECK (EndDate IS NULL OR EndDate >= StartDate),
    
    -- Multi-column: can't overspend budget
    CHECK (SpentAmount <= Budget),
    
    -- Conditional: completed projects must have end date
    CHECK (Status != 'completed' OR EndDate IS NOT NULL),
    
    -- Complex: active projects can't be 100% spent
    CHECK (Status != 'active' OR SpentAmount < Budget)
);
 
-- Example 3: Named constraints for better error messages
CREATE TABLE Reservation (
    ReservationID   SERIAL          PRIMARY KEY,
    ResourceID      INT             NOT NULL,
    ReservationType VARCHAR(20)     NOT NULL,
    RequestedBy     INT             NOT NULL,
    ApprovedBy      INT,
    RequestDate     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    StartTime       TIMESTAMP       NOT NULL,
    EndTime         TIMESTAMP       NOT NULL,
    GuestCount      INT,
    
    -- Named constraints: error messages include constraint name
    CONSTRAINT chk_reservation_timing 
        CHECK (EndTime > StartTime),
    
    CONSTRAINT chk_reservation_advance_notice
        CHECK (StartTime >= RequestDate + INTERVAL '1 hour'),
    
    CONSTRAINT chk_reservation_max_duration
        CHECK (EndTime - StartTime <= INTERVAL '8 hours'),
    
    CONSTRAINT chk_reservation_type_valid
        CHECK (ReservationType IN ('meeting', 'presentation', 'workshop', 'event')),
    
    CONSTRAINT chk_reservation_guest_count
        CHECK (GuestCount IS NULL OR GuestCount > 0),
    
    -- Conditional: events require guest count
    CONSTRAINT chk_event_needs_guests
        CHECK (ReservationType != 'event' OR GuestCount IS NOT NULL),
    
    -- Self-approval prevention
    CONSTRAINT chk_no_self_approval
        CHECK (ApprovedBy IS NULL OR ApprovedBy != RequestedBy)
);
 
-- Example 4: Complex business rules with CHECK
CREATE TABLE FinancialTransaction (
    TransactionID   SERIAL          PRIMARY KEY,
    AccountID       INT             NOT NULL,
    TransactionType VARCHAR(20)     NOT NULL,
    Amount          DECIMAL(15,2)   NOT NULL,
    Currency        CHAR(3)         NOT NULL DEFAULT 'USD',
    ExchangeRate    DECIMAL(10,6)   DEFAULT 1.0,
    Reference       VARCHAR(100),
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ProcessedAt     TIMESTAMP,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    
    -- Type determines sign of amount
    CONSTRAINT chk_transaction_amount_sign CHECK (
        (TransactionType IN ('deposit', 'credit', 'refund') AND Amount > 0) OR
        (TransactionType IN ('withdrawal', 'debit', 'charge') AND Amount < 0) OR
        (TransactionType = 'adjustment')  -- adjustments can be +/-
    ),
    
    -- Non-USD transactions require exchange rate
    CONSTRAINT chk_exchange_rate_required CHECK (
        Currency = 'USD' OR ExchangeRate IS NOT NULL
    ),
    
    -- Status progression constraints
    CONSTRAINT chk_status_valid CHECK (
        Status IN ('pending', 'processing', 'completed', 'failed', 'reversed')
    ),
    
    -- Completed/failed transactions have processed timestamp
    CONSTRAINT chk_processed_timestamp CHECK (
        Status IN ('pending', 'processing') OR ProcessedAt IS NOT NULL
    ),
    
    -- Processing time cannot precede creation
    CONSTRAINT chk_processing_after_creation CHECK (
        ProcessedAt IS NULL OR ProcessedAt >= CreatedAt
    )
);

Constraint Naming Convention

Always name your CHECK constraints with a clear pattern: chk_tablename_rulename. When constraints are violated, the error message includes the constraint name. Named constraints make debugging and maintenance far easier than default generated names like 'check_constraint_1'.

Triggers for Complex Constraints

When declarative constraints aren't sufficient, triggers provide procedural enforcement of complex business rules. Triggers execute automatically before or after insert, update, or delete operations.

When Triggers Are Necessary:

Cross-table constraints: Checking values in other tables
Aggregate constraints: Limits based on counts or sums
Historical constraints: Rules involving previous values
Complex state machines: Valid status transitions
Derived value maintenance: Keeping computed values updated

Trigger Design Principles:

Prefer BEFORE triggers for validation: Reject early, before data is committed
Use AFTER triggers for side effects: Logging, notifications, cascading updates
Keep triggers simple: Complex logic should be in stored procedures
Document thoroughly: Triggers are hidden logic; make them discoverable
Consider performance: Triggers run for every affected row; avoid expensive operations

trigger_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
-- ==================================================
-- TRIGGER-BASED CONSTRAINTS (PostgreSQL)
-- ==================================================
 
-- Example 1: Cross-table constraint
-- Prevent assigning more employees to project than budgeted capacity
 
CREATE TABLE Project (
    ProjectID       SERIAL          PRIMARY KEY,
    ProjectName     VARCHAR(200)    NOT NULL,
    MaxTeamSize     INT             NOT NULL CHECK (MaxTeamSize > 0)
);
 
CREATE TABLE ProjectAssignment (
    AssignmentID    SERIAL          PRIMARY KEY,
    ProjectID       INT             NOT NULL REFERENCES Project(ProjectID),
    EmployeeID      INT             NOT NULL REFERENCES Employee(EmployeeID),
    AssignedDate    DATE            NOT NULL DEFAULT CURRENT_DATE,
    UNIQUE (ProjectID, EmployeeID)
);
 
-- Function to check team size limit
CREATE OR REPLACE FUNCTION check_project_team_size()
RETURNS TRIGGER AS $$
DECLARE
    current_count INT;
    max_size INT;
BEGIN
    -- Get current assignment count and limit
    SELECT COUNT(*), p.MaxTeamSize
    INTO current_count, max_size
    FROM ProjectAssignment pa
    JOIN Project p ON pa.ProjectID = p.ProjectID
    WHERE pa.ProjectID = NEW.ProjectID
    GROUP BY p.MaxTeamSize;
    
    -- Check limit (for INSERT, new row not counted yet)
    IF TG_OP = 'INSERT' AND current_count >= max_size THEN
        RAISE EXCEPTION 'Project % has reached maximum team size of %', 
            NEW.ProjectID, max_size;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_check_project_team_size
BEFORE INSERT ON ProjectAssignment
FOR EACH ROW EXECUTE FUNCTION check_project_team_size();
 
 
-- Example 2: State machine enforcement
-- Only allow valid status transitions
 
CREATE TABLE Order_Status_Machine (
    OrderID         SERIAL          PRIMARY KEY,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
);
 
CREATE OR REPLACE FUNCTION enforce_order_status_transition()
RETURNS TRIGGER AS $$
BEGIN
    -- Define allowed transitions
    IF OLD.Status = 'pending' AND NEW.Status NOT IN ('confirmed', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: pending -> %', NEW.Status;
    ELSIF OLD.Status = 'confirmed' AND NEW.Status NOT IN ('processing', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: confirmed -> %', NEW.Status;
    ELSIF OLD.Status = 'processing' AND NEW.Status NOT IN ('shipped', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: processing -> %', NEW.Status;
    ELSIF OLD.Status = 'shipped' AND NEW.Status NOT IN ('delivered') THEN
        RAISE EXCEPTION 'Invalid transition: shipped -> %', NEW.Status;
    ELSIF OLD.Status = 'delivered' AND NEW.Status NOT IN ('refunded') THEN
        RAISE EXCEPTION 'Invalid transition: delivered -> %', NEW.Status;
    ELSIF OLD.Status IN ('cancelled', 'refunded') THEN
        RAISE EXCEPTION 'Cannot transition from terminal state: %', OLD.Status;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_order_status_transition
BEFORE UPDATE OF Status ON Order_Status_Machine
FOR EACH ROW EXECUTE FUNCTION enforce_order_status_transition();
 
 
-- Example 3: Aggregate constraint with derived column maintenance
-- Maintain customer's total order value with limit check
 
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,
    CustomerName    VARCHAR(100)    NOT NULL,
    CreditLimit     DECIMAL(12,2)   NOT NULL DEFAULT 10000,
    TotalOrderValue DECIMAL(12,2)   NOT NULL DEFAULT 0  -- Denormalized aggregate
);
 
CREATE TABLE CustomerOrder (
    OrderID         SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL REFERENCES Customer(CustomerID),
    OrderTotal      DECIMAL(12,2)   NOT NULL CHECK (OrderTotal > 0),
    OrderDate       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE OR REPLACE FUNCTION update_customer_order_total()
RETURNS TRIGGER AS $$
DECLARE
    new_total DECIMAL(12,2);
    credit_limit DECIMAL(12,2);
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- Check if new order would exceed credit limit
        SELECT c.TotalOrderValue + NEW.OrderTotal, c.CreditLimit
        INTO new_total, credit_limit
        FROM Customer c
        WHERE c.CustomerID = NEW.CustomerID;
        
        IF new_total > credit_limit THEN
            RAISE EXCEPTION 'Order would exceed credit limit. Current: %, Order: %, Limit: %',
                new_total - NEW.OrderTotal, NEW.OrderTotal, credit_limit;
        END IF;
        
        -- Update aggregate
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue + NEW.OrderTotal
        WHERE CustomerID = NEW.CustomerID;
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- Adjust for difference
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue + (NEW.OrderTotal - OLD.OrderTotal)
        WHERE CustomerID = NEW.CustomerID;
        
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue - OLD.OrderTotal
        WHERE CustomerID = OLD.CustomerID;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_customer_order_total
AFTER INSERT OR UPDATE OR DELETE ON CustomerOrder
FOR EACH ROW EXECUTE FUNCTION update_customer_order_total();
 
 
-- Example 4: Immutability constraint (prevent updates to certain fields)
CREATE OR REPLACE FUNCTION prevent_field_modification()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.CreatedAt != OLD.CreatedAt THEN
        RAISE EXCEPTION 'CreatedAt is immutable';
    END IF;
    IF NEW.CreatedBy != OLD.CreatedBy THEN
        RAISE EXCEPTION 'CreatedBy is immutable';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger Performance Impact

Triggers add latency to every affected operation. A trigger that queries other tables can cause lock contention and deadlocks in high-concurrency environments. Profile trigger impact and consider asynchronous enforcement (queues, eventual consistency) for expensive checks.

Constraint Implementation Strategy

Effective constraint implementation requires strategic thinking about where to enforce each business rule. The goal is maximum data integrity with minimal performance impact and maintenance burden.

Decision Framework:

For each business rule, consider:

Can it be expressed declaratively? If yes, use CHECK, UNIQUE, or FOREIGN KEY.
Does it span multiple tables? If yes, consider triggers or application logic.
How expensive is validation? Expensive checks may need batching or eventual consistency.
How critical is guaranteed enforcement? Critical rules need database-level constraints.
Is it presentation or storage logic? Presentation rules (formatting) belong in the application.

Constraint Placement Decision Matrix

•Data type constraints → Database schema: Always. This is fundamental.
•NOT NULL → Database schema: Almost always. Make exceptions only for truly optional data.
•Value ranges/enumerations → CHECK constraints: When values are simple and static.
•Complex enumerations → Lookup tables with FK: When values need metadata or change frequently.
•Uniqueness → UNIQUE constraints: Always enforce at database level, even if also checking in app.
•Referential integrity → FOREIGN KEYs: Almost always. Exceptions for extreme performance cases.
•Cross-table rules → Triggers or application: Choose based on complexity and performance.
•Format validation → Application with database backup: App for UX, database CHECK for safety.

Constraint Enforcement Layer Trade-offs
Layer	Pros	Cons	Best For
Database (Declarative)	Always enforced, optimizer-aware, atomic	Limited expressiveness, DB-specific syntax	Core integrity rules
Database (Triggers)	Always enforced, full SQL power	Hidden logic, performance impact, complexity	Cross-table rules, audit
Application Logic	Full language power, easy testing	Bypassable, must duplicate across apps	Complex business rules
API/Middleware	Centralized, cross-app enforcement	Bypassable via direct DB access	Integration validation
UI/Frontend	Immediate feedback, good UX	Easily bypassed, not authoritative	User guidance only

The Golden Rule

Enforce as much as possible at the database level. The database is the single source of truth—every access path passes through it. Application-level enforcement is supplementary, improving UX and catching errors early, but the database is the last line of defense.

Summary and Key Takeaways

Constraints transform a database from a simple data container into a guardian of business rules. Let's consolidate the essential knowledge:

Constraint Specification Essentials

•Constraints form a hierarchy from attribute-level (type, NOT NULL) through relation-level (PK, UNIQUE) to database-level (FK, triggers).
•Domain constraints (data types, NOT NULL, CHECK) are the first line of defense—make them as restrictive as business rules allow.
•Key constraints (PRIMARY KEY, UNIQUE) ensure tuple uniqueness and enable relationships.
•Foreign keys enforce referential integrity with configurable actions (CASCADE, RESTRICT, SET NULL).
•CHECK constraints encode arbitrary boolean conditions; name them for clear error messages.
•Triggers handle complex rules that declarative constraints cannot express; use sparingly.
•Prefer declarative to procedural: optimizer understands declarative constraints; triggers are hidden logic.
•Defense in depth: validate at UI and application layers for UX, but always enforce at the database.

What Comes Next:

With mapping, schema specification, normalization, and constraints covered, we complete logical design with refinement—the iterative process of reviewing, validating, and optimizing the schema before physical design. Refinement ensures the logical schema is ready for implementation.

Page Complete

You now command the complete constraint toolkit: from simple NOT NULL declarations through complex trigger-based enforcement. This knowledge enables you to encode business rules directly into database structure, ensuring data integrity regardless of how applications access the data.

4 / 5

Loading learning content...

Database Management SystemsLogical Design

Logical Design: From Concepts to Schemas

LevelIntermediate

Duration90 mins

TopicLogical Design

4 / 5

Constraint Specification

Constraints: The Guardians of Data Integrity

The Defense-in-Depth Principle:

Smart systems validate at multiple layers:

UI Layer: Immediate feedback, user experience
Application Layer: Business logic, complex validations
Database Layer: Last line of defense, canonical enforcement

Learning Objectives

Constraint Taxonomy

Database constraints form a hierarchy from simple data type restrictions to complex multi-table business rules. Understanding this taxonomy enables systematic constraint specification.

Classification by Scope:

Attribute Constraints: Apply to single attributes (columns)
- Data type, NOT NULL, DEFAULT, CHECK on single column
Tuple Constraints: Apply across attributes within a single row
- Multi-column CHECK constraints (e.g., end_date >= start_date)
Relation Constraints: Apply across all tuples in a relation
- PRIMARY KEY, UNIQUE, single-table constraints
Database Constraints: Apply across multiple relations
- FOREIGN KEY, cross-table assertions, triggers

Classification by Enforcement Mechanism:

Implicit: Enforced automatically by the DBMS (data types, NOT NULL)
Explicit Declarative: Specified in DDL (PK, FK, UNIQUE, CHECK)
Procedural: Enforced via triggers or stored procedures
Application-Level: Enforced by application code (least reliable)

Complete Constraint Taxonomy
Constraint Type	Scope	SQL Mechanism	Example
Data Type	Attribute	Column definition	age INT
NOT NULL	Attribute	Column modifier	name VARCHAR(100) NOT NULL
DEFAULT	Attribute	Column modifier	status VARCHAR(20) DEFAULT 'active'
CHECK (single)	Attribute	CHECK constraint	CHECK (age >= 0 AND age <= 150)
CHECK (multi)	Tuple	CHECK constraint	CHECK (end_date >= start_date)
PRIMARY KEY	Relation	PRIMARY KEY	PRIMARY KEY (employee_id)
UNIQUE	Relation	UNIQUE constraint	UNIQUE (email)
FOREIGN KEY	Database	FOREIGN KEY	FOREIGN KEY (dept_id) REFERENCES ...
Assertion	Database	CREATE ASSERTION (rarely supported)	Complex cross-table rules
Trigger	Database	CREATE TRIGGER	Arbitrary procedural logic

Prefer Declarative Constraints

Domain and Attribute Constraints

Domain constraints restrict the values an attribute can hold. They are the first line of validation and should be as tight as business requirements allow.

Data Type Selection:

The choice of data type is the most basic constraint. Select types that:

Reject obviously invalid data (can't insert 'hello' into INTEGER)
Minimize storage while accommodating all valid values
Support the operations you need (dates for date arithmetic, etc.)

NOT NULL Constraint:

NOT NULL is critically underused. Ask for every column: "Can this logically be unknown or missing?" If not, make it NOT NULL. This:

Prevents application bugs from creating incomplete records
Eliminates NULL handling complexity in queries
Documents the data model (this value is always present)

DEFAULT Values:

Defaults simplify inserts and establish sensible initial states. Use them for:

Timestamps (CURRENT_TIMESTAMP)
Status fields ('pending', 'active')
Counters (0)
Boolean flags (FALSE)

domain_attribute_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
-- ==================================================
-- DOMAIN AND ATTRIBUTE CONSTRAINTS
-- ==================================================
 
-- Example: User account table with comprehensive attribute constraints
 
CREATE TABLE UserAccount (
    -- Surrogate primary key
    UserID          SERIAL,
    
    -- Username: required, length-constrained, pattern-validated
    Username        VARCHAR(50)     NOT NULL
                    CHECK (LENGTH(Username) >= 3)
                    CHECK (Username ~ '^[a-zA-Z][a-zA-Z0-9_-]*$'),
    
    -- Email: required, standard length, unique
    Email           VARCHAR(254)    NOT NULL,  -- RFC 5321 max length
    
    -- Password hash: required, fixed length (bcrypt outputs 60 chars)
    PasswordHash    CHAR(60)        NOT NULL,
    
    -- Account status: enumerated domain, default value
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
                    CHECK (Status IN ('pending', 'active', 'suspended', 'deleted')),
    
    -- Timestamps: required, automatic defaults
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LastLoginAt     TIMESTAMP,      -- NULL allowed (never logged in)
    
    -- Security counters: required, bounded range
    FailedLoginCount INT            NOT NULL DEFAULT 0
                    CHECK (FailedLoginCount >= 0 AND FailedLoginCount <= 100),
    
    -- Optional profile fields
    DisplayName     VARCHAR(100),
    DateOfBirth     DATE            CHECK (DateOfBirth <= CURRENT_DATE - INTERVAL '13 years'),
    
    -- Keys
    PRIMARY KEY (UserID),
    UNIQUE (Username),
    UNIQUE (Email)
);
 
-- ==================================================
-- CUSTOM DOMAINS (PostgreSQL)
-- ==================================================
 
-- Create reusable domain types
CREATE DOMAIN EmailAddress AS VARCHAR(254)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
CREATE DOMAIN PositiveDecimal AS DECIMAL(19,4)
    CHECK (VALUE > 0);
 
CREATE DOMAIN NonNegativeDecimal AS DECIMAL(19,4)
    CHECK (VALUE >= 0);
 
CREATE DOMAIN PhoneNumberE164 AS VARCHAR(16)
    CHECK (VALUE ~ '^\+[1-9][0-9]{6,14}$');  -- E.164 format
 
CREATE DOMAIN URLString AS VARCHAR(2048)
    CHECK (VALUE ~ '^https?://');
 
CREATE DOMAIN Percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Use domains in table definitions
CREATE TABLE Product (
    ProductID       SERIAL          PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL,
    Price           PositiveDecimal NOT NULL,
    DiscountRate    Percentage      DEFAULT 0,
    ProductURL      URLString
);
 
-- ==================================================
-- ENUM TYPES (Alternative to CHECK constraints)
-- ==================================================
 
-- PostgreSQL enums are type-safe and self-documenting
CREATE TYPE OrderStatus AS ENUM (
    'cart',
    'pending',
    'confirmed',
    'processing',
    'shipped',
    'delivered',
    'cancelled',
    'refunded'
);
 
CREATE TABLE Orders (
    OrderID         SERIAL          PRIMARY KEY,
    Status          OrderStatus     NOT NULL DEFAULT 'cart',
    -- Enum benefits: type-safe, ordered, storage-efficient
    -- Drawbacks: harder to add values in some positions
    ShippedAt       TIMESTAMP,
    DeliveredAt     TIMESTAMP
);
 
-- ==================================================
-- COMPUTED/GENERATED COLUMNS
-- ==================================================
 
-- Store derived values that depend on other columns
CREATE TABLE OrderLine (
    LineID          SERIAL          PRIMARY KEY,
    OrderID         INT             NOT NULL,
    ProductID       INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice       DECIMAL(10,2)   NOT NULL CHECK (UnitPrice >= 0),
    Discount        DECIMAL(5,2)    NOT NULL DEFAULT 0 CHECK (Discount BETWEEN 0 AND 100),
    
    -- Generated column: always consistent, no update anomaly
    LineTotal       DECIMAL(12,2)   GENERATED ALWAYS AS (
        Quantity * UnitPrice * (1 - Discount / 100)
    ) STORED
);

Validate at the Right Level

Key Constraints: PRIMARY KEY and UNIQUE

Key constraints enforce tuple uniqueness—ensuring no two rows have the same identifying values. They are fundamental to relational integrity.

PRIMARY KEY:

The primary key constraint combines two guarantees:

Uniqueness: No two rows may have the same PK value(s)
NOT NULL: Primary key columns cannot be NULL

Every table should have exactly one primary key. The choice between natural keys (meaningful business values) and surrogate keys (system-generated) is a design decision with trade-offs:

Natural Keys:

Self-explanatory (ISBN identifies books)
May be compound (slower joins)
Subject to change (SSN policies, rebranding)
May have complex business rules

Surrogate Keys:

Simple integers (fast joins)
Never change (stable references)
Meaningless (requires lookup for understanding)
Require additional unique constraints on business keys

UNIQUE Constraints:

UNIQUE enforces uniqueness but allows NULL values (in most databases, NULLs are not considered equal). Use UNIQUE for:

Alternate candidate keys (email when using surrogate PK)
Business identifiers that must be unique but aren't the PK
Preventing duplicates on non-key columns

key_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- ==================================================
-- PRIMARY KEY AND UNIQUE CONSTRAINTS
-- ==================================================
 
-- Example 1: Surrogate key with natural key as UNIQUE
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,  -- Surrogate PK
    CustomerCode    VARCHAR(20)     NOT NULL UNIQUE,  -- Natural key
    Email           VARCHAR(254)    NOT NULL UNIQUE,  -- Another candidate key
    Name            VARCHAR(100)    NOT NULL,
    Phone           VARCHAR(20)     UNIQUE  -- NULL allowed, but if present must be unique
);
 
-- Example 2: Composite primary key
CREATE TABLE Enrollment (
    StudentID       INT             NOT NULL,
    CourseID        INT             NOT NULL,
    Semester        CHAR(6)         NOT NULL,  -- e.g., '2024SP'
    Grade           DECIMAL(3,2),
    EnrollmentDate  DATE            NOT NULL,
    
    -- Composite PK: student can enroll in course once per semester
    PRIMARY KEY (StudentID, CourseID, Semester),
    
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
 
-- Example 3: Composite UNIQUE (business rule enforcement)
CREATE TABLE EmployeeAssignment (
    AssignmentID    SERIAL          PRIMARY KEY,
    EmployeeID      INT             NOT NULL,
    ProjectID       INT             NOT NULL,
    StartDate       DATE            NOT NULL,
    EndDate         DATE,
    Role            VARCHAR(50)     NOT NULL,
    
    -- Business rule: Employee can't have overlapping assignments to same project
    -- This partial constraint prevents same employee on same project with same start
    UNIQUE (EmployeeID, ProjectID, StartDate),
    
    -- Note: Preventing actual date range overlap requires triggers or exclusion constraints
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)
);
 
-- ==================================================
-- PARTIAL UNIQUE CONSTRAINTS (PostgreSQL)
-- ==================================================
 
-- Only one 'primary' contact per customer
CREATE TABLE CustomerContact (
    ContactID       SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL REFERENCES Customer(CustomerID),
    ContactType     VARCHAR(20)     NOT NULL,  -- 'phone', 'email', 'address'
    ContactValue    VARCHAR(200)    NOT NULL,
    IsPrimary       BOOLEAN         NOT NULL DEFAULT FALSE
);
 
-- Partial unique index: at most one primary contact per customer per type
CREATE UNIQUE INDEX idx_customer_primary_contact 
ON CustomerContact (CustomerID, ContactType) 
WHERE IsPrimary = TRUE;
 
-- ==================================================
-- EXCLUSION CONSTRAINTS (PostgreSQL) - Range overlap prevention
-- ==================================================
 
-- Prevent double-booking: room can't have overlapping reservations
CREATE TABLE RoomReservation (
    ReservationID   SERIAL          PRIMARY KEY,
    RoomID          INT             NOT NULL REFERENCES Room(RoomID),
    StartTime       TIMESTAMP       NOT NULL,
    EndTime         TIMESTAMP       NOT NULL,
    ReservedBy      INT             NOT NULL REFERENCES Employee(EmployeeID),
    
    CHECK (EndTime > StartTime),
    
    -- Exclusion constraint: same room can't have overlapping time ranges
    EXCLUDE USING GIST (
        RoomID WITH =,
        TSRANGE(StartTime, EndTime) WITH &&  -- && means "overlaps"
    )
);
 
-- Requires: CREATE EXTENSION btree_gist;
 
-- ==================================================
-- AUTO-INCREMENT AND SEQUENCES
-- ==================================================
 
-- PostgreSQL SERIAL example
CREATE TABLE Invoice (
    InvoiceID       SERIAL          PRIMARY KEY,  -- Auto-generated
    InvoiceNumber   VARCHAR(20)     NOT NULL UNIQUE,  -- Business identifier
    Amount          DECIMAL(12,2)   NOT NULL
);
 
-- Using IDENTITY (SQL standard, PostgreSQL 10+)
CREATE TABLE Payment (
    PaymentID       INT             GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Amount          DECIMAL(12,2)   NOT NULL
);
 
-- With specific sequence settings
CREATE TABLE Product (
    ProductID       INT             GENERATED BY DEFAULT AS IDENTITY (
                                        START WITH 1000
                                        INCREMENT BY 1
                                    ) PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL
);

UUID vs. Auto-Increment

Referential Integrity: FOREIGN KEY Constraints

Referential integrity ensures that relationships between tables remain consistent. A foreign key value must either be NULL or match an existing primary/unique key value in the referenced table.

FOREIGN KEY Syntax Components:

The referencing columns: Columns in the child table
The referenced table: Parent table being pointed to
The referenced columns: Primary or unique key in parent (often implicit if PK)
Referential actions: What happens when parent row changes

Referential Actions (ON DELETE / ON UPDATE):

RESTRICT (or NO ACTION): Prevent the parent operation if children exist. Default in most databases.
CASCADE: Apply the same operation to children. Delete parent → delete children.
SET NULL: Set foreign key to NULL in children. Requires nullable FK column.
SET DEFAULT: Set foreign key to its default value in children.

Choosing the right action requires understanding the relationship semantics.

Referential Action Selection Guide
Relationship Type	Recommended ON DELETE	Recommended ON UPDATE	Rationale
Identifying (Weak Entity)	CASCADE	CASCADE	Child has no meaning without parent
Dependent but important	RESTRICT	CASCADE	Don't lose data, but propagate key changes
Optional association	SET NULL	CASCADE	Preserve child, remove association
Lookup/reference data	RESTRICT	RESTRICT	Don't delete categories with products
Audit/log records	SET NULL	SET NULL	Keep logs even if referenced entity deleted

referential_integrity.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
-- ==================================================
-- FOREIGN KEY CONSTRAINTS WITH ACTIONS
-- ==================================================
 
-- Example 1: Identifying relationship (CASCADE)
-- Dependent entity: Order lines have no meaning without their order
CREATE TABLE Orders (
    OrderID         SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL,
    OrderDate       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
);
 
CREATE TABLE OrderLine (
    OrderLineID     SERIAL          PRIMARY KEY,
    OrderID         INT             NOT NULL,
    ProductID       INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice       DECIMAL(10,2)   NOT NULL,
    
    -- CASCADE: delete order → delete its lines
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- RESTRICT: can't delete product if it's on an order
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- Example 2: Optional association (SET NULL)
-- Employee may or may not have a manager; manager leaving doesn't delete employee
CREATE TABLE Employee (
    EmployeeID      SERIAL          PRIMARY KEY,
    Name            VARCHAR(100)    NOT NULL,
    ManagerID       INT,            -- Nullable: CEO has no manager
    DepartmentID    INT             NOT NULL,
    
    -- SET NULL: manager leaves → employee has no manager
    FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    
    -- RESTRICT: can't delete department with employees
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- Example 3: Audit preservation (SET NULL with history)
-- Logs reference users, but we keep logs even if user is deleted
CREATE TABLE AuditLog (
    LogID           SERIAL          PRIMARY KEY,
    Action          VARCHAR(50)     NOT NULL,
    PerformedAt     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UserID          INT,            -- Nullable to allow SET NULL
    UserNameSnapshot VARCHAR(100)   NOT NULL,  -- Preserve name at time of action
    Details         JSONB,
    
    -- SET NULL: user deleted → log preserved with NULL UserID
    FOREIGN KEY (UserID) REFERENCES UserAccount(UserID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);
 
-- Example 4: SET DEFAULT for lookup tables
-- Default category when specific category is removed
CREATE TABLE Product (
    ProductID       SERIAL          PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL,
    CategoryID      INT             NOT NULL DEFAULT 1,  -- Default: 'Uncategorized'
    
    -- SET DEFAULT: category deleted → product moves to default category
    FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
        ON DELETE SET DEFAULT
        ON UPDATE CASCADE
);
 
-- The default category must exist and typically has a well-known ID
INSERT INTO Category (CategoryID, CategoryName) VALUES (1, 'Uncategorized');
 
-- ==================================================
-- SELF-REFERENCING FOREIGN KEYS
-- ==================================================
 
-- Hierarchical structures
CREATE TABLE Category (
    CategoryID      SERIAL          PRIMARY KEY,
    CategoryName    VARCHAR(100)    NOT NULL,
    ParentID        INT,            -- NULL = root category
    
    -- Prevent category from being its own parent
    CHECK (CategoryID != ParentID),
    
    -- RESTRICT: can't delete category with subcategories
    FOREIGN KEY (ParentID) REFERENCES Category(CategoryID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- ==================================================
-- DEFERRABLE CONSTRAINTS (for complex inserts)
-- ==================================================
 
-- When circular references need insertion in same transaction
CREATE TABLE Author (
    AuthorID        SERIAL          PRIMARY KEY,
    Name            VARCHAR(100)    NOT NULL,
    FavoriteBookID  INT             -- Circular: refers to Book
);
 
CREATE TABLE Book (
    BookID          SERIAL          PRIMARY KEY,
    Title           VARCHAR(200)    NOT NULL,
    PrimaryAuthorID INT             NOT NULL  -- Refers to Author
);
 
-- Add constraints as DEFERRABLE
ALTER TABLE Author
ADD CONSTRAINT fk_author_favorite_book 
FOREIGN KEY (FavoriteBookID) REFERENCES Book(BookID)
DEFERRABLE INITIALLY DEFERRED;
 
ALTER TABLE Book
ADD CONSTRAINT fk_book_author
FOREIGN KEY (PrimaryAuthorID) REFERENCES Author(AuthorID)
DEFERRABLE INITIALLY DEFERRED;
 
-- Now both can be inserted in same transaction:
-- BEGIN;
-- INSERT INTO Author (AuthorID, Name, FavoriteBookID) VALUES (1, 'Alice', 101);
-- INSERT INTO Book (BookID, Title, PrimaryAuthorID) VALUES (101, 'Great Book', 1);
-- COMMIT;  -- Constraints checked here, both now exist

Cascading Deletes: Use with Caution

CHECK Constraints: Business Rule Enforcement

CHECK constraints encode arbitrary boolean conditions that must be true for every row. They bridge the gap between simple type constraints and complex business rules.

Scope of CHECK Constraints:

Column-level CHECK: References only the column being constrained
- Defined inline with column definition
- Cleaner syntax, self-documenting
Table-level CHECK: Can reference multiple columns
- Defined separately from column definitions
- Required for multi-column conditions

Effective CHECK Patterns:

Range validation: age BETWEEN 0 AND 150
Enumeration: status IN ('draft', 'published', 'archived')
Pattern matching: code ~ '^[A-Z]{2}[0-9]{4}$'
Multi-column logic: end_date >= start_date
Conditional requirements: (type = 'external' AND vendor_id IS NOT NULL) OR type != 'external'

Limitations:

CHECK constraints cannot:

Reference other tables (use triggers or application logic)
Use subqueries (not in standard SQL, some DBs allow)
Call user-defined functions (database-dependent)

check_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- ==================================================
-- CHECK CONSTRAINTS: COMPREHENSIVE EXAMPLES
-- ==================================================
 
-- Example 1: Column-level CHECK constraints
CREATE TABLE Employee (
    EmployeeID      SERIAL          PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL CHECK (LENGTH(FirstName) >= 1),
    LastName        VARCHAR(50)     NOT NULL CHECK (LENGTH(LastName) >= 1),
    
    -- Range constraint
    Age             INT             CHECK (Age BETWEEN 18 AND 100),
    
    -- Positive values only
    Salary          DECIMAL(12,2)   NOT NULL CHECK (Salary > 0),
    
    -- Enumerated values
    EmploymentType  VARCHAR(20)     NOT NULL 
                    CHECK (EmploymentType IN ('full-time', 'part-time', 'contractor', 'intern')),
    
    -- Pattern constraint (employee ID format)
    EmployeeCode    CHAR(8)         NOT NULL 
                    CHECK (EmployeeCode ~ '^E[0-9]{7}$')  -- E + 7 digits
);
 
-- Example 2: Table-level CHECK constraints (multi-column)
CREATE TABLE Project (
    ProjectID       SERIAL          PRIMARY KEY,
    ProjectName     VARCHAR(200)    NOT NULL,
    StartDate       DATE            NOT NULL,
    EndDate         DATE,
    Budget          DECIMAL(15,2)   NOT NULL CHECK (Budget > 0),
    SpentAmount     DECIMAL(15,2)   NOT NULL DEFAULT 0,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'planning',
    
    -- Multi-column: end after start
    CHECK (EndDate IS NULL OR EndDate >= StartDate),
    
    -- Multi-column: can't overspend budget
    CHECK (SpentAmount <= Budget),
    
    -- Conditional: completed projects must have end date
    CHECK (Status != 'completed' OR EndDate IS NOT NULL),
    
    -- Complex: active projects can't be 100% spent
    CHECK (Status != 'active' OR SpentAmount < Budget)
);
 
-- Example 3: Named constraints for better error messages
CREATE TABLE Reservation (
    ReservationID   SERIAL          PRIMARY KEY,
    ResourceID      INT             NOT NULL,
    ReservationType VARCHAR(20)     NOT NULL,
    RequestedBy     INT             NOT NULL,
    ApprovedBy      INT,
    RequestDate     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    StartTime       TIMESTAMP       NOT NULL,
    EndTime         TIMESTAMP       NOT NULL,
    GuestCount      INT,
    
    -- Named constraints: error messages include constraint name
    CONSTRAINT chk_reservation_timing 
        CHECK (EndTime > StartTime),
    
    CONSTRAINT chk_reservation_advance_notice
        CHECK (StartTime >= RequestDate + INTERVAL '1 hour'),
    
    CONSTRAINT chk_reservation_max_duration
        CHECK (EndTime - StartTime <= INTERVAL '8 hours'),
    
    CONSTRAINT chk_reservation_type_valid
        CHECK (ReservationType IN ('meeting', 'presentation', 'workshop', 'event')),
    
    CONSTRAINT chk_reservation_guest_count
        CHECK (GuestCount IS NULL OR GuestCount > 0),
    
    -- Conditional: events require guest count
    CONSTRAINT chk_event_needs_guests
        CHECK (ReservationType != 'event' OR GuestCount IS NOT NULL),
    
    -- Self-approval prevention
    CONSTRAINT chk_no_self_approval
        CHECK (ApprovedBy IS NULL OR ApprovedBy != RequestedBy)
);
 
-- Example 4: Complex business rules with CHECK
CREATE TABLE FinancialTransaction (
    TransactionID   SERIAL          PRIMARY KEY,
    AccountID       INT             NOT NULL,
    TransactionType VARCHAR(20)     NOT NULL,
    Amount          DECIMAL(15,2)   NOT NULL,
    Currency        CHAR(3)         NOT NULL DEFAULT 'USD',
    ExchangeRate    DECIMAL(10,6)   DEFAULT 1.0,
    Reference       VARCHAR(100),
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ProcessedAt     TIMESTAMP,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    
    -- Type determines sign of amount
    CONSTRAINT chk_transaction_amount_sign CHECK (
        (TransactionType IN ('deposit', 'credit', 'refund') AND Amount > 0) OR
        (TransactionType IN ('withdrawal', 'debit', 'charge') AND Amount < 0) OR
        (TransactionType = 'adjustment')  -- adjustments can be +/-
    ),
    
    -- Non-USD transactions require exchange rate
    CONSTRAINT chk_exchange_rate_required CHECK (
        Currency = 'USD' OR ExchangeRate IS NOT NULL
    ),
    
    -- Status progression constraints
    CONSTRAINT chk_status_valid CHECK (
        Status IN ('pending', 'processing', 'completed', 'failed', 'reversed')
    ),
    
    -- Completed/failed transactions have processed timestamp
    CONSTRAINT chk_processed_timestamp CHECK (
        Status IN ('pending', 'processing') OR ProcessedAt IS NOT NULL
    ),
    
    -- Processing time cannot precede creation
    CONSTRAINT chk_processing_after_creation CHECK (
        ProcessedAt IS NULL OR ProcessedAt >= CreatedAt
    )
);

Constraint Naming Convention

Triggers for Complex Constraints

When Triggers Are Necessary:

Cross-table constraints: Checking values in other tables
Aggregate constraints: Limits based on counts or sums
Historical constraints: Rules involving previous values
Complex state machines: Valid status transitions
Derived value maintenance: Keeping computed values updated

Trigger Design Principles:

Prefer BEFORE triggers for validation: Reject early, before data is committed
Use AFTER triggers for side effects: Logging, notifications, cascading updates
Keep triggers simple: Complex logic should be in stored procedures
Document thoroughly: Triggers are hidden logic; make them discoverable
Consider performance: Triggers run for every affected row; avoid expensive operations

trigger_constraints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
-- ==================================================
-- TRIGGER-BASED CONSTRAINTS (PostgreSQL)
-- ==================================================
 
-- Example 1: Cross-table constraint
-- Prevent assigning more employees to project than budgeted capacity
 
CREATE TABLE Project (
    ProjectID       SERIAL          PRIMARY KEY,
    ProjectName     VARCHAR(200)    NOT NULL,
    MaxTeamSize     INT             NOT NULL CHECK (MaxTeamSize > 0)
);
 
CREATE TABLE ProjectAssignment (
    AssignmentID    SERIAL          PRIMARY KEY,
    ProjectID       INT             NOT NULL REFERENCES Project(ProjectID),
    EmployeeID      INT             NOT NULL REFERENCES Employee(EmployeeID),
    AssignedDate    DATE            NOT NULL DEFAULT CURRENT_DATE,
    UNIQUE (ProjectID, EmployeeID)
);
 
-- Function to check team size limit
CREATE OR REPLACE FUNCTION check_project_team_size()
RETURNS TRIGGER AS $$
DECLARE
    current_count INT;
    max_size INT;
BEGIN
    -- Get current assignment count and limit
    SELECT COUNT(*), p.MaxTeamSize
    INTO current_count, max_size
    FROM ProjectAssignment pa
    JOIN Project p ON pa.ProjectID = p.ProjectID
    WHERE pa.ProjectID = NEW.ProjectID
    GROUP BY p.MaxTeamSize;
    
    -- Check limit (for INSERT, new row not counted yet)
    IF TG_OP = 'INSERT' AND current_count >= max_size THEN
        RAISE EXCEPTION 'Project % has reached maximum team size of %', 
            NEW.ProjectID, max_size;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_check_project_team_size
BEFORE INSERT ON ProjectAssignment
FOR EACH ROW EXECUTE FUNCTION check_project_team_size();
 
 
-- Example 2: State machine enforcement
-- Only allow valid status transitions
 
CREATE TABLE Order_Status_Machine (
    OrderID         SERIAL          PRIMARY KEY,
    Status          VARCHAR(20)     NOT NULL DEFAULT 'pending'
);
 
CREATE OR REPLACE FUNCTION enforce_order_status_transition()
RETURNS TRIGGER AS $$
BEGIN
    -- Define allowed transitions
    IF OLD.Status = 'pending' AND NEW.Status NOT IN ('confirmed', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: pending -> %', NEW.Status;
    ELSIF OLD.Status = 'confirmed' AND NEW.Status NOT IN ('processing', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: confirmed -> %', NEW.Status;
    ELSIF OLD.Status = 'processing' AND NEW.Status NOT IN ('shipped', 'cancelled') THEN
        RAISE EXCEPTION 'Invalid transition: processing -> %', NEW.Status;
    ELSIF OLD.Status = 'shipped' AND NEW.Status NOT IN ('delivered') THEN
        RAISE EXCEPTION 'Invalid transition: shipped -> %', NEW.Status;
    ELSIF OLD.Status = 'delivered' AND NEW.Status NOT IN ('refunded') THEN
        RAISE EXCEPTION 'Invalid transition: delivered -> %', NEW.Status;
    ELSIF OLD.Status IN ('cancelled', 'refunded') THEN
        RAISE EXCEPTION 'Cannot transition from terminal state: %', OLD.Status;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_order_status_transition
BEFORE UPDATE OF Status ON Order_Status_Machine
FOR EACH ROW EXECUTE FUNCTION enforce_order_status_transition();
 
 
-- Example 3: Aggregate constraint with derived column maintenance
-- Maintain customer's total order value with limit check
 
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,
    CustomerName    VARCHAR(100)    NOT NULL,
    CreditLimit     DECIMAL(12,2)   NOT NULL DEFAULT 10000,
    TotalOrderValue DECIMAL(12,2)   NOT NULL DEFAULT 0  -- Denormalized aggregate
);
 
CREATE TABLE CustomerOrder (
    OrderID         SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL REFERENCES Customer(CustomerID),
    OrderTotal      DECIMAL(12,2)   NOT NULL CHECK (OrderTotal > 0),
    OrderDate       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE OR REPLACE FUNCTION update_customer_order_total()
RETURNS TRIGGER AS $$
DECLARE
    new_total DECIMAL(12,2);
    credit_limit DECIMAL(12,2);
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- Check if new order would exceed credit limit
        SELECT c.TotalOrderValue + NEW.OrderTotal, c.CreditLimit
        INTO new_total, credit_limit
        FROM Customer c
        WHERE c.CustomerID = NEW.CustomerID;
        
        IF new_total > credit_limit THEN
            RAISE EXCEPTION 'Order would exceed credit limit. Current: %, Order: %, Limit: %',
                new_total - NEW.OrderTotal, NEW.OrderTotal, credit_limit;
        END IF;
        
        -- Update aggregate
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue + NEW.OrderTotal
        WHERE CustomerID = NEW.CustomerID;
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- Adjust for difference
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue + (NEW.OrderTotal - OLD.OrderTotal)
        WHERE CustomerID = NEW.CustomerID;
        
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE Customer 
        SET TotalOrderValue = TotalOrderValue - OLD.OrderTotal
        WHERE CustomerID = OLD.CustomerID;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_customer_order_total
AFTER INSERT OR UPDATE OR DELETE ON CustomerOrder
FOR EACH ROW EXECUTE FUNCTION update_customer_order_total();
 
 
-- Example 4: Immutability constraint (prevent updates to certain fields)
CREATE OR REPLACE FUNCTION prevent_field_modification()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.CreatedAt != OLD.CreatedAt THEN
        RAISE EXCEPTION 'CreatedAt is immutable';
    END IF;
    IF NEW.CreatedBy != OLD.CreatedBy THEN
        RAISE EXCEPTION 'CreatedBy is immutable';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger Performance Impact

Constraint Implementation Strategy

Effective constraint implementation requires strategic thinking about where to enforce each business rule. The goal is maximum data integrity with minimal performance impact and maintenance burden.

Decision Framework:

For each business rule, consider:

Can it be expressed declaratively? If yes, use CHECK, UNIQUE, or FOREIGN KEY.
Does it span multiple tables? If yes, consider triggers or application logic.
How expensive is validation? Expensive checks may need batching or eventual consistency.
How critical is guaranteed enforcement? Critical rules need database-level constraints.
Is it presentation or storage logic? Presentation rules (formatting) belong in the application.

Constraint Placement Decision Matrix

•Data type constraints → Database schema: Always. This is fundamental.
•NOT NULL → Database schema: Almost always. Make exceptions only for truly optional data.
•Value ranges/enumerations → CHECK constraints: When values are simple and static.
•Complex enumerations → Lookup tables with FK: When values need metadata or change frequently.
•Uniqueness → UNIQUE constraints: Always enforce at database level, even if also checking in app.
•Referential integrity → FOREIGN KEYs: Almost always. Exceptions for extreme performance cases.
•Cross-table rules → Triggers or application: Choose based on complexity and performance.
•Format validation → Application with database backup: App for UX, database CHECK for safety.

Constraint Enforcement Layer Trade-offs
Layer	Pros	Cons	Best For
Database (Declarative)	Always enforced, optimizer-aware, atomic	Limited expressiveness, DB-specific syntax	Core integrity rules
Database (Triggers)	Always enforced, full SQL power	Hidden logic, performance impact, complexity	Cross-table rules, audit
Application Logic	Full language power, easy testing	Bypassable, must duplicate across apps	Complex business rules
API/Middleware	Centralized, cross-app enforcement	Bypassable via direct DB access	Integration validation
UI/Frontend	Immediate feedback, good UX	Easily bypassed, not authoritative	User guidance only

The Golden Rule

Summary and Key Takeaways

Constraints transform a database from a simple data container into a guardian of business rules. Let's consolidate the essential knowledge:

Constraint Specification Essentials

•Constraints form a hierarchy from attribute-level (type, NOT NULL) through relation-level (PK, UNIQUE) to database-level (FK, triggers).
•Domain constraints (data types, NOT NULL, CHECK) are the first line of defense—make them as restrictive as business rules allow.
•Key constraints (PRIMARY KEY, UNIQUE) ensure tuple uniqueness and enable relationships.
•Foreign keys enforce referential integrity with configurable actions (CASCADE, RESTRICT, SET NULL).
•CHECK constraints encode arbitrary boolean conditions; name them for clear error messages.
•Triggers handle complex rules that declarative constraints cannot express; use sparingly.
•Prefer declarative to procedural: optimizer understands declarative constraints; triggers are hidden logic.
•Defense in depth: validate at UI and application layers for UX, but always enforce at the database.

What Comes Next:

Page Complete

4 / 5