DROP TABLE
NAME
DROP TABLE: drop a base table
SYNOPSIS
<drop table statement> ::= DROP TABLE <table name> <drop behavior>
<drop behavior> ::= CASCADE | RESTRICT
DESCRIPTION
DROP TABLE drops a base table: its rows, its indexes, and the privileges on it. RESTRICT refuses the drop if a view reads the table, or a constraint of another table or an assertion refers to it. CASCADE drops those views, and the views that read them, however far; drops the assertions; and takes from each other table the constraints that refer to it, a foreign key that references it among them, leaving the table. A function or procedure whose body reads the table is refused the same way, and dropped by CASCADE with what invokes it.
DROP TABLE runs within the session's transaction: ROLLBACK undoes it.
Until the transaction ends, others read the table as it was; one that
writes it is refused (SQLSTATE TB911), or waits after SET LOCKS.WAIT = 'ON'.
RULES
- The table exists, and is a base table, not a view.
- Its user owns the table's schema.
- The table is not a declared local temporary table: that goes when the session ends.
- RESTRICT: no view, no constraint of another table, and no assertion refers to the table.
- No cursor of the transaction reads the table.
- The transaction is not READ ONLY.
PRIVILEGES
The table'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 table's schema |
| 24000 | -501 | A cursor of the transaction reads the table |
| 25000 | -917 | The transaction is READ ONLY |
| TB911 | -911 | Another transaction holds a lock on the table |
EXAMPLES
Of tables DEPT (CODE CHAR(3) PRIMARY KEY) and EMP (ID INTEGER, DEPT CHAR(3) REFERENCES DEPT), and a view ENG of EMP:
DROP TABLE DEPT RESTRICT;
DROP TABLE EMP RESTRICT;
DROP TABLE ENG CASCADE;
DROP TABLE NOPE CASCADE;
DROP TABLE DEPT;
turborel: SQLCODE -103 (SQLSTATE 2A000): constraint ANN.EMP_FOREIGN1 of ANN.EMP refers to ANN.DEPT, so it is dropped only with CASCADE [ISO/IEC 9075:1992 §11.18 SR4] (at character 11)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.EMP is read by view ANN.ENG, so it is dropped only with CASCADE [ISO/IEC 9075:1992 §11.18 SR4] (at character 11)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.ENG is a view, not a base table [ISO/IEC 9075:1992 §11.18 SR2] (at character 11)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.NOPE is not defined [ISO/IEC 9075:1992 §11.18 SR1] (at character 11)
turborel: SQLCODE -102 (SQLSTATE 2A000): expected CASCADE or RESTRICT, found end of statement (at character 15)
With CASCADE, EMP stays, without its foreign key:
DROP TABLE DEPT CASCADE;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'EMP';
OK
CONSTRAINT_NAME
--------------------------------------------------------------
(0 rows)
STANDARD
ISO/IEC 9075:1992 §11.18. SQL-86 has no DROP TABLE.