Using the VALUE Keyword
Go Up to Creating Domains (Data Definition Guide)
VALUE
defines the set of values that is valid for the domain. VALUE
is a placeholder for the name of a column that will eventually be based on the domain. The search condition can verify whether the value entered falls within a certain range, or match it to any one value in a list of values.
If
NULL
values are allowed, they must be included in the CHECK
constraint, as in the following example:CHECK ((VALUE IS NULL) OR (VALUE > 1000));
The next statement creates a domain where value must be > 1,000:
CREATE DOMAIN CUSTNO
AS INTEGER
CHECK (VALUE > 1000);
The following statement creates a domain that must have a positive value greater than 1,000, with a default value of 9,999.
CREATE DOMAIN CUSTNO
AS INTEGER
DEFAULT 9999
CHECK (VALUE > 1000);
The next statement limits the values entered in the domain to four specific values:
CREATE DOMAIN PRODTYPE
AS VARCHAR(12)
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));
When a problem cannot be solved using comparisons, you can instruct the system to search for a specific pattern in a character column. For example, the next search condition allows only cities in California to be entered into columns that are based on the CALIFORNIA
domain:
CREATE DOMAIN CALIFORNIA
AS VARCHAR(25)
CHECK (VALUE LIKE '%, CA');