`CREATE DOMAIN` Examples

Creating a domain that can take values greater than 1,000, with a default value of 10,000.
```
CREATE DOMAIN CUSTNO AS
  INTEGER DEFAULT 10000
  CHECK (VALUE > 1000);
```
Creating a domain that can take the values 'Yes' and 'No' in the default character set specified during the creation of the database.
```
CREATE DOMAIN D_BOOLEAN AS
  CHAR(3) CHECK (VALUE IN ('Yes', 'No'));
```

Creating a domain with the UTF8 character set and the UNICODE_CI_AI collation.

CREATE DOMAIN FIRSTNAME AS
  VARCHAR(30) CHARACTER SET UTF8
  COLLATE UNICODE_CI_AI;

Creating a domain of the DATE type that will not accept NULL and uses the current date as the default value.
```
CREATE DOMAIN D_DATE AS
  DATE DEFAULT CURRENT_DATE
  NOT NULL;
```
Creating a domain defined as an array of 2 elements of the NUMERIC(18, 3) type.The starting array index is 1.
```
CREATE DOMAIN D_POINT AS
  NUMERIC(18, 3) [2];
```
Note

Domains defined over an array type may be used only to define table columns.You cannot use array domains to define local variables in PSQL modules.

Creating a domain whose elements can be only country codes defined in the COUNTRY table.

CREATE DOMAIN D_COUNTRYCODE AS CHAR(3)
  CHECK (EXISTS(SELECT * FROM COUNTRY
         WHERE COUNTRYCODE = VALUE));

Note	The example is given only to show the possibility of using predicates with queries in the domain test condition.It is not recommended to create this style of domain in practice unless the lookup table contains data that are never deleted.

`ALTER DOMAIN`

Alters the attributes of a domain or renames a domain

Available in

DSQL, ESQL

Syntax

ALTER DOMAIN domain_name
  [TO new_name]
  [TYPE <datatype>]
  [{SET DEFAULT {<literal> | NULL | <context_var>} | DROP DEFAULT}]
  [{SET | DROP} NOT NULL]
  [{ADD [CONSTRAINT] CHECK (<dom_condition>) | DROP CONSTRAINT}]

<datatype> ::=
   <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::=
  !! See Scalar Data Types Syntax !!

<blob_datatype> ::=
  !! See BLOB Data Types Syntax !!

!! See also CREATE DOMAIN Syntax !!

Table 1. `ALTER DOMAIN` Statement Parameters
Parameter	Description
new_name	New name for domain.The maximum length is 63 characters
literal	A literal value that is compatible with datatype
context_var	Any context variable whose type is compatible with datatype

The ALTER DOMAIN statement enables changes to the current attributes of a domain, including its name.You can make any number of domain alterations in one ALTER DOMAIN statement.

docnext count = 7

`ALTER DOMAIN` clauses

TO name

Renames the domain, as long as there are no dependencies on the domain, i.e. table columns, local variables or procedure arguments referencing it.

SET DEFAULT

Sets a new default value for the domain, replacing any existing default.

DROP DEFAULT

Deletes a previously specified default value and replace it with NULL.

SET NOT NULL

Adds a NOT NULL constraint to the domain;columns or parameters of this domain will be prevented from being written as NULL, i.e. a value is required.

Note	Adding a `NOT NULL` constraint to an existing domain will subject all columns using this domain to a full data validation, so ensure that the columns have no nulls before attempting the change.

DROP NOT NULL

Drops the NOT NULL constraint from the domain.

Note	An explicit `NOT NULL` constraint on a column that depends on a domain prevails over the domain.In this situation, the modification of the domain to make it nullable does not propagate to the column.

ADD CONSTRAINT CHECK

Adds a CHECK constraint to the domain.If the domain already has a CHECK constraint, it has to be deleted first, using an ALTER DOMAIN statement that includes a DROP CONSTRAINT clause.

TYPE

Changes the data type of the domain to a different, compatible one.The system will forbid any change to the type that could result in data loss.An example would be if the number of characters in the new type were smaller than in the existing type.

Important

When you alter the attributes of a domain, existing PSQL code may become invalid.For information on how to detect it, read the piece entitled The RDB$VALID_BLR Field in Appendix A.

What `ALTER DOMAIN` Cannot Alter

If the domain was declared as an array, it is not possible to change its type or its dimensions;nor can any other type be changed to an array type.
The collation cannot be changed without dropping the domain and recreating it with the desired attributes.

Who Can Alter a Domain

The ALTER DOMAIN statement can be executed by:

Administrators
The owner of the domain
Users with the ALTER ANY DOMAIN privilege

Domain alterations can be prevented by dependencies from objects to which the user does not have sufficient privileges.

`ALTER DOMAIN` Examples

Changing the data type to INTEGER and setting or changing the default value to 2,000:
```
ALTER DOMAIN CUSTNO
  TYPE INTEGER
  SET DEFAULT 2000;
```
Renaming a domain.
```
ALTER DOMAIN D_BOOLEAN TO D_BOOL;
```

Deleting the default value and adding a constraint for the domain:

ALTER DOMAIN D_DATE
  DROP DEFAULT
  ADD CONSTRAINT CHECK (VALUE >= date '01.01.2000');

Changing the CHECK constraint:

ALTER DOMAIN D_DATE
  DROP CONSTRAINT;

ALTER DOMAIN D_DATE
  ADD CONSTRAINT CHECK
    (VALUE BETWEEN date '01.01.1900' AND date '31.12.2100');

Changing the data type to increase the permitted number of characters:
```
ALTER DOMAIN FIRSTNAME
  TYPE VARCHAR(50) CHARACTER SET UTF8;
```
Adding a NOT NULL constraint:
```
ALTER DOMAIN FIRSTNAME
  SET NOT NULL;
```
Removing a NOT NULL constraint:
```
ALTER DOMAIN FIRSTNAME
  DROP NOT NULL;
```

`DROP DOMAIN`

Drops an existing domain

Available in

DSQL, ESQL

Syntax

DROP DOMAIN domain_name

The DROP DOMAIN statement deletes a domain that exists in the database.It is not possible to delete a domain if it is referenced by any database table columns or used in any PSQL module.To delete a domain that is in use, all columns in all tables that refer to the domain have to be dropped and all references to the domain have to be removed from PSQL modules.

Who Can Drop a Domain

The DROP DOMAIN statement can be executed by:

Administrators
The owner of the domain
Users with the DROP ANY DOMAIN privilege

Example of `DROP DOMAIN`

Deleting the COUNTRYNAME domain

DROP DOMAIN COUNTRYNAME;

CREATE DOMAIN Examples

CREATE DOMAIN Examples

ALTER DOMAIN

docnext count = 7

ALTER DOMAIN clauses

What ALTER DOMAIN Cannot Alter