Attribute Domains

What are attribute domains used for?

In a database, a domain is a named set of constraints that define the allowable values for an attribute.

General Syntax:

CREATE DOMAIN domain_name as <DATATYPE> <CONSTRAINT> <CONDITION>

Consider the attribute Age where we want the valid range of values to be between 0 and 120:

CREATE DOMAIN age_domain AS INTEGER CHECK (VALUE >= 0 AND <= 120);

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age age_domain NOT NULL, // This ensures that all values entered into the `Age` column meet the criteria defined in the `AgeDomain`.
  email VARCHAR(100) UNIQUE
);

Note the position: we can use domain to specify a data type for a column in a table. Can also define a general domain to be used as a data type for multiple columns in multiple tables.

Why are they used?

They help enforce data integrity by specifying a set of rules that the data in the attribute must follow.

List of constraints that can be used

CHECK constraint: to specify a condition that the domain values must satisfy.

CREATE DOMAIN age_domain AS INTEGER CHECK (VALUE >= 0 AND VALUE <= 150);

DEFAULT constraint: to provide a default value for the domain when no value is specified during insert.

CREATE DOMAIN date_of_birth AS DATE DEFAULT '1900-01-01';

NOT NULL constraint: to ensure that the domain value is not null.

CREATE DOMAIN email AS VARCHAR(255) NOT NULL;

CHECK constraint with function: to specify a condition that the domain values must satisfy using a function.

CREATE DOMAIN positive_integer AS INTEGER CHECK (VALUE >= 0);

CONSTRAINT clause: to name a constraint and specify the constraint type.

CREATE DOMAIN temperature AS FLOAT CONSTRAINT temp_range CHECK (VALUE >= -273.15 AND VALUE <= 1000);