101
0/241
Loading content...
Data types tell the database what kind of data a column can hold—integers, strings, dates. But data types alone are crude instruments. They cannot distinguish between a valid age (0-150) and an absurd one (-5 or 10,000). They cannot enforce that an email follows a specific pattern, that a status belongs to a defined set of values, or that a percentage stays between 0 and 100.
Domain constraints fill this gap. They are the precise rules that transform a generic data type into a meaningful domain—a curated set of values that reflect real-world requirements. Domain constraints are the first line of defense against invalid data, catching errors at the point of entry rather than allowing corrupted data to propagate through systems.
In a well-designed database, domain constraints encode business rules directly in the schema. They make impossible states unrepresentable, following the principle that correct by construction beats validated after the fact.
By the end of this page, you will understand the taxonomy of domain constraints, how to implement constraints in SQL, the distinction between declarative constraints and procedural validation, constraint checking timing and deferral, best practices for constraint design, and tradeoffs between constraint complexity and maintainability.
Domain constraints can be classified into several categories based on their nature and scope. Understanding this taxonomy helps in selecting the appropriate constraint mechanism.
| Constraint Type | Definition | Examples | SQL Mechanism |
|---|---|---|---|
| Type Constraint | Value must be of declared data type | Integer, VARCHAR, DATE | Column type declaration |
| NOT NULL | Value cannot be missing | Required fields | NOT NULL clause |
| Range/Bound | Value within numeric bounds | Age between 0-150 | CHECK (col >= 0 AND col <= 150) |
| Enumeration | Value from a fixed set | Status in ('active', 'inactive') | CHECK (col IN (...)) or ENUM type |
| Pattern/Format | Value matches a format | Email, phone number | CHECK (col ~ 'pattern') |
| Length | String within length bounds | ZIP code exactly 5 chars | CHAR(5) or CHECK (LENGTH(col) = 5) |
| Precision | Numeric precision limits | Price with 2 decimal places | DECIMAL(10,2) |
| Default | Value if none provided | Status defaults to 'pending' | DEFAULT value clause |
| Computed/Derived | Value calculated from others | Full name from parts | Generated columns, triggers |
Domain constraints operate on single attribute values. Constraints that span multiple attributes (e.g., end_date > start_date) or multiple rows (e.g., unique values) are called tuple constraints and relation constraints respectively. This page focuses on single-attribute domain constraints.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
-- Comprehensive constraint demonstration CREATE TABLE employee ( -- TYPE CONSTRAINT: Column data type employee_id SERIAL PRIMARY KEY, -- NOT NULL CONSTRAINT: Required field first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, -- RANGE CONSTRAINT: Numeric bounds age SMALLINT CHECK (age >= 18 AND age <= 100), -- ENUMERATION CONSTRAINT: Fixed value set employment_type VARCHAR(20) NOT NULL CHECK (employment_type IN ('full_time', 'part_time', 'contractor', 'intern')), -- PATTERN CONSTRAINT: Format validation email VARCHAR(255) NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'), -- LENGTH CONSTRAINT: Exact length social_security CHAR(11) CHECK (social_security ~ '^[0-9]{3}-[0-9]{2}-[0-9]{4}$'), -- PRECISION CONSTRAINT: Decimal precision salary DECIMAL(12, 2) NOT NULL CHECK (salary >= 0), -- DEFAULT CONSTRAINT: Auto-fill values hire_date DATE NOT NULL DEFAULT CURRENT_DATE, is_active BOOLEAN NOT NULL DEFAULT TRUE, -- Combined constraint: Multiple conditions phone_number VARCHAR(20) CHECK ( phone_number IS NULL OR phone_number ~ '^\+?[0-9]{10,15}$' )); -- Alternative: Use PostgreSQL ENUM type for enumerationCREATE TYPE employment_status AS ENUM ( 'pending', 'active', 'on_leave', 'terminated', 'retired'); CREATE TABLE employee_v2 ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, status employment_status NOT NULL DEFAULT 'pending');The CHECK constraint is the most flexible domain constraint mechanism in SQL. It allows any boolean expression that evaluates the value being inserted or updated. CHECK constraints can enforce everything from simple ranges to complex pattern matching.
CHECK Constraint Syntax:
-- Inline (column-level)
column_name data_type CHECK (condition)
-- Named constraint (column-level)
column_name data_type CONSTRAINT constraint_name CHECK (condition)
-- Table-level (can reference multiple columns)
CHECK (condition) -- anonymous
CONSTRAINT constraint_name CHECK (condition) -- named
Important Rules:
NULL values pass CHECK constraints because NULL > 0 is UNKNOWN, not FALSE. If you need to reject NULLs, combine CHECK with NOT NULL: age INT NOT NULL CHECK (age >= 0). A CHECK alone does not guarantee non-NULL values.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
-- Range constraintsCREATE TABLE product ( product_id SERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, -- Simple range price DECIMAL(10,2) CHECK (price >= 0), -- Bounded range discount_pct DECIMAL(5,2) CHECK (discount_pct >= 0 AND discount_pct <= 100), -- Minimum value stock_quantity INT CHECK (stock_quantity >= 0)); -- Enumeration constraintsCREATE TABLE order_item ( order_id INT NOT NULL, product_id INT NOT NULL, status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')), size CHAR(2) CHECK (size IN ('XS', 'S', 'M', 'L', 'XL')), PRIMARY KEY (order_id, product_id)); -- Pattern constraints using regular expressionsCREATE TABLE contact ( contact_id SERIAL PRIMARY KEY, -- Email pattern email VARCHAR(255) CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$'), -- US phone: (XXX) XXX-XXXX or XXX-XXX-XXXX phone VARCHAR(20) CHECK (phone ~ '^(\([0-9]{3}\) |[0-9]{3}-)[0-9]{3}-[0-9]{4}$'), -- US ZIP code: 5 digits or 5+4 zip_code VARCHAR(10) CHECK (zip_code ~ '^[0-9]{5}(-[0-9]{4})?$'), -- URL pattern website VARCHAR(500) CHECK (website ~* '^https?://[a-z0-9.-]+\.[a-z]{2,}.*$')); -- Complex CHECK with boolean logicCREATE TABLE employee_schedule ( schedule_id SERIAL PRIMARY KEY, employee_id INT NOT NULL, day_of_week SMALLINT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6), start_time TIME NOT NULL, end_time TIME NOT NULL, -- Table-level CHECK: end must be after start CHECK (end_time > start_time), -- Table-level CHECK: no overnight shifts CHECK (start_time >= '06:00' AND end_time <= '22:00')); -- Named constraints for better error messagesCREATE TABLE financial_account ( account_id SERIAL PRIMARY KEY, account_type VARCHAR(20) NOT NULL CONSTRAINT valid_account_type CHECK (account_type IN ('checking', 'savings', 'investment')), balance DECIMAL(15,2) NOT NULL CONSTRAINT non_negative_balance CHECK (balance >= 0), overdraft_limit DECIMAL(10,2) CONSTRAINT valid_overdraft CHECK (overdraft_limit IS NULL OR overdraft_limit >= 0), CONSTRAINT overdraft_for_checking_only CHECK ( account_type = 'checking' OR overdraft_limit IS NULL )); -- Testing constraint violationsINSERT INTO financial_account (account_type, balance, overdraft_limit)VALUES ('savings', 1000, 500); -- ERROR: violates check constraint "overdraft_for_checking_only"When the same constraints apply to multiple attributes across the schema, CREATE DOMAIN provides a way to define a reusable constrained type. This centralizes constraint logic and ensures consistency.
CREATE DOMAIN Syntax:
CREATE DOMAIN domain_name AS base_type
[ DEFAULT default_value ]
[ CONSTRAINT constraint_name ] CHECK (condition)
[ ... multiple constraints ... ];
Benefits of Domains:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081
-- Define reusable domains -- Monetary amounts (non-negative, 2 decimal places)CREATE DOMAIN monetary_amount AS DECIMAL(15, 2) CONSTRAINT positive_amount CHECK (VALUE >= 0); -- Percentage (0-100)CREATE DOMAIN percentage AS DECIMAL(5, 2) CONSTRAINT valid_percentage CHECK (VALUE >= 0 AND VALUE <= 100); -- Email addressesCREATE DOMAIN email_address AS VARCHAR(255) CONSTRAINT valid_email CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); -- US phone numbersCREATE DOMAIN us_phone AS VARCHAR(20) CONSTRAINT valid_phone CHECK (VALUE ~ '^\+?1?[0-9]{10}$' OR VALUE ~ '^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$'); -- US ZIP codesCREATE DOMAIN us_zip AS VARCHAR(10) CONSTRAINT valid_zip CHECK (VALUE ~ '^[0-9]{5}(-[0-9]{4})?$'); -- ISO country codesCREATE DOMAIN country_code AS CHAR(2) CONSTRAINT valid_country CHECK (VALUE ~ '^[A-Z]{2}$'); -- ISO currency codesCREATE DOMAIN currency_code AS CHAR(3) CONSTRAINT valid_currency CHECK (VALUE ~ '^[A-Z]{3}$'); -- UUID with default generationCREATE DOMAIN entity_uuid AS UUID DEFAULT gen_random_uuid(); -- Rating (1-5 stars)CREATE DOMAIN star_rating AS SMALLINT CONSTRAINT valid_rating CHECK (VALUE >= 1 AND VALUE <= 5); -- URLCREATE DOMAIN url AS VARCHAR(2048) CONSTRAINT valid_url CHECK (VALUE ~* '^https?://'); -- Using domains in tablesCREATE TABLE customer ( customer_id entity_uuid PRIMARY KEY, email email_address NOT NULL UNIQUE, phone us_phone, billing_zip us_zip NOT NULL, preferred_currency currency_code NOT NULL DEFAULT 'USD'); CREATE TABLE product_review ( review_id entity_uuid PRIMARY KEY, product_id INT NOT NULL, customer_id entity_uuid NOT NULL, rating star_rating NOT NULL, review_url url, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE TABLE invoice ( invoice_id entity_uuid PRIMARY KEY, customer_id entity_uuid NOT NULL, subtotal monetary_amount NOT NULL, tax_rate percentage NOT NULL, discount_pct percentage DEFAULT 0, total monetary_amount NOT NULL); -- Modifying a domain (affects all uses)ALTER DOMAIN percentage DROP CONSTRAINT valid_percentage; ALTER DOMAIN percentage ADD CONSTRAINT valid_percentage CHECK (VALUE >= 0 AND VALUE <= 200); -- Now allows up to 200% -- Drop a domain (must not be in use)-- DROP DOMAIN IF EXISTS some_domain;Create a 'domain library' script that defines all common domains for your organization. Run this before creating application tables. Include: monetary amounts, percentages, email addresses, phone numbers, ZIP codes, ratings, confidence scores, etc. This promotes reuse and consistency across all database projects.
Enumerations restrict an attribute to a fixed, finite set of values. There are multiple implementation approaches, each with tradeoffs.
| Approach | Syntax | Pros | Cons |
|---|---|---|---|
| CHECK constraint | CHECK (col IN ('a', 'b', 'c')) | Simple, portable | No ordering, values in code |
| ENUM type | CREATE TYPE t AS ENUM (...) | Native type, ordered | Hard to modify, not portable |
| Lookup table + FK | Separate reference table | Flexible, metadata support | Extra table, join needed |
| Domain | CREATE DOMAIN d CHECK (...) | Reusable, centralized | Same as CHECK limitations |
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586
-- APPROACH 1: CHECK constraintCREATE TABLE order_v1 ( order_id SERIAL PRIMARY KEY, status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))); -- Simple but values are scattered in code -- APPROACH 2: PostgreSQL ENUM typeCREATE TYPE order_status AS ENUM ( 'pending', -- Values are ordered by definition order 'confirmed', 'shipped', 'delivered', 'cancelled'); CREATE TABLE order_v2 ( order_id SERIAL PRIMARY KEY, status order_status NOT NULL DEFAULT 'pending'); -- Enum ordering allows comparisonSELECT * FROM order_v2 WHERE status >= 'shipped'; -- Adding values (only at end or with position)ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered'; -- WARNING: Removing enum values is very difficult!-- Requires recreating the type -- APPROACH 3: Lookup table with foreign key (most flexible)CREATE TABLE ref_order_status ( status_code VARCHAR(20) PRIMARY KEY, status_name VARCHAR(50) NOT NULL, display_order INT NOT NULL, is_terminal BOOLEAN NOT NULL DEFAULT FALSE, description TEXT); INSERT INTO ref_order_status VALUES ('pending', 'Pending', 1, FALSE, 'Order received, awaiting confirmation'), ('confirmed', 'Confirmed', 2, FALSE, 'Order confirmed by seller'), ('shipped', 'Shipped', 3, FALSE, 'Order dispatched for delivery'), ('delivered', 'Delivered', 4, TRUE, 'Order delivered to customer'), ('cancelled', 'Cancelled', 5, TRUE, 'Order cancelled'), ('returned', 'Returned', 6, TRUE, 'Order returned by customer'); CREATE TABLE order_v3 ( order_id SERIAL PRIMARY KEY, status_code VARCHAR(20) NOT NULL REFERENCES ref_order_status(status_code) DEFAULT 'pending'); -- Query with metadataSELECT o.order_id, s.status_name, s.is_terminalFROM order_v3 oJOIN ref_order_status s ON o.status_code = s.status_codeORDER BY s.display_order; -- Easy to add, modify, or (soft) delete valuesUPDATE ref_order_status SET status_name = 'In Transit' WHERE status_code = 'shipped'; -- APPROACH 4: Domain with CHECKCREATE DOMAIN order_status_domain AS VARCHAR(20) CHECK (VALUE IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')); CREATE TABLE order_v4 ( order_id SERIAL PRIMARY KEY, status order_status_domain NOT NULL); -- RECOMMENDATION: Use lookup table for:-- - Statuses that need metadata (descriptions, display names)-- - Values that may change over application lifetime-- - Values that non-developers need to manage -- Use ENUM or CHECK for:-- - Truly fixed categories (days of week, card suits)-- - Performance-critical columns (ENUMs store as integers)-- - Simple cases where lookup overhead isn't justifiedPostgreSQL ENUMs are problematic for evolving systems. Adding values is possible (with ordering caveats), but removing or renaming values requires recreating the type and all dependent columns. For values that might change, prefer lookup tables with foreign keys.
Constraints are typically checked immediately when a statement executes. However, some scenarios require deferred constraint checking—postponing validation until the end of a transaction.
Constraint Timing Options:
| Mode | When Checked | Use Case |
|---|---|---|
NOT DEFERRABLE | After each statement (default) | Normal operations |
DEFERRABLE INITIALLY IMMEDIATE | After each statement, but can defer | Flexibility with control |
DEFERRABLE INITIALLY DEFERRED | At transaction commit | Circular dependencies, bulk loads |
Why Defer Constraints?
Circular Foreign Keys: Table A references B, B references A. Cannot insert first row of either without deferral.
Multi-row Updates: Updating a unique column across multiple rows might temporarily violate uniqueness mid-transaction.
Bulk Loading: Import scripts may insert data in an order that temporarily violates integrity.
Complex Business Rules: Some rules are only satisfiable after multiple related operations complete.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879
-- Problem: Circular reference between department and employee -- Department has a manager (who is an employee)-- Employee belongs to a department-- Chicken-and-egg: can't create first of either! -- Solution: Deferrable constraintsCREATE TABLE department ( department_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT -- Will reference employee); CREATE TABLE employee ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, department_id INT NOT NULL REFERENCES department(department_id) DEFERRABLE INITIALLY DEFERRED -- Check at commit); ALTER TABLE department ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employee(employee_id) DEFERRABLE INITIALLY DEFERRED; -- Check at commit -- Now we can insert circular references in a single transactionBEGIN; -- Insert department without manager firstINSERT INTO department (department_id, name, manager_id) VALUES (1, 'Engineering', NULL); -- Insert employee referencing departmentINSERT INTO employee (employee_id, name, department_id) VALUES (1, 'Alice', 1); -- Update department with managerUPDATE department SET manager_id = 1 WHERE department_id = 1; COMMIT; -- Constraints checked here - both are satisfied! -- Alternative: Change constraint mode within transactionCREATE TABLE inventory ( product_id INT PRIMARY KEY, sku VARCHAR(20) NOT NULL UNIQUE DEFERRABLE INITIALLY IMMEDIATE, quantity INT NOT NULL); -- Swapping SKUs between two productsBEGIN; -- This would fail immediately without deferralSET CONSTRAINTS ALL DEFERRED; UPDATE inventory SET sku = 'TEMP-SKU' WHERE product_id = 1;UPDATE inventory SET sku = (SELECT sku FROM inventory WHERE product_id = 1) WHERE product_id = 2;UPDATE inventory SET sku = 'NEW-SKU' WHERE product_id = 1; SET CONSTRAINTS ALL IMMEDIATE; -- Check now COMMIT; -- Check constraint deferral (PostgreSQL 9.4+)CREATE TABLE scheduled_event ( event_id SERIAL PRIMARY KEY, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL, CONSTRAINT valid_time_range CHECK (end_time > start_time) NOT DEFERRABLE -- CHECK constraints cannot be deferred in PostgreSQL!); -- Note: CHECK constraints are NOT deferrable in standard PostgreSQL-- Only UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUDE constraints support deferralIn PostgreSQL (and most databases), CHECK constraints are always checked immediately—they cannot be deferred. Only UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUDE constraints support deferral. For complex multi-row CHECK-like logic, use triggers instead.
A perennial debate in software architecture: should validation happen in the database (constraints) or the application (code)? The answer is: both, for different purposes.
The Defense-in-Depth Strategy:
Application Layer: Validate early for user experience. Provide helpful messages. Enforce complex business rules. Call external validation services.
Database Layer: Enforce invariants that must never be violated. Catch bugs in application code. Protect against direct database access. Document schema requirements.
What Belongs Where:
| Validation Type | Application | Database |
|---|---|---|
| Type constraints (integer, date) | ✓ (parse input) | ✓ (column type) |
| Required fields | ✓ (form validation) | ✓ (NOT NULL) |
| Range constraints | ✓ (immediate feedback) | ✓ (CHECK) |
| Pattern matching | ✓ (helpful errors) | ✓ (CHECK with regex) |
| Uniqueness | ✓ (check before submit) | ✓ (UNIQUE constraint) |
| Referential integrity | ✓ (valid FK dropdowns) | ✓ (FOREIGN KEY) |
| External validation (email delivery) | ✓ | ✗ |
| Complex business rules | ✓ | Some (triggers) |
| Rate limiting, quotas | ✓ | ✗ |
Assume the application will have bugs. Assume someone will connect directly to the database. Assume an import script will skip validation. Database constraints are your last defense—use them liberally for any invariant that, if violated, would corrupt your data model.
Effective constraint design balances correctness, maintainability, and performance. Follow these battle-tested practices:
CONSTRAINT positive_price CHECK (price >= 0) produces better error messages than anonymous constraints.1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677
-- BEST PRACTICE: Named, documented constraints CREATE TABLE product ( product_id SERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, -- Named constraint with clear purpose sku VARCHAR(20) NOT NULL CONSTRAINT product_sku_unique UNIQUE, -- Explained constraint price DECIMAL(10,2) NOT NULL CONSTRAINT product_price_positive CHECK (price >= 0), -- Comment: Prices can be zero for promotional items -- Multiple simple constraints instead of one complex discount_pct DECIMAL(5,2) CONSTRAINT discount_not_negative CHECK (discount_pct >= 0) CONSTRAINT discount_max_100 CHECK (discount_pct <= 100), -- Domain usage for repeated pattern category_id INT NOT NULL, -- Foreign key with explicit name CONSTRAINT product_category_fk FOREIGN KEY (category_id) REFERENCES category(category_id)); -- Add comments for documentationCOMMENT ON CONSTRAINT product_price_positive ON product IS 'Prices must be non-negative. Zero allowed for promotional/sample products.'; COMMENT ON CONSTRAINT discount_max_100 ON product IS 'Discount cannot exceed 100%. For greater discounts, adjust base price.'; -- BEST PRACTICE: Test constraints explicitly -- Create test helper function (for testing environments)CREATE OR REPLACE FUNCTION assert_constraint_error( p_sql TEXT, p_constraint_name TEXT) RETURNS BOOLEAN AS $$BEGIN EXECUTE p_sql; RAISE EXCEPTION 'Expected constraint violation did not occur';EXCEPTION WHEN check_violation OR unique_violation THEN IF SQLERRM LIKE '%' || p_constraint_name || '%' THEN RETURN TRUE; END IF; RAISE EXCEPTION 'Wrong constraint violated: %', SQLERRM;END;$$ LANGUAGE plpgsql; -- Test: Negative price should be rejectedSELECT assert_constraint_error( 'INSERT INTO product (name, sku, price, category_id) VALUES (''Test'', ''TST-001'', -10.00, 1)', 'product_price_positive'); -- Test: Discount over 100 should be rejectedSELECT assert_constraint_error( 'INSERT INTO product (name, sku, price, discount_pct, category_id) VALUES (''Test'', ''TST-002'', 100.00, 150.00, 1)', 'discount_max_100'); -- BEST PRACTICE: Schema version control migration example-- migrations/20240115_add_product_constraints.sql -- Up migrationALTER TABLE product ADD CONSTRAINT product_name_not_empty CHECK (LENGTH(TRIM(name)) > 0); -- Down migration-- ALTER TABLE product DROP CONSTRAINT product_name_not_empty;Domain constraints transform generic data types into meaningful, validated domains that enforce business rules at the data layer. Let's consolidate the key concepts:
Module Complete:
With domain constraints understood, you've completed the Attributes and Domains module. You now have comprehensive knowledge of attributes (the named roles values play), domains (the sets of valid values), atomic values (the 1NF requirement), NULL (the absence marker), and domain constraints (the rules enforcing validity).
This knowledge forms the foundation for understanding tuples, keys, and more complex integrity constraints in subsequent modules.
You now have deep mastery of attributes and domains—from formal definitions to practical implementation. You understand how to define meaningful attributes, constrain them to valid domains, handle NULL appropriately, and enforce business rules through constraints. This foundation is essential for designing robust, maintainable relational databases.