Go Up to Working with Tables
DROP TABLE to delete an entire table from the database.
- Note: If you want to drop columns from a table, use
Dropping a Table
DROP TABLE to remove the data, metadata, and indexes of a table from a database. It also drops any triggers that are based on the table. A table can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.
You cannot drop a table that is referenced in a computed column, a view, integrity constraint, or stored procedure. You cannot drop a table that is being used by an active transaction until the table is no longer in use.
DROP TABLE fails and returns an error if:
- The person who attempts to drop the table is not the owner of the table.
- The table is in use when the drop is attempted. The drop is postponed until the table is no longer in use.
- The table has a
PRIMARY KEYdefined for it, and the
PRIMARY KEYis referenced by a
FOREIGN KEYin another table. First drop the
FOREIGN KEYconstraints in the other table, then drop the table.
- The table is used in a view, trigger, stored procedure, or computed column. Remove the other elements before dropping the table.
- The table is referenced in the
CHECKconstraint of another table.
DROP TABLEdoes not delete external tables; it removes the table definition from the database. You must explicitly delete the external file.
DROP TABLE Syntax
DROP TABLE name;
The following statement drops the table,
DROP TABLE COUNTRY;