Domain Types for Reusable Constraints

1. What a domain type is and what problem it solves

A domain type is a custom data type that builds on an existing base data type and extends it with additional rules such as NOT NULL, a default value, and any number of CHECK conditions. Once a domain type is defined, it can be used like any built-in data type for columns in any number of tables, with all its bound rules automatically applying, without needing to be written out again at every single spot.

The problem a domain type solves is the duplication of identical constraints across many columns and tables. An email address field in practice often appears in several tables, for example for customers, employees, and suppliers, and each of these columns needs the same regex check for a valid format. Without a domain type, the same CHECK condition must be repeated in every single table definition, risking that a later change to the rule only gets applied in some places, not all of them.

A domain type centralizes this rule at a single place in the schema. A later adjustment of the validation logic via ALTER DOMAIN automatically affects every column using that domain type, guaranteeing consistency that would be unattainable with a manually duplicated CHECK condition per column.

2. CREATE DOMAIN in detail

The syntax CREATE DOMAIN domain_name AS base_type defines a new domain type that can subsequently be used in a column definition exactly like a built-in type. The base type can be any built-in data type, such as NUMERIC, VARCHAR, or INTEGER, and the domain type fully inherits its underlying behavior, augmented with the additional rules set at definition time.

A simple domain type for a positive monetary amount shows the basic principle: NUMERIC as the base type, augmented with NOT NULL and a CHECK condition that rejects negative values. Once this domain type is defined, any column in any table that needs a positive amount can use positive_amount instead of NUMERIC(12,2) NOT NULL CHECK (VALUE >= 0), which makes the schema considerably more readable and maintainable.

-- PostgreSQL: a reusable domain type for monetary amounts
CREATE DOMAIN positive_amount AS NUMERIC(12,2)
    NOT NULL
    DEFAULT 0.00
    CHECK (VALUE >= 0);

-- Use the domain type exactly like a built-in type in any table
CREATE TABLE invoice (
    invoice_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    net_amount positive_amount,
    tax_amount positive_amount
);

CREATE TABLE refund (
    refund_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- same validation rule, reused without duplicating the CHECK expression
    refund_amount positive_amount
);

3. Combining domain types with check constraints

Inside the CHECK condition of a domain type, the keyword VALUE refers to the value currently being validated, analogous to NEW in a trigger. This condition can be arbitrarily complex, for instance a regex pattern for an email format or a combination of length checking and character set validation for an IBAN. A domain type can also carry several named CHECK constraints at once, which simplifies targeted error handling later, because each rule gets its own descriptive name.

A practical example is a domain type for a normalized email address that simultaneously checks a rough format pattern and enforces the maximum length per RFC 5321. This combination of several rules into a single, reusable type would be a considerable maintenance burden if repeated across ten different tables, with each rule formulated separately as a per-column CHECK constraint.

-- PostgreSQL: a domain type bundling two named rules for email columns
CREATE DOMAIN email_address AS VARCHAR(254)
    CONSTRAINT email_not_empty CHECK (length(trim(VALUE)) > 0)
    CONSTRAINT email_format CHECK (
        VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
    );

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       email_address NOT NULL
);

CREATE TABLE supplier (
    supplier_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- identical validation reused, no copy-pasted regex
    contact_email email_address
);

4. Domain type vs custom function: the conceptual difference

A common misunderstanding is confusing a domain type with an IMMUTABLE function called inside a CHECK constraint. The decisive difference lies in responsibility: a function must be explicitly referenced in its own CHECK constraint in every table, while a domain type binds the validation to the data type itself. A column declared as email_address can never contain an invalid value, because the check is inseparably tied to the type, not to a separately maintained condition.

This difference also affects schema readability: a column definition with a domain type shows its business meaning directly in the type name, while a function used inside a CHECK constraint must first be looked up to understand its meaning. For frequently recurring, simple validation patterns, a domain type is therefore the clearer and more self-documenting choice.

5. Changing validation logic centrally with Alter Domain

The biggest practical advantage of a domain type shows when a business rule changes later. With ALTER DOMAIN domain_name ADD CONSTRAINT, a new rule can be added to an already existing domain type, which then automatically applies to every column using that type, regardless of how many tables already use it. A change made in one single place in the schema thus propagates consistently across the entire data model.

