Learn online DBMS “Drop Table Command in MySQL” Complete Lesson 9

DROP TABLE COMMAND:This command is used to delete tables. For example, suppose you want to drop the Teacher table then the command would be:

DROP TABLE Teacher CASCADE;

Thus Teacher table would be dropped and with the CASCADE option, all the constraints that refer this table would also be automatically dropped.

However if the requirement is that the table should not be dropped if it is being referenced in some other table then RESTRICT option can be used as shown below:

DROP TABLE Teacher RESTRICT;

Drop table command can also be used to drop named constraints. However it is used along with the Alter Table command which is discussed in next section.

Note in MySQL server 5.6.14, RESTRICT and CASCADE options are not supported though they are permitted to make porting easier.

In MySQL server 5.6.14, you can simply type “Drop Table” followed by the table name to delete a table from the database. Also it will not allow you to drop a table if the table is being referenced in some other table.

As shown above, there were two tables Department and Teacher. Table Teacher contained a foreign key that reference the Primary key of Department table. Hence we were dropping Department table, error is shown as Department table is being referred in Teacher Table. Also note that the CASCADE option is not working as it is not supported in MySQL server 5.6.14. Department table can only be deleted if all the tables that refer Department table are deleted so that there are no references of this table anywhere else. That is why after we have dropped Teacher table, Department table deletion was possible.