ALTER DOMAIN

Go Up to Statement and Function Reference (Language Reference Guide)

Changes a domain definition. Available in gpre, DSQL, and isql, but not in the stored procedure or trigger language.

ALTER DOMAIN { name |
old_name TO new_name }

 SET DEFAULT {literal | NULL | USER}
| DROP DEFAULT
| ADD [CONSTRAINT] CHECK (dom_search_condition)
| DROP CONSTRAINT
| new_col_name
| TYPE data_type;
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
operator = {= | < | > | <= | >= | !< | !> | <> | !=}

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Argument	Description
<name>	Represents the name of an existing domain.
`SET DEFAULT`	Specifies a default column value that is entered when no other entry is made. Values: <literal>—Inserts a specified string, numeric value, or date value. `NULL`—Enters a `NULL` value. `USER`—Enters the user name of the current user; the olumn must be of compatible text type to use the default. Defaults set at the column level overrides defaults set at the domain level.
`DROP DEFAULT`	Drops an existing default.
`ADD[CONSTRAINT]CHECK`<dom_search_condition>	Adds a `CHECK` constraint to the domain definition; a domain definition can include only one `CHECK` constraint.
`DROP CONSTRAINT`	Drops the `CHECK` constraint from the domain definition.
<new_col_name>	Changes the domain name.
`TYPE` <data_type>	Changes the domain data type.

Description: ALTER DOMAIN changes any aspect of an existing domain except its 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.

Note:
To change the NOT NULL setting of a domain, drop the domain and recreate it with the desired combination of features.

The TYPE clause of ALTER DOMAIN does not allow you to make data type conversions that could lead to data loss.

A domain can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Example: The following isql statements create a domain that must have a value > 1,000, then alter it by setting a default of 9,999:

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

Advance To:

ALTER EXCEPTION

ALTER DOMAIN

See Also

Advance To: