Domain Constraints

Domain Constraints
Every column has a domain, in other words a set of legal values (see Section 3.2.1). For
example, the sex of a member of staff is either ‘M’ or ‘F’, so the domain of the column
sex of the Staff table is a single character string consisting of either ‘M’ or ‘F’. The ISO
standard provides two mechanisms for specifying domains in the CREATE and ALTER
TABLE statements. The first is the CHECK clause, which allows a constraint to be
defined on a column or the entire table. The format of the CHECK clause is:
CHECK (searchCondition)
In a column constraint, the CHECK clause can reference only the column being defined.
Thus, to ensure that the column sex can only be specified as ‘M’ or ‘F’, we could define
the column as:
sex CHAR NOT NULL CHECK (sex IN (‘M’, ‘F’))
However, the ISO standard allows domains to be defined more explicitly using the CREATE
DOMAIN statement:
CREATE DOMAIN DomainName [AS] dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
A domain is given a name, DomainName, a data type (as described in Section 6.1.2),
an optional default value, and an optional CHECK constraint. This is not the complete
definition, but it is sufficient to demonstrate the basic concept. Thus, for the above
example, we could define a domain for sex as:
CREATE DOMAIN SexType AS CHAR
DEFAULT ‘M’
CHECK (VALUE IN (‘M’, ‘F’));
This creates a domain SexType that consists of a single character with either the value ‘M’
or ‘F’. When defining the column sex, we can now use the domain name SexType in place
of the data type CHAR:
sex SexType NOT NULL
The searchCondition can involve a table lookup. For example, we can create a domain
BranchNumber to ensure that the values entered correspond to an existing branch number in
the Branch table, using the statement:
CREATE DOMAIN BranchNumber AS CHAR(4)
CHECK (VALUE IN (SELECT branchNo FROM Branch));
The preferred method of defining domain constraints is using the CREATE DOMAIN
statement. Domains can be removed from the database using the DROP DOMAIN
statement:
DROP DOMAIN DomainName [RESTRICT | CASCADE]
The drop behavior, RESTRICT or CASCADE, specifies the action to be taken if the
domain is currently being used. If RESTRICT is specified and the domain is used in an
existing table, view, or assertion definition (see Section 6.2.5), the drop will fail. In the
case of CASCADE, any table column that is based on the domain is automatically changed
to use the domain’s underlying data type, and any constraint or default clause for the
domain is replaced by a column constraint or column default clause, if appropriate.