Loading learning content...
Before data can participate in complex relationships or business logic, it must satisfy a more fundamental requirement: it must be valid. An age cannot be negative. A percentage cannot exceed 100. An email address must contain an @ symbol. A status code must be one of a predefined set of values.
Domain constraints are the guardians of this fundamental validity. In the relational model, a domain is a set of atomic values that an attribute can legally take. Domain constraints ensure that every value stored in a column belongs to its defined domain—rejecting invalid data at the point of entry rather than allowing corruption to spread through the system.
This page provides an exhaustive exploration of domain constraints, from their theoretical foundations in relational algebra to their practical implementation as data types, CHECK constraints, and enumerated types across modern database systems.
By the end of this page, you will understand: (1) The formal definition of domains in relational theory, (2) How data types implement basic domain constraints, (3) CHECK constraints for complex value validation, (4) Enumerated types and their use cases, (5) User-defined types and domain abstraction, and (6) The relationship between domain constraints and application-level validation.
In Codd's original relational model, a domain is defined as:
Domain: A named set of atomic (indivisible) values of the same type. Each attribute in a relation is defined over exactly one domain, and every value of that attribute must be a member of its domain.
This definition encodes several important concepts:
The type system connection:
In practical database systems, domains are primarily implemented through the type system. When you declare a column as INT or VARCHAR(100), you are specifying its domain. The database will reject any attempt to store a value that doesn't belong to that domain—for example, storing "hello" in an INT column.
However, built-in types provide only a coarse-grained domain definition. The INT type encompasses all integers from approximately -2 billion to +2 billion, but a 'human age' domain should really only include values from 0 to perhaps 150. This is where CHECK constraints and user-defined types extend the domain concept to match real-world semantics.
SQL data types define syntactic domains—the raw storage format. But real-world data has semantic domains—ages are positive integers typically under 150, email addresses follow a specific pattern, currencies have exactly two decimal places. Effective domain constraints bridge this gap, encoding semantic rules into database enforcement.
The most fundamental domain constraint is the column's data type. The database enforces type compatibility at every operation—insertion, update, and query. Understanding the available types and their precise boundaries is essential for effective domain modeling.
| Category | Type | Domain Description | Boundary Considerations |
|---|---|---|---|
| Integer | SMALLINT | Integers from -32,768 to 32,767 | Suitable for ages, ratings, small counts |
| Integer | INT/INTEGER | Integers from -2.1B to 2.1B | General-purpose integer; most common choice |
| Integer | BIGINT | Integers from -9.2×10¹⁸ to 9.2×10¹⁸ | Large identifiers, timestamps as integers |
| Decimal | DECIMAL(p,s) | Fixed-point with p total digits, s after decimal | Financial data; exact arithmetic |
| Floating | FLOAT/DOUBLE | Approximate floating-point numbers | Scientific data; beware of precision loss |
| String | VARCHAR(n) | Variable-length strings up to n characters | Most text data; n limits storage |
| String | CHAR(n) | Fixed-length strings of exactly n characters | Codes, identifiers with fixed format |
| String | TEXT | Unlimited length strings | Large text fields; may have index limitations |
| Date/Time | DATE | Calendar dates (year, month, day) | Birthdates, due dates |
| Date/Time | TIME | Time of day (hour, minute, second) | Schedules, opening hours |
| Date/Time | TIMESTAMP | Date plus time, often with timezone | Event timestamps, audit trails |
| Boolean | BOOLEAN | TRUE, FALSE, (and NULL if allowed) | Flags, toggles, yes/no fields |
| Binary | BLOB/BYTEA | Binary data of arbitrary length | Files, images, encrypted data |
| UUID | UUID | 128-bit universally unique identifiers | Distributed primary keys |
123456789101112131415161718192021222324252627282930313233343536373839
-- Type enforcement examplesCREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, quantity_in_stock INT NOT NULL, weight_kg FLOAT, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Valid insertion: all types matchINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES (1, 'Widget', 19.99, 100); -- Type violation: 'hello' is not an INTINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES ('hello', 'Gadget', 29.99, 50);-- ERROR: invalid input syntax for type integer: "hello" -- Type violation: 'expensive' is not a DECIMALINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES (2, 'Gizmo', 'expensive', 25);-- ERROR: invalid input syntax for type numeric: "expensive" -- Implicit conversion works in some casesINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES (3, 'Thingamajig', '39.99', 75); -- String '39.99' converted to DECIMAL-- OK: implicit conversion from string to numeric -- VARCHAR length enforcementINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES (4, 'This product name is way too long and exceeds the one hundred character limit that we specified for the product_name column', 49.99, 10);-- ERROR: value too long for type character varying(100) -- DECIMAL precision enforcementINSERT INTO products (product_id, product_name, price, quantity_in_stock)VALUES (5, 'Cheap Item', 9.999, 200); -- Three decimal places-- Behavior varies: rounds to 10.00 or errors depending on DBWhile databases often perform implicit type conversion, relying on it is dangerous. String '123' converts to INT 123, but '12a3' causes an error. Decimal 123.99 truncates to INT 123 (or rounds to 124 depending on the database). Always use explicit types in your application code and understand your database's conversion rules.
Data types define broad categories, but business rules often require narrower domains. A 'percentage' must be between 0 and 100. A 'future date' must be greater than the current date. An 'even number' must be divisible by 2.
CHECK constraints allow you to define arbitrary boolean conditions that every value must satisfy. They are the primary mechanism for encoding semantic domain rules in SQL.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677
-- Basic CHECK constraintsCREATE TABLE employees ( emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(100) NOT NULL, -- Age must be reasonable for employment age INT NOT NULL CHECK (age >= 18 AND age <= 100), -- Salary must be positive salary DECIMAL(12,2) CHECK (salary > 0), -- Email must contain @ email VARCHAR(255) CHECK (email LIKE '%@%.%'), -- Status must be one of specific values status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'terminated')), -- Hire date cannot be in the future hire_date DATE NOT NULL CHECK (hire_date <= CURRENT_DATE), -- Termination date, if set, must be after hire date termination_date DATE CHECK (termination_date IS NULL OR termination_date > hire_date)); -- Named constraints for better error messagesCREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL, shipping_date DATE, total_amount DECIMAL(12,2) NOT NULL, discount_percent DECIMAL(5,2), -- Named constraints provide clearer error messages CONSTRAINT chk_positive_total CHECK (total_amount >= 0), CONSTRAINT chk_valid_discount CHECK (discount_percent IS NULL OR (discount_percent >= 0 AND discount_percent <= 100)), CONSTRAINT chk_shipping_after_order CHECK (shipping_date IS NULL OR shipping_date >= order_date)); -- Violation examplesINSERT INTO employees (emp_name, age, salary, email, status, hire_date)VALUES ('John Doe', 15, 50000, 'john@example.com', 'active', '2024-01-01');-- ERROR: new row violates check constraint "employees_age_check" INSERT INTO orders (customer_id, order_date, total_amount, discount_percent)VALUES (1, '2024-01-15', 500.00, 150);-- ERROR: new row violates check constraint "chk_valid_discount" -- Multi-column CHECK constraintsCREATE TABLE date_ranges ( range_id SERIAL PRIMARY KEY, range_name VARCHAR(50) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, -- End must be after start CONSTRAINT chk_valid_range CHECK (end_date >= start_date)); -- Complex boolean logicCREATE TABLE transactions ( txn_id SERIAL PRIMARY KEY, txn_type VARCHAR(10) NOT NULL, amount DECIMAL(12,2) NOT NULL, -- Credits must be positive, debits must be negative CONSTRAINT chk_amount_sign CHECK ( (txn_type = 'credit' AND amount > 0) OR (txn_type = 'debit' AND amount < 0) OR (txn_type = 'reversal') -- Reversals can be either ));CONSTRAINT chk_positive_age CHECK (age > 0) produces clearer error messages than anonymous constraints.IS NOT NULL if NULL should fail.In SQL, CHECK constraints pass when the expression evaluates to TRUE or UNKNOWN (NULL). This means CHECK (value > 0) allows NULL values! If you want to require a value, combine with NOT NULL: value INT NOT NULL CHECK (value > 0). This is a common source of bugs.
Many real-world attributes have a small, fixed set of valid values: order statuses, days of the week, blood types, card suits. While CHECK constraints with IN clauses can enforce these, enumerated types (ENUMs) provide a more elegant solution in databases that support them.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
-- PostgreSQL: Native ENUM type support -- Create an enum typeCREATE TYPE order_status AS ENUM ( 'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled'); -- Use the enum in a tableCREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, status order_status NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -- Valid insertionINSERT INTO orders (customer_id, status)VALUES (1, 'confirmed'); -- Invalid insertion: 'approved' is not in the enumINSERT INTO orders (customer_id, status)VALUES (2, 'approved');-- ERROR: invalid input value for enum order_status: "approved" -- Comparing enum values (ordered by definition order)SELECT * FROM orders WHERE status > 'confirmed'; -- processing, shipped, delivered, cancelled -- Adding a new value to an enum (PostgreSQL 9.1+)ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered'; -- View all values in an enumSELECT enumlabel FROM pg_enum WHERE enumtypid = 'order_status'::regtypeORDER BY enumsortorder; -- Creating enum with explicit labels for displayCREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'critical'); CREATE TABLE tickets ( ticket_id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, priority priority_level NOT NULL DEFAULT 'medium');| Approach | Pros | Cons | Best For |
|---|---|---|---|
| ENUM Type | Type-safe; storage efficient; built-in ordering | Adding values needs DDL; not portable | Fixed, rarely-changing value sets |
| CHECK Constraint | Portable; simple; no schema objects | No metadata; repeated in each table | Small value sets; maximum compatibility |
| Reference Table | Full flexibility; metadata support; no DDL to add values | Extra joins; more complex queries | Values needing metadata; frequently changing sets |
If the valid values are truly fixed (days of week, card suits), use ENUMs or CHECK constraints. If values may change or need associated metadata (product categories, user roles), use a reference table with a foreign key. The reference table approach trades query simplicity for flexibility.
Some database systems support User-Defined Types (UDTs) or CREATE DOMAIN statements that allow you to create reusable domain definitions. These provide a layer of abstraction above base types, encapsulating constraints and potentially custom operations.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
-- PostgreSQL: CREATE DOMAIN for reusable constrained types -- Domain for email addressesCREATE DOMAIN email_address AS VARCHAR(255) CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); -- Domain for positive monetary amountsCREATE DOMAIN money_positive AS DECIMAL(12,2) CHECK (VALUE > 0); -- Domain for percentage valuesCREATE DOMAIN percentage AS DECIMAL(5,2) CHECK (VALUE >= 0 AND VALUE <= 100); -- Domain for US phone numbersCREATE DOMAIN us_phone AS CHAR(12) CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$'); -- Domain for future datesCREATE DOMAIN future_date AS DATE CHECK (VALUE > CURRENT_DATE); -- Use domains in table definitionsCREATE TABLE employees ( emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(100) NOT NULL, work_email email_address NOT NULL UNIQUE, personal_email email_address, phone us_phone, salary money_positive NOT NULL, bonus_percent percentage); CREATE TABLE contracts ( contract_id SERIAL PRIMARY KEY, client_email email_address NOT NULL, start_date DATE NOT NULL, end_date future_date NOT NULL, value money_positive NOT NULL, completion_percent percentage DEFAULT 0); -- Domain violations produce clear errorsINSERT INTO employees (emp_name, work_email, salary)VALUES ('Jane Doe', 'invalid-email', 50000);-- ERROR: value for domain email_address violates check constraint INSERT INTO employees (emp_name, work_email, salary)VALUES ('Jane Doe', 'jane@example.com', -50000);-- ERROR: value for domain money_positive violates check constraint -- Modifying a domain affects all columns using itALTER DOMAIN email_address DROP CONSTRAINT email_address_check;ALTER DOMAIN email_address ADD CONSTRAINT email_address_check CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,63}$'); -- View domain definitionsSELECT domain_name, data_type, character_maximum_length, domain_defaultFROM information_schema.domainsWHERE domain_schema = 'public';email_address is more meaningful than VARCHAR(255). The schema documents its own semantics.CREATE DOMAIN is a PostgreSQL feature not available in MySQL or SQL Server in this exact form. SQL Server has similar capabilities with user-defined types and rules, but the syntax differs. For maximum portability, CHECK constraints on individual columns remain the safest approach, though less elegant.
The NOT NULL constraint is a special domain constraint that excludes the NULL value from the column's domain. While often discussed separately, it is fundamentally a domain restriction: the domain of possible values no longer includes 'unknown' or 'absent.'
The semantics of NOT NULL:
When a column is declared NOT NULL, you are asserting that:
This is a strong semantic statement that should align with business requirements.
| Scenario | NOT NULL? | Rationale |
|---|---|---|
| Primary key columns | Always | Entity integrity requires identifiability |
| Required business fields | Yes | Order must have customer, product must have name |
| Foreign keys (mandatory relationship) | Yes | Employee must belong to department |
| Foreign keys (optional relationship) | No | Employee may or may not have manager |
| Columns with defaults | Usually | Default provides value, NULL unnecessary |
| Optional attributes | No | Middle name, secondary phone |
| Calculated/derived values | Depends | May be NULL until calculated |
| Audit columns (created_at, updated_at) | Yes | Every record has creation/update time |
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
-- NOT NULL with defaultsCREATE TABLE audit_log ( log_id SERIAL PRIMARY KEY, event_type VARCHAR(50) NOT NULL, event_data JSONB NOT NULL DEFAULT '{}', -- Empty object, not NULL created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by INT NOT NULL, -- Optional relation related_table VARCHAR(50), -- May be NULL if not applicable related_id INT -- May be NULL); -- NOT NULL enforces mandatory fieldsINSERT INTO audit_log (event_type, created_by)VALUES ('login', 1001); -- created_at and event_data use defaults -- NULL rejectionINSERT INTO audit_log (event_type, created_by)VALUES (NULL, 1001);-- ERROR: null value in column "event_type" violates not-null constraint -- Combining NOT NULL with CHECKCREATE TABLE products ( product_id SERIAL PRIMARY KEY, -- Name is required AND must have content product_name VARCHAR(100) NOT NULL CHECK (LENGTH(TRIM(product_name)) > 0), -- Price is required AND must be positive price DECIMAL(10,2) NOT NULL CHECK (price > 0), -- Description is optional but if provided, must have content description TEXT CHECK (description IS NULL OR LENGTH(TRIM(description)) > 10)); -- Empty string passes NOT NULL but might be invalidINSERT INTO products (product_name, price)VALUES ('', 19.99);-- ERROR: violates check constraint (LENGTH(TRIM(...)) > 0) -- Adding NOT NULL to existing column requires backfill-- Step 1: Set default for existing NULLsUPDATE products SET description = 'No description available' WHERE description IS NULL; -- Step 2: Add constraintALTER TABLE products ALTER COLUMN description SET NOT NULL;Many experienced database designers advocate for 'NOT NULL by default'—make every column NOT NULL unless you have a specific reason to allow NULL. This forces explicit decisions about optionality and prevents the accumulation of NULLs that complicate queries. Some teams even configure their schema tools to warn on nullable columns without explicit justification.
A common architectural question: should domain validation occur in the application or the database? The answer is unequivocally both, but with different purposes:
The defense-in-depth principle:
Application validation is about user experience. Database validation is about data integrity. Application validation can be bypassed (bugs, direct database access, migrations). Database validation cannot—it is the ultimate enforcer.
Think of it like building security. Application validation is the receptionist checking IDs at the front desk—efficient and friendly. Database validation is the lock on the vault door—absolute and unbypassable.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
// Application layer: User-friendly validation with detailed messagesfunction validateProduct(product) { const errors = []; if (!product.name || product.name.trim().length === 0) { errors.push("Product name is required"); } else if (product.name.length > 100) { errors.push("Product name must be 100 characters or less"); } if (product.price === undefined || product.price === null) { errors.push("Price is required"); } else if (typeof product.price !== 'number' || isNaN(product.price)) { errors.push("Price must be a valid number"); } else if (product.price <= 0) { errors.push("Price must be greater than zero"); } else if (product.price > 999999.99) { errors.push("Price exceeds maximum allowed value"); } if (product.discountPercent !== null && product.discountPercent !== undefined) { if (product.discountPercent < 0 || product.discountPercent > 100) { errors.push("Discount must be between 0% and 100%"); } } return errors; // Empty array = valid} // Usage in API endpointasync function createProduct(req, res) { const errors = validateProduct(req.body); if (errors.length > 0) { return res.status(400).json({ errors }); } try { // Database layer provides second line of defense await db.query(` INSERT INTO products (name, price, discount_percent) VALUES ($1, $2, $3) `, [req.body.name, req.body.price, req.body.discountPercent]); return res.status(201).json({ success: true }); } catch (dbError) { // Database constraint violation = application bug // Log for developers, return generic error to user logger.error('Constraint violation despite app validation', dbError); return res.status(500).json({ error: "Internal server error" }); }}If database constraints catch a violation that application validation should have caught, treat it as a bug—the constraint is working correctly, but your application has a gap. Investigate why the invalid data reached the database and fix the application validation. The database constraint is not a substitute for good application code; it's a backstop that should rarely trigger in production.
Domain constraints are the foundation of data quality. Let's consolidate the essential knowledge:
What's next:
Domain constraints govern individual values. The next page explores Key Constraints—the rules that govern uniqueness and identification within tables, preventing duplicate entities and ensuring data can be reliably referenced.
You now understand how domain constraints—from basic data types through CHECK constraints to user-defined domains—work together to ensure data validity. Every invalid value rejected at insertion is a bug prevented, a report made accurate, and a downstream failure avoided.