Practical method of deleting associated tables in MySQL

In the MySQL database, after tables are associated with each other, they cannot be deleted at will, otherwise the structure of all associated tables will be affected. So how to safely delete associated tables? Let us find out.

Drop a foreign key constraint from a table

A foreign key is a special field that relates a table to its parent table. When the table is created, the foreign key constraints are already set. To remove the association between them, you need to use the following statement.

alter table table name drop foreign key foreign key alias;

The foreign key alias parameter refers to the foreign key code set when creating the table.

2. Delete the common table that is not associated

drop table table name;

When you delete a table, all data in the table will also be deleted. When deleting a table, it is best to back up the data in the table first.

3. Delete the parent table associated with other tables

When deleting a table with an associated relationship, using drop table example1 will result in an error because there is a foreign key that depends on the table.

For example, an example4 table is created that depends on the example1 table, and the foreign key stu_id of the example4 table depends on the primary key of the example1 table. The example1 table is the parent table of the example4 table.

If you want to delete the example4 table, you must first remove this dependency. The simplest way is to delete the child table example4 first, and then delete the parent table example1. But this may affect other data in the sub-table.

Another way is to drop the foreign key constraint of the child table first, then drop the parent table. This method will not affect other data in the subtable and can ensure the security of the database.

For example, the foreign key alias of example4 is d_fk. Delete the foreign key constraint of example4.

alter table example4 drop foreign key d_fk;.

You can run show create table example4 \G to check whether it has been deleted.

Then execute drop table example1;.

If the execution is successful, the operation is successful.