Altering Domains

From InterBase
Jump to: navigation, search

Go Up to Working with Domains


ALTER DOMAIN changes any aspect of an existing domain except its NOT NULL setting. Changes that you make 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.

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

ALTER DOMAIN allows you to:

  • Drop an existing default value
  • Set a new default value
  • Drop an existing CHECK constraint
  • Add a new CHECK constraint
  • Modify the domain name and data type
  • Modify the data type of a column

The syntax for ALTER DOMAIN is:

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
};

The following statement sets a new default value for the CUSTNO domain:

ALTER DOMAIN CUSTNO SET DEFAULT 9999;

The following statement changes the name of the CUSTNO domain to CUSTNUM:

ALTER DOMAIN CUSTNO TO CUSTNUM;

The following statement changes the data type of the CUSTNUM domain to CHAR(20):

ALTER DOMAIN CUSTNUM TYPE CHAR(20);

The TYPE clause of ALTER DOMAIN does not allow you to make data type conversions that could lead to data loss. For example, it does not allow you to change the number of characters in a column to be less than the largest value in the column.

Advance To: