Domain Constraints — Integrity Constraints in… | OneNoughtOne

0/86

Database Management SystemsIntegrity Constraints

Integrity Constraints in the Relational Model

LevelIntermediate

Duration90 mins

TopicIntegrity Constraints

3 / 5

Domain Constraints

The Foundation of Data Validity

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.

What You Will Master

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.

Formal Definition of Domains

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:

Components of a Domain

•Named — Domains have identities. 'Age' is a different domain from 'Quantity' even if both consist of integers. The name carries semantic meaning.
•Set of values — A domain is mathematically a set, with all the properties of sets: membership, distinctness, and potentially infinite cardinality.
•Atomic values — Each value in the domain cannot be decomposed further within the relational model. This is the First Normal Form (1NF) requirement.
•Same type — All values in a domain share a common structure and comparison semantics. You can meaningfully compare two ages or two quantities, but comparing an age to a product name is meaningless.

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.

Semantic vs Syntactic Domains

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.

Data Types as Basic Domain Constraints

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.

Common SQL Data Types and Their Domains
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

data_type_enforcement.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
-- Type enforcement examples
CREATE 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 match
INSERT INTO products (product_id, product_name, price, quantity_in_stock)
VALUES (1, 'Widget', 19.99, 100);
 
-- Type violation: 'hello' is not an INT
INSERT 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 DECIMAL
INSERT 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 cases
INSERT 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 enforcement
INSERT 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 enforcement
INSERT 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 DB

Implicit Conversion Pitfalls

While 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.

CHECK Constraints: Refining Domains

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.

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
-- Basic CHECK constraints
CREATE 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 messages
CREATE 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 examples
INSERT 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 constraints
CREATE 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 logic
CREATE 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
    )
);

CHECK Constraint Best Practices

•Name your constraints — CONSTRAINT chk_positive_age CHECK (age > 0) produces clearer error messages than anonymous constraints.
•Keep constraints simple — Complex logic is hard to debug and may indicate a design problem. Consider triggers for complex validation.
•Document the business rule — Add comments explaining why the constraint exists, not just what it checks.
•Test boundary values — Verify behavior at exactly the constraint boundaries (age = 18, discount = 100, etc.).
•Handle NULL explicitly — CHECK constraints return TRUE for NULL by default. Use IS NOT NULL if NULL should fail.

NULL and CHECK Constraints

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.

Enumerated Types: Finite Domain Definition

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.

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
-- PostgreSQL: Native ENUM type support
 
-- Create an enum type
CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);
 
-- Use the enum in a table
CREATE 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 insertion
INSERT INTO orders (customer_id, status)
VALUES (1, 'confirmed');
 
-- Invalid insertion: 'approved' is not in the enum
INSERT 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 enum
SELECT enumlabel FROM pg_enum 
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;
 
-- Creating enum with explicit labels for display
CREATE 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'
);

ENUM vs CHECK vs Reference Table
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

Choose Based on Volatility

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.

User-Defined Types (UDTs)

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.

user_defined_domains.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
-- PostgreSQL: CREATE DOMAIN for reusable constrained types
 
-- Domain for email addresses
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
-- Domain for positive monetary amounts
CREATE DOMAIN money_positive AS DECIMAL(12,2)
    CHECK (VALUE > 0);
 
-- Domain for percentage values
CREATE DOMAIN percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Domain for US phone numbers
CREATE DOMAIN us_phone AS CHAR(12)
    CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$');
 
-- Domain for future dates
CREATE DOMAIN future_date AS DATE
    CHECK (VALUE > CURRENT_DATE);
 
-- Use domains in table definitions
CREATE 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 errors
INSERT 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 it
ALTER 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 definitions
SELECT 
    domain_name,
    data_type,
    character_maximum_length,
    domain_default
FROM information_schema.domains
WHERE domain_schema = 'public';

Benefits of User-Defined Domains

•Consistency — The same validation is applied everywhere the domain is used. Change it in one place, and all columns update.
•Semantic naming — email_address is more meaningful than VARCHAR(255). The schema documents its own semantics.
•Reduced errors — Developers can't accidentally omit constraints when creating new tables.
•Centralized maintenance — Pattern updates (e.g., supporting longer TLDs) require only one ALTER DOMAIN.
•Type safety — Some advanced uses allow custom operators and functions on the domain.

