Force drop mysql bypassing foreign key constraint

mysql foreign-keys sql-drop

I'm trying to delete all tables from a database except one, and I end up having the following error:

Cannot delete or update a parent row: a foreign key constraint fails

Of course I could trial and error to see what those key constraints are and eventually delete all tables but I'd like to know if there is a fast way to force drop all tables (as I'll be able to re-insert those I don't want deleted).

Google aimed me at some site that suggested the following method:

mysql> SET foreign_key_checks = 0;
mysql> drop table ...
mysql> SET foreign_key_checks = 1;

Short answer is it didn't really do the trick since I ended up receiving the same error while I was able to delete some more tables. I've seen on Stack Overflow ways to get all foreign keys linked to a certain table but that's way too time consuming unless I script it all (which is doable in the case there is no other option)

Database is 4.1 so I can't use DROP DATABASE

Ideas?

Why did you choose the answer as selected which doesnot even provide the solution to your question?

Robert Pounder

This might be useful to someone ending up here from a search. Make sure you're trying to drop a table and not a view.

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

SET foreign_key_checks = 0 is to set foreign key checks to off and then SET foreign_key_checks = 1 is to set foreign key checks back on. While the checks are off the tables can be dropped, the checks are then turned back on to keep the integrity of the table structure.

This is a better correct answer. Solves the problem whether deleting all tables, or just a few. Terrific!

@PAT Thank you so much ,It worked . though it could not work in Mysql Query browser . You saved my day .

worked perfectly, I used this because I can't drop the entire database

I agree that this is normally the right solution but @johnnyArt, who asked the question, specifically excluded this as a viable option because it didn't work for him. So it seems like this is not the correct answer for the original question, is it?

This answer is apt, correct and helped me resolve my frustration, However, it is important to note that there are two foreign_key_checks variables: a global variable and a local (per session) variable. The command SET foreign_key_checks modifies the session variable while SET GLOBAL foreign_key_checks modify the global variable. Alternatively, you can use ALTER TABLE table_name DISABLE KEYS to disable FK temporarily. Enable it back using ALTER TABLE table_name ENABLE KEYS - tableplus.com/blog/2018/08/…

Ali Azhar

If you are using phpmyadmin then this feature is already there.

Select the tables you want to drop

From the dropdown at the bottom of tables list, select drop

A new page will be opened having checkbox at the bottom saying "Foreign key check", uncheck it.

Confirm the deletion by accepting "yes".

srinivas

https://i.stack.imgur.com/7xXAJ.jpg

This is a duplicate of the highest voted answer, which was posted four years prior.

MindStalker

Drop database exist in all versions of MySQL. But if you want to keep the table structure, here is an idea

mysqldump --no-data --add-drop-database --add-drop-table -hHOSTNAME -uUSERNAME -p > dump.sql

This is a program, not a mysql command

Then, log into mysql and

source dump.sql;

Sanjay

Simple solution to drop all the table at once from terminal.

This involved few steps inside your mysql shell (not a one step solution though), this worked me and saved my day.

Worked for Server version: 5.6.38 MySQL Community Server (GPL)

Steps I followed:

 1. generate drop query using concat and group_concat.
 2. use database
 3. turn off / disable foreign key constraint check (SET FOREIGN_KEY_CHECKS = 0;), 
 4. copy the query generated from step 1
 5. re enable foreign key constraint check (SET FOREIGN_KEY_CHECKS = 1;)
 6. run show table

MySQL shell

$ mysql -u root -p
Enter password: ****** (your mysql root password)
mysql> SYSTEM CLEAR;
mysql> SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name SEPARATOR '`, `'), '`;') AS dropquery FROM information_schema.tables WHERE table_schema = 'emall_duplicate';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dropquery                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE emall_duplicate;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0;                                                                                                                                                   Query OK, 0 rows affected (0.00 sec)

// copy and paste generated query from step 1
mysql> DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`;
Query OK, 0 rows affected (0.18 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW tables;
Empty set (0.01 sec)

mysql>

SAR

Table1 {T_Id, T_Name, TT_Id(Nullable)(Foreign key on Table2 to the column TT_ID)}

Table2 {TT_ID,TT_Title}

1- make the foreign Key relation null able on the table1
2- update table1 set TT_ID = null where T_ID = ?
3- delete from table1

now you can have the table1 data delete and table2 data remain.

Otávio Décio

Since you are not interested in keeping any data, drop the entire database and create a new one.

Oh my, I feel so stupid now, I was substituting the word DATABASE with the actual name of the database instead of adding it afterwards, thanks both +1

this is not the answer

It's the correct answer to the question, I don't understand the down votes personally, the OP wanted to delete the database, didn't know how and was creating alternatives. This answer actually answered the original question to the OP that he got lost trying to fix. This is emphasised by the fact the OP accepted this answer.

@RobertPounder that was exactly my objective, and has been since I joined this site. I appreciate your point of view.

@RobertPounder SO is a bit more than just helping OPs, it amounts to a searchable "how to" resource, I landed on this topic because I needed to disable foreign key constraint checks, and Google led me here. I'm happy dropping the database was a good workaround for the OP, but the answer below is actually correct for the question "Force drop mysql bypassing foreign key constraint".

Force drop mysql bypassing foreign key constraint

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US