SQL DOMAIN

What does the SQL DOMAIN keyword do?

DOMAIN creates a reusable, constraint-backed, user-defined data type.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DOMAIN:

SQL DOMAIN Full Explanation

DOMAIN (used with the CREATE DOMAIN statement) lets you define a named, reusable data type that builds on an existing base type while adding business-specific rules such as NOT NULL, DEFAULT values, and CHECK constraints. Once created, the domain can be referenced in any table or view column exactly like a built-in type, centralizing data-quality logic and making future schema changes easier. Domains are stored in the system catalog, can be altered or dropped, and automatically propagate their rules to every column that uses them. They validate data on INSERT and UPDATE but do not retroactively scan existing rows unless those rows are touched. A single domain can be used thousands of times, ensuring consistency without duplicating constraint code. Support is defined in the SQL Standard; popular implementations include PostgreSQL and, more recently, MySQL and MariaDB.

SQL DOMAIN Syntax

CREATE DOMAIN domain_name
    AS base_data_type
    [ DEFAULT default_expression ]
    [ NOT NULL ]
    [ CHECK ( search_condition ) ];

ALTER DOMAIN domain_name
    { SET DEFAULT default_expression
    | DROP DEFAULT
    | SET NOT NULL
    | DROP NOT NULL
    | ADD CHECK ( search_condition )
    | DROP CONSTRAINT constraint_name };

DROP DOMAIN domain_name [ CASCADE | RESTRICT ];

SQL DOMAIN Parameters

domain_name (identifier) - The new domain's name.
base_data_type (data type) - Any built-in or existing type the domain is based on.
default_expression (expression) - Value assigned when none is supplied.
search_condition (boolean expression) - Rule every value must satisfy.
constraint_name (identifier) - Optional name for a CHECK constraint.

Example Queries Using SQL DOMAIN

-- 1. Create a domain enforcing valid email format
CREATE DOMAIN email AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$');

-- 2. Use the domain in a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    address email NOT NULL
);

-- 3. Add a default to the domain later
ALTER DOMAIN email SET DEFAULT 'unknown@example.com';

-- 4. Remove the domain when obsolete
DROP DOMAIN email CASCADE;

Expected Output Using SQL DOMAIN

Domain created and stored in catalog.
Table created; column "address" inherits domain rules. Attempts to insert an invalid email fail.
New rows omitting "address" now receive the default value.
All dependent columns are dropped (or the command fails without CASCADE).

Use Cases with SQL DOMAIN

Enforce company-wide rules like non-empty emails or positive monetary values.
Centralize validation logic instead of repeating CHECK clauses.
Simplify schema evolution by altering one domain rather than many tables.
Improve readability by giving semantic names (e.g., usd_money, url, percent).

Common Mistakes with SQL DOMAIN

Forgetting to use VALUE inside the CHECK expression, causing syntax errors.
Assuming every database supports DOMAIN – many do not.
Expecting existing invalid data to be flagged automatically; domains validate only new or updated rows.
Dropping a domain without CASCADE when dependent columns still exist.

First Introduced In

SQL-92 Standard; first major implementation in PostgreSQL 6.0

Frequently Asked Questions

What is the VALUE keyword inside a domain CHECK?

Inside a DOMAIN's CHECK clause, VALUE represents the data being validated. Use it just like a column name: `CHECK (VALUE > 0)`.

Can I add NOT NULL after the domain is created?

Yes. `ALTER DOMAIN domain_name SET NOT NULL;` will apply the NOT NULL rule to all future inserts and updates on columns that use the domain.

How do I drop a DOMAIN that other tables use?

Use `DROP DOMAIN name CASCADE;` to automatically drop or alter dependent objects. Without CASCADE, the command fails if dependencies exist.

Are domains the same as ENUM types?

No. ENUM stores a fixed set of literal values, while DOMAIN can wrap any base type and attach arbitrary constraints, giving greater flexibility.