Altering Domains
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.
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.