Limited Portability

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.

NOT NULL as a Domain Constraint

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: Every row must have a known, definite value for this attribute The attribute is mandatory for the entity to be meaningful Applications cannot defer providing this value This is a strong semantic statement that should align with business requirements.

When to Use NOT NULL
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

not_null_patterns.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
-- NOT NULL with defaults
CREATE 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 fields
INSERT INTO audit_log (event_type, created_by)
VALUES ('login', 1001);  -- created_at and event_data use defaults
 
-- NULL rejection
INSERT 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 CHECK
CREATE 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 invalid
INSERT 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 NULLs
UPDATE products SET description = 'No description available' 
WHERE description IS NULL;
 
-- Step 2: Add constraint
ALTER TABLE products ALTER COLUMN description SET NOT NULL;

NOT NULL by Default Philosophy

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.

Application vs Database Validation

A common architectural question: should domain validation occur in the application or the database? The answer is unequivocally both, but with different purposes:

Application-Level Validation

•User experience — Provide immediate, friendly error messages before database round-trip
•Rich validation — Complex business logic, cross-system checks, async validations
•Performance — Catch obvious errors early without database load
•Context-aware — Different rules for different user roles or contexts
•Format flexibility — Accept multiple input formats, normalize for storage

Database-Level Validation

•Last line of defense — Catches bugs in application code, bypasses, direct SQL access
•Data integrity — Guarantee that invalid data CANNOT exist, regardless of source
•Multi-application — Same rules enforced for all applications accessing the database
•Audit compliance — Provable enforcement for regulatory requirements
•Migration safety — Protects data during ETL, migrations, bulk operations

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.

validation_layers.js
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
// Application layer: User-friendly validation with detailed messages
function 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 endpoint
async 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" });
    }
}

Never Trust Application Validation Alone

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.

Database Management SystemsIntegrity Constraints

Integrity Constraints in the Relational Model

LevelIntermediate

Duration90 mins

TopicIntegrity Constraints

3 / 5

Domain Constraints

The Foundation of Data Validity

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.

What You Will Master

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.

Formal Definition of Domains

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:

Components of a Domain

•Named — Domains have identities. 'Age' is a different domain from 'Quantity' even if both consist of integers. The name carries semantic meaning.
•Set of values — A domain is mathematically a set, with all the properties of sets: membership, distinctness, and potentially infinite cardinality.
•Atomic values — Each value in the domain cannot be decomposed further within the relational model. This is the First Normal Form (1NF) requirement.
•Same type — All values in a domain share a common structure and comparison semantics. You can meaningfully compare two ages or two quantities, but comparing an age to a product name is meaningless.

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.

Semantic vs Syntactic Domains

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.

Data Types as Basic Domain Constraints

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.

Common SQL Data Types and Their Domains
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

data_type_enforcement.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
-- Type enforcement examples
CREATE 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 match
INSERT INTO products (product_id, product_name, price, quantity_in_stock)
VALUES (1, 'Widget', 19.99, 100);
 
-- Type violation: 'hello' is not an INT
INSERT 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 DECIMAL
INSERT 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 cases
INSERT 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 enforcement
INSERT 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 enforcement
INSERT 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 DB

Implicit Conversion Pitfalls

While 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.

CHECK Constraints: Refining Domains

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.

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
-- Basic CHECK constraints
CREATE 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 messages
CREATE 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 examples
INSERT 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 constraints
CREATE 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 logic
CREATE 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
    )
);

CHECK Constraint Best Practices

•Name your constraints — CONSTRAINT chk_positive_age CHECK (age > 0) produces clearer error messages than anonymous constraints.
•Keep constraints simple — Complex logic is hard to debug and may indicate a design problem. Consider triggers for complex validation.
•Document the business rule — Add comments explaining why the constraint exists, not just what it checks.
•Test boundary values — Verify behavior at exactly the constraint boundaries (age = 18, discount = 100, etc.).
•Handle NULL explicitly — CHECK constraints return TRUE for NULL by default. Use IS NOT NULL if NULL should fail.

NULL and CHECK Constraints

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.

Enumerated Types: Finite Domain Definition

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.

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
-- PostgreSQL: Native ENUM type support
 
-- Create an enum type
CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);
 
-- Use the enum in a table
CREATE 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 insertion
INSERT INTO orders (customer_id, status)
VALUES (1, 'confirmed');
 
-- Invalid insertion: 'approved' is not in the enum
INSERT 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 enum
SELECT enumlabel FROM pg_enum 
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;
 
-- Creating enum with explicit labels for display
CREATE 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'
);

ENUM vs CHECK vs Reference Table
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

Choose Based on Volatility

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.

User-Defined Types (UDTs)

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.

user_defined_domains.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
-- PostgreSQL: CREATE DOMAIN for reusable constrained types
 
-- Domain for email addresses
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
-- Domain for positive monetary amounts
CREATE DOMAIN money_positive AS DECIMAL(12,2)
    CHECK (VALUE > 0);
 
-- Domain for percentage values
CREATE DOMAIN percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Domain for US phone numbers
CREATE DOMAIN us_phone AS CHAR(12)
    CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$');
 
-- Domain for future dates
CREATE DOMAIN future_date AS DATE
    CHECK (VALUE > CURRENT_DATE);
 
-- Use domains in table definitions
CREATE 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 errors
INSERT 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 it
ALTER 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 definitions
SELECT 
    domain_name,
    data_type,
    character_maximum_length,
    domain_default
FROM information_schema.domains
WHERE domain_schema = 'public';

Benefits of User-Defined Domains

•Consistency — The same validation is applied everywhere the domain is used. Change it in one place, and all columns update.
•Semantic naming — email_address is more meaningful than VARCHAR(255). The schema documents its own semantics.
•Reduced errors — Developers can't accidentally omit constraints when creating new tables.
•Centralized maintenance — Pattern updates (e.g., supporting longer TLDs) require only one ALTER DOMAIN.
•Type safety — Some advanced uses allow custom operators and functions on the domain.

Limited Portability

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.

NOT NULL as a Domain Constraint

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: Every row must have a known, definite value for this attribute The attribute is mandatory for the entity to be meaningful Applications cannot defer providing this value This is a strong semantic statement that should align with business requirements.

When to Use NOT NULL
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

not_null_patterns.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
-- NOT NULL with defaults
CREATE 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 fields
INSERT INTO audit_log (event_type, created_by)
VALUES ('login', 1001);  -- created_at and event_data use defaults
 
-- NULL rejection
INSERT 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 CHECK
CREATE 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 invalid
INSERT 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 NULLs
UPDATE products SET description = 'No description available' 
WHERE description IS NULL;
 
-- Step 2: Add constraint
ALTER TABLE products ALTER COLUMN description SET NOT NULL;

NOT NULL by Default Philosophy

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.

Application vs Database Validation

A common architectural question: should domain validation occur in the application or the database? The answer is unequivocally both, but with different purposes:

Application-Level Validation

•User experience — Provide immediate, friendly error messages before database round-trip
•Rich validation — Complex business logic, cross-system checks, async validations
•Performance — Catch obvious errors early without database load
•Context-aware — Different rules for different user roles or contexts
•Format flexibility — Accept multiple input formats, normalize for storage

Database-Level Validation

•Last line of defense — Catches bugs in application code, bypasses, direct SQL access
•Data integrity — Guarantee that invalid data CANNOT exist, regardless of source
•Multi-application — Same rules enforced for all applications accessing the database
•Audit compliance — Provable enforcement for regulatory requirements
•Migration safety — Protects data during ETL, migrations, bulk operations

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.

validation_layers.js
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
// Application layer: User-friendly validation with detailed messages
function 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 endpoint
async 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" });
    }
}

Never Trust Application Validation Alone

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

The Foundation of Data Validity

Formal Definition of Domains

Data Types as Basic Domain Constraints

CHECK Constraints: Refining Domains

Enumerated Types: Finite Domain Definition

User-Defined Types (UDTs)

NOT NULL as a Domain Constraint

Application vs Database Validation

There’s more to understand.

Domain Constraints

The Foundation of Data Validity

Formal Definition of Domains

Data Types as Basic Domain Constraints

CHECK Constraints: Refining Domains

Enumerated Types: Finite Domain Definition

User-Defined Types (UDTs)

NOT NULL as a Domain Constraint

Application vs Database Validation

There’s more to understand.