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 gt  SET foreign key checks   0  mysql gt  drop table     mysql gt  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

User · Answer

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

User · Answer

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 gt  SYSTEM CLEAR  mysql gt  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 gt  USE emall duplicate  Database changed mysql gt  SET FOREIGN KEY CHECKS   0                                                                                                                                                    Query OK  0 rows affected  0 00 sec      copy and paste generated query from step 1 mysql gt  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 gt  SET FOREIGN KEY CHECKS   1  Query OK  0 rows affected  0 00 sec   mysql gt  SHOW tables  Empty set  0 01 sec   mysql gt

User · Answer

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 -

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Force drop mysql bypassing foreign key constraint

Examples related to mysql

Examples related to foreign-keys

Examples related to sql-drop