Important here: just as with adding a regular CHECK constraint after the fact, ALTER DOMAIN ADD CONSTRAINT fails immediately if even a single existing value in any column using this domain type violates the new rule. Before such a change, it is therefore important to systematically verify that all affected columns in all affected tables already satisfy the new rule.

-- PostgreSQL: tighten validation for all columns using this domain at once
ALTER DOMAIN email_address
    ADD CONSTRAINT email_no_plus_alias
    CHECK (VALUE !~ '\+[^@]*@');

-- Check for violations across every table using the domain type first,
-- querying the system catalog to find them
SELECT c.relname AS table_name, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'email_address'
  AND a.attnum > 0;

6. Null handling and default values for domain types

A domain type can carry its own NOT NULL rule and its own default value, both of which automatically apply when a column is created with this type, as long as the column definition itself does not set a differing rule. An explicit NOT NULL or DEFAULT specification directly in a table's column definition overrides the domain type's default for that one column, which can be useful in rare exceptional cases, but locally softens the domain type's consistency guarantee for that column.

Inside the CHECK condition of a domain type, the same three-valued logic applies as for a regular CHECK constraint: a condition that hits a NULL value evaluates to unknown and is therefore not counted as a violation. A domain type without an explicit NOT NULL therefore still allows NULL values, even if the CHECK condition enforces a strict rule for every concrete value, which is often overlooked at definition time.

7. Migrating existing columns to a domain type

An existing column that was previously defined directly as VARCHAR or NUMERIC with an individual CHECK constraint can later be switched to a domain type to centralize the rule. The switch requires an ALTER TABLE ... ALTER COLUMN ... TYPE that names the new domain type as the target type, with PostgreSQL automatically checking whether all existing values in the column satisfy the new domain type's rules.

During this migration, it is sensible to remove the column's original, individual CHECK constraint only after the switch has succeeded, to avoid double checks during the transition period, but also not to accidentally lose a rule too early. A systematic test in a staging environment with a copy of the production data reliably reveals whether unexpected existing data violates the new, centralized rule.

-- Existing column with an ad-hoc CHECK constraint
CREATE TABLE legacy_contact (
    contact_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      VARCHAR(254) NOT NULL,
    CONSTRAINT chk_legacy_email_format
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Migrate the column to the centralized domain type
ALTER TABLE legacy_contact
    ALTER COLUMN email TYPE email_address;

-- Drop the now-redundant per-column constraint after validation succeeds
ALTER TABLE legacy_contact
    DROP CONSTRAINT chk_legacy_email_format;

8. Vendor support: PostgreSQL, MySQL, Oracle

PostgreSQL offers the most complete implementation of domain types among widely used relational databases, with CREATE DOMAIN, ALTER DOMAIN, and full integration into the system catalog. MySQL does not know the CREATE DOMAIN concept at all, there is no direct replacement offering the same central reusability across any number of tables. As a workaround, MySQL only leaves a documented naming convention for CHECK constraints, combined with generated migration scripts that consistently create the same condition across multiple tables.

Oracle offers a related but considerably more limited concept with subtypes in PL/SQL, primarily intended for variables and parameters in stored procedures, not for table columns themselves. Anyone needing to port a schema platform-independently between PostgreSQL and MySQL should therefore plan a fallback strategy from the start, such as a code generator that produces the appropriate CHECK constraints for each target database from a central YAML or JSON definition.

9. Domain type vs per-column check constraint vs function

The choice between a domain type, an individual per-column CHECK constraint, and an IMMUTABLE function depends on how often the same rule is reused across the schema, and whether the rule is better thought of as a standalone type or as callable logic. A domain type pays off from the second or third reuse of the same rule across different tables onward.

10. Summary

A domain type bundles a base data type with NOT NULL, default values, and any number of CHECK conditions into its own, reusable type that can be used across any number of tables and columns. CREATE DOMAIN defines the type once, ALTER DOMAIN allows later centralized adjustments that automatically affect every column using it, without needing to change the rule individually everywhere.

PostgreSQL offers the most complete support for domain types, MySQL does not know the concept and requires a replacement through naming conventions and code generators. From the second or third reuse of the same rule across different tables onward, a domain type pays off compared to an individual per-column CHECK constraint, because it guarantees consistency and considerably simplifies later maintenance.

11. FAQ: Domain Types for Reusable Constraints