DROP DOMAIN
NAME
DROP DOMAIN: drop a domain
SYNOPSIS
<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>
<drop behavior> ::= CASCADE | RESTRICT
DESCRIPTION
DROP DOMAIN drops a domain, and the USAGE privileges on it. RESTRICT refuses the drop if a column is of the domain, or a view, a constraint or a generated column's generation expression CASTs to it.
CASCADE drops it all the same. Each column of the domain keeps its data type, and the domain's default, if it has none of its own; and each of the domain's constraints is kept on it, as a constraint of its table. The views, constraints and assertions that CAST to the domain are dropped. Each generated column whose generation expression CASTs to it is dropped first, as ALTER TABLE DROP COLUMN with CASCADE drops it. A drop that would leave a table no column is refused before it drops any. If the drop fails after one is dropped, the transaction can only roll back.
DROP DOMAIN runs within the session's transaction: ROLLBACK undoes it.
RULES
- The domain exists, and its user owns the domain's schema.
- RESTRICT: no column is of the domain, and no view, constraint or generation expression CASTs to it.
- The transaction is not READ ONLY.
PRIVILEGES
The domain's schema is its user's.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -102 | No <drop behavior> is written |
| 2A000 | -103 | A rule above is broken |
| 2A000 | -104 | Its user does not own the domain's schema |
| 25000 | -917 | The transaction is READ ONLY |
EXAMPLES
Of the domain MONEY of CREATE DOMAIN, DECIMAL(9,2) DEFAULT 0 with the constraint NOT_NEGATIVE, and the table LEDGER (ENTRY INTEGER, AMOUNT MONEY):
DROP DOMAIN MONEY RESTRICT;
DROP DOMAIN MONEY CASCADE;
INSERT INTO LEDGER (ENTRY) VALUES (1);
INSERT INTO LEDGER VALUES (2, -1);
SELECT * FROM LEDGER;
DROP DOMAIN NOPE CASCADE;
turborel: SQLCODE -103 (SQLSTATE 2A000): domain ANN.MONEY is used by column ANN.LEDGER.AMOUNT, so it is dropped only with CASCADE [ISO/IEC 9075:1992 §11.27 SR2] (at character 12)
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.NOT_NEGATIVE ON LEDGER.AMOUNT of ANN.LEDGER is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
ENTRY | AMOUNT
--------------+-------------
1 | 0.00
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): there is no domain ANN.NOPE [ISO/IEC 9075:1992 §11.27 SR1] (at character 12)
AMOUNT kept the domain's default and its constraint.
STANDARD
ISO/IEC 9075:1992 §11.27. SQL-86 has no domains. A generation expression that CASTs to the domain refuses RESTRICT, as ISO/IEC 9075-2:2023 §11.40 SR2a says. That CASCADE drops its generated column is turborel's reading, as the standard does not say.