DROP TABLE (SQL)

DROP TABLE (SQL) command removes a table. Only the table owner, a DBA, or SYSADM may execute the command.

When dropping a table, DBMS also drops all indexes and primary keys on the table. If the table has a primary key that is referenced by one or more foreign keys drop all foreign keys that reference the primary key before dropping the table. When table is dropped, all the data within that table is deleted automatically and cannot be restored by just re-creating that table.

When you no longer need a table, you can drop it from your schema.

Example 1:

The following statement drop Employee table from database. This command will error out if there are any referential constraints defined to this table.

DROP TABLE Employee;

In Oracle, you can drop a table that is referenced by foreign key constraints using the following syntax:

Example 2:

The following example drop Employee table along with all referential constraints to this table:

DROP TABLE Employee CASCADE CONSTRAINTS;

CASCADE: If any other objects in the database (foreign keys, constraints, and so on) that depend on the table are also dropped as a cascaded effect of the DROP TABLE statement. The CASCADE effect can cause quite dramatic changes in the database; therefore, use it with care. It’s usually a better idea to use the RESTRICT mode (explicitly drop the dependent foreign keys and constraints, using the appropriate ALTER or DROP statements) before dropping the column.

Foreign key constraints that reference the table being dropped will be dropped themselves.