Dropping Tables

From InterBase
Jump to: navigation, search

Go Up to Working with Tables


Use DROP TABLE to delete an entire table from the database.

Note: If you want to drop columns from a table, use ALTER TABLE.

Dropping a Table

Use 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 UNIQUE or PRIMARY KEY defined for it, and the ­PRIMARY KEY is referenced by a FOREIGN KEY in another table. First drop the FOREIGN KEY constraints 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 CHECK constraint of another table.
Note: DROP TABLE does 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, COUNTRY:

DROP TABLE COUNTRY;

Advance To: