Domains.

    A domain is like a TYPE clause in Pascal and a typedef clause in C. In a database, a domain acts as a global column definition. After it has been defined, columns can be based on it instead of on native types supported by the engine. Of course, a domain is not a completely new definition, because it's based on the native types that are accepted in the engine. Contrary to programming languages, a domain 

A domain specifies a data type and a set of column attributes and constraints. Subsequent table definitions can use it to define columns. Let's see some declarations from the example employee.gdb database:

CREATE DOMAIN DEPTNO AS CHAR(3) CHECK
(VALUE = "000" OR
(VALUE > "0" AND VALUE <= "999")
OR VALUE IS NULL);

CREATE DOMAIN PROJNO
AS CHAR(5)
CHECK (VALUE = UPPER (VALUE));

CREATE DOMAIN CUSTNO
AS INTEGER
CHECK (VALUE > 1000);
ALTER DOMAIN CUSTNO SET DEFAULT 9999;

One thing that's different from a check constraint imposed on a field is the fact that for a domain, because it's an abstraction, the reserved word VALUE is used where the name of a field would be. This is part of the syntax IB v4.X accepts:

CREATE DOMAIN domain [AS] <datatype>
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [CHECK ( <dom_search_condition>)]
[COLLATE collation];

<dom_search_condition> = {
VALUE <operator> <val>
| VALUE [NOT] BETWEEN <val> AND <val>
| VALUE [NOT] LIKE <val> [ESCAPE <val>]
| VALUE [NOT] IN ( <val> [ , <val> ...])
| VALUE IS [NOT] NULL
| VALUE [NOT] CONTAINING <val>
| VALUE [NOT] STARTING [WITH] <val>
| ( <dom_search_condition>)
| NOT <dom_search_condition>
| <dom_search_condition> OR <dom_search_condition>
| <dom_search_condition> AND <dom_search_condition>
}

As you can see, the above syntax for <dom_search_condition> is not very different than the one used as part of the CREATE TABLE command to define a COMPUTED FIELD, also referred sometimes as a server-calculated field. If you apply the same check constraint to a field directly, the field's name would be instead of VALUE in the syntax shown.

Also in IB4.X, a provision was made to allow for limited changes to a domain using SQL. For other raw changes, see Metadata Manipulation. The ALTER DOMAIN command changes any aspect of an existing domain except its data type and NOT NULL setting. Changes made to a domain definition affect all column definitions based on the domain that have not been overridden at the table level. As the previous lines imply, a domain can be overridden at the table level when a field based in that domain is declared. A field defined through a domain can add a constraint to the one in the domain, change the default as declared in the domain, add the NOT NULL clause or change the collate, but it cannot void any restriction in the domain. For example, if the domain already specified NOT NULL or CHECK (VALUE>0), a field inherits these restrictions and cannot get rid of them, but it can add its own CHECK that's AND'ed with the one in the domain.

ALTER DOMAIN name {
[SET DEFAULT { literal | NULL | USER}]
| [DROP DEFAULT]
| [ADD [CONSTRAINT] CHECK ( <dom_search_condition>)]
| [DROP CONSTRAINT]
};

Since a domain can have only one CHECK constraint, the ADD options only works if there's no constraint in place or the one already in place should be deleted with DROP CONSTRAINT first.

The syntax for ALTER DOMAIN was extended again in IB6 to allow more changes in an official way instead of through QLI or direct manipulation of the system tables.

This page was last updated on 2000-06-02 22:25:01