Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

DROP SCHEMA

Statements

NAME

DROP SCHEMA: drop a schema

SYNOPSIS

<drop schema statement> ::= DROP SCHEMA <schema name> <drop behavior>

<schema name> ::= [ <catalog name> . ] <identifier>

<drop behavior> ::= CASCADE | RESTRICT

DESCRIPTION

DROP SCHEMA drops a schema. RESTRICT refuses the drop if the schema holds a table, a view, a domain, an assertion, a routine, a module, a collation, or a registered XML Schema or XQuery module. CASCADE drops each of them first: its tables, as DROP TABLE CASCADE does, and so the views, constraints and assertions of other schemas that refer to them; its views left; its domains, as DROP DOMAIN CASCADE does; its assertions; and its routines, as DROP ROUTINE CASCADE does, with what of other schemas invokes them; and its modules, as DROP MODULE CASCADE does; and its registered XML Schemas and XQuery modules, as DROP XML SCHEMA and DROP XQUERY MODULE CASCADE do. Either way, a module of another schema whose SCHEMA or PATH names it is dropped too (CREATE MODULE). A constraint named in the schema of a table or a domain of another schema is dropped too (extension), as ALTER TABLE or ALTER DOMAIN DROP CONSTRAINT drops it; RESTRICT refuses the drop while there is one. So is a view, a routine or a trigger of another schema whose text maps the schema by XMLMAP(SCHEMA ...) (XML values), as DROP VIEW, DROP ROUTINE and DROP TRIGGER drop it; RESTRICT refuses the drop while there is one. Its collations go last, as DROP COLLATION CASCADE drops them, so a column of one in another schema's table is made of the default collation, its rows copied, and that can fail if a constraint no longer holds.

Before all that, CASCADE drops each generated column of another schema's table whose generation expression names one of the schema's domains or collations, as ALTER TABLE DROP COLUMN with CASCADE drops it.

DROP SCHEMA runs within the session's transaction: ROLLBACK undoes it, as it does not undo a CREATE SCHEMA.

RULES

  1. The schema exists; a catalog named is the one catalog.
  2. Its user owns the schema.
  3. RESTRICT: the schema holds nothing, names no constraint of a table or domain of another schema, and no view, routine or trigger of another schema maps it by XMLMAP.
  4. The transaction is not READ ONLY.

PRIVILEGES

The schema is its user's.

ERRORS

SQLSTATE SQLCODE When
2A000 -102 No <drop behavior> is written
3F000 -103 There is no such schema, or a catalog other than the one there is is named
2A000 -103 RESTRICT, and the schema holds something
2A000 -104 Its user does not own the schema
23000 -201 A constraint no longer holds of the rows of a table reverted
25000 -917 The transaction is READ ONLY

EXAMPLES

Run by ANN, of a schema SHOP of a table ITEMS and a view of it:

DROP SCHEMA SHOP RESTRICT;
DROP SCHEMA SHOP CASCADE;
ROLLBACK;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SHOP';
DROP SCHEMA NOPE CASCADE;
turborel: SQLCODE -103 (SQLSTATE 2A000): schema SHOP holds SHOP.ITEMS, so it is dropped only with CASCADE [ISO/IEC 9075:1992 §11.2 SR3] (at character 12)
OK
OK
 SCHEMA_NAME
--------------------------------------------------------------
 SHOP
(1 row)
turborel: SQLCODE -103 (SQLSTATE 3F000): there is no schema NOPE [ISO/IEC 9075:1992 §11.2 SR2] (at character 12)

Run by BOB:

DROP SCHEMA ANN CASCADE;
turborel: SQLCODE -104 (SQLSTATE 2A000): schema ANN is owned by ANN, not BOB [ISO/IEC 9075:1992 §11.2 AR1] (at character 12)

STANDARD

ISO/IEC 9075:1992 §11.2; its collations, ISO/IEC 9075-2:2023 §11.2 GR5. SQL-86 has no DROP SCHEMA. Dropping the constraints named in the schema of another schema's tables and domains, which SQL-92 leaves, is an extension.

SEE ALSO

CREATE SCHEMA, DROP TABLE, DROP DOMAIN