First, launch the MySQL workbench and log in to the MySQL Server. Second, right-click the database that you want to remove, for example, testdb2 and choose the Drop Schema... option. Third, MySQL Workbench displays a dialog to confirm the deletion.
You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
Dropping Tables/Schemas/Databases For example, when dropping a database, if the database contains a primary/unique key which is referenced by a foreign key from another database, the referencing foreign keys are also dropped.
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.
If you are using phpmyadmin then this feature is already there.
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 -
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;
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With