Drop Table

Drop Table

Suppose we need to delete any of the tables that we have created from the database. We can use DROP statement to drop the table. It will permanently delete the table from the database.

DROP TABLE CATEGORY;

This is the command to drop the table from the database. But above query will not allow dropping the table, as it has dependent table – BOOK_CATEGORY. Hence if we need to drop any table then we needs to drop all its child tables and then the parent table.

When we try to drop BOOK_CATEGORY, it will get dropped without any error even though it has ISBN in it. Why? ISBN is not the child of BOOK_CATEGORY, but it is from the parent table. Hence dropping the child table does not give any issue.

Hence dropping the tables would be in following order:

DROP TABLE BOOK_CATEGORY;
DROP TABLE CATEGORY;

But in huge tables we might not know which all child tables are, or we might need to spend time searching child tables. Hence oracle provides CASCADE feature, which drops all the child tables first and then the parent table that we are dropping.

DROP TABLE CATEGORY CASCADE; --Automatically BOOK_CATEGORY would be dropped.

DROP TABLE CATEGORY PURGE;

Above PURGE is used while dropping the table, which will drop child tables as well as parent table. But it will not keep dropped copy of the tables in the recycle bin of database. Hence if this option is used, we cannot recover these tables latter. This option is like Shift + Delete a folder.