How to delete related tables in mysql

In the mysql database, after the tables are associated, they cannot be deleted at will, otherwise it will affect the structure of all associated tables. So how to safely delete associated tables, let us find out.

Recommended courses: MySQL Tutorial.

mysql uses the drop command to delete the associated table. The method is:

1. Delete the foreign key constraints of the table

Foreign key is a special field that associates a table with its parent table. When creating the table, foreign key constraints have already been set. To remove the association between them, you need to use the following statement.

alter table 表名 drop foreign key 外键别名；

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

2. Delete ordinary tables that are not related

drop table 表名；

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 that is associated with other tables

When deleting the related table, using drop table example1 will report an error because there are foreign key dependencies. For this 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 subtable.

Another method is to delete the foreign key constraints of the child table first, and then delete 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 table is d_fk, delete the foreign key constraint of example4

alter table example4 drop foreign key d_fk;.

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

Then execute drop table example1;.

If the execution is successful, the operation is successful.

The above is the detailed content of How to delete related tables in mysql. For more information, please follow other related articles on the PHP Chinese website!