Deletes a domain definition. If you attached to the database
using the PATHNAME qualifier, SQL also deletes the domain
definition from the repository.
1 – Environment
You can use the DROP DOMAIN statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mDROP[m[1m [1;4mDOMAIN[m[1m qq> <domain-name> qqwqqqqqqqqqqqqqqqwqq>[m [1m mq> [1;4mIF[m[1m [1;4mEXISTS[m[1m qqj[m [1m [m [1m [m
3 – Arguments
3.1 – domain-name
Specifies the name of the domain you want to delete.
3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
4 – Examples
Example 1: Deleting a domain not referred to by columns
SQL> --
SQL> -- The following CREATE DOMAIN statement creates a domain
SQL> -- that is not used by any columns:
SQL> --
SQL> CREATE DOMAIN ABCD IS CHAR(4);
SQL> --
SQL> -- The SHOW DOMAINS statement shows domain ABCD at the
SQL> -- top of the list:
SQL> --
SQL> SHOW DOMAINS
User domains in database with filename personnel
ABCD CHAR(4)
ADDRESS_DATA_1_DOM CHAR(25)
ADDRESS_DATA_2_DOM CHAR(20)
.
.
.
SQL> --
SQL> -- Now delete the domain:
SQL> --
SQL> DROP DOMAIN ABCD;
SQL> --
SQL> -- The SHOW DOMAINS statement shows that the
SQL> -- domain ABCD has been deleted:
SQL> --
SQL> SHOW DOMAINS
User domains in database with filename personnel
ADDRESS_DATA_1_DOM CHAR(25)
ADDRESS_DATA_2_DOM CHAR(20)
.
.
.
Example 2: Deleting a domain referred to by columns
The following example deletes a domain definition. Because a
column refers to the domain definition and a constraint refers
to the column, you must first alter the table before deleting the
domain.
SQL> --
SQL> -- Attempt to delete the domain SEX_DOM. Error messages
SQL> -- indicate that the table EMPLOYEES uses the domain
SQL> -- SEX_DOM, so SEX_DOM cannot yet be deleted:
SQL> --
SQL> DROP DOMAIN SEX_DOM;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELEXI, field SEX_DOM is used in relation EMPLOYEES
-RDMS-F-FLDNOTDEL, field SEX_DOM has not been deleted
SQL> --
SQL> -- Looking at the EMPLOYEES table shows that SEX is the
SQL> -- column that depends on the domain SEX_DOM. Try
SQL> -- to delete the column SEX; error messages indicate that the
SQL> -- constraint EMP_SEX_VALUES depends on the column SEX:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP COLUMN SEX;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field SEX is referenced in constraint EMP_SEX_VALUES
-RDMS-F-RELFLDNOD, field SEX has not been deleted
from relation EMPLOYEES
SQL> --
SQL> -- Delete the constraint EMP_SEX_VALUES:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_SEX_VALUES;
SQL> --
SQL> -- Because EMP_SEX_VALUES was the only constraint or index
SQL> -- that depended on the column SEX, you can now delete
SQL> -- the column SEX:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP COLUMN SEX;
SQL> --
SQL> -- The column SEX in the table EMPLOYEES was the only column in
SQL> -- the database that depended on the domain SEX_DOM, so you can
SQL> -- now delete the domain SEX_DOM:
SQL> --
SQL> DROP DOMAIN SEX_DOM;
SQL>