Force drop mysql bypassing foreign key constraint

Question

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? — Sanjay, Mar 05 '19 at 17:24

score 472 · Answer 1 · edited Feb 07 '17 at 15:03

472

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.

edited Feb 07 '17 at 15:03

Robert Pounder

1,490
1
14
29

answered May 20 '10 at 13:13

PAT

4,729
1
15
2

6

This is a better correct answer. Solves the problem whether deleting all tables, or just a few. Terrific! – Luke Stevenson Apr 22 '13 at 04:15
@PAT Thank you so much ,It worked . though it could not work in Mysql Query browser . You saved my day . – MaNn Jul 17 '15 at 11:27
worked perfectly, I used this because I can't drop the entire database – Pablo Pazos Jul 27 '15 at 07:28
1

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? – David Jan 24 '17 at 19:12
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 - https://tableplus.com/blog/2018/08/mysql-how-to-temporarily-disable-foreign-key-constraints.html – Dimgba Kalu Aug 20 '19 at 08:47

score 19 · Answer 2 · answered Jan 09 '15 at 14:44

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".

score 4 · Answer 3 · answered Jul 02 '14 at 15:46

4

You can use the following steps, its worked for me to drop table with constraint,solution already explained in the above comment, i just added screen shot for that - enter image description here

answered Jul 02 '14 at 15:46

srinivas

474
7
14

1

This is a duplicate of the highest voted answer, which was posted four years prior. – chb Jul 30 '19 at 23:39

score 3 · Answer 4 · answered Feb 20 '10 at 00:55

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;

score 3 · Answer 5 · answered Mar 05 '19 at 17:29

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>

score 1 · Answer 6 · answered Apr 11 '21 at 08:10

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.

score -50 · Accepted Answer · answered Feb 19 '10 at 23:53

-50

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

answered Feb 19 '10 at 23:53

Otávio Décio

73,752
17
161
228

Forgot to say the database is not mysql 5.x but 4.x which means I'm not able to use that command – johnnyArt Feb 19 '10 at 23:59
3

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 – johnnyArt Feb 20 '10 at 00:53
1

what in case you don't have priv to drop DB but only to drop tables? – Greg Mar 08 '13 at 10:33
19

this is not the answer – Freelancer Jul 16 '14 at 16:30
7

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.** – Robert Pounder Feb 07 '17 at 13:48
6

@RobertPounder that was exactly my objective, and has been since I joined this site. I appreciate your point of view. – Otávio Décio Feb 07 '17 at 13:57
3

@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". – Val Redchenko Dec 18 '17 at 12:27
OP did **not** ask to "delete the database" as is claimed. OP asked to "delete all tables from a database except one". How can one claim that this is the correct answer? – jlh Mar 10 '20 at 12:45

Force drop mysql bypassing foreign key constraint

7 Answers7

Linked