![]() ![]() This is a very simple example, but hopefully you can see how this will help when your database schema has 10, 20, or more tables with MySQL foreign key relationships. That's all you have to do for this MySQL DROP TABLE foreign key technique just set the MySQL FOREIGN_KEY_CHECKS variable before and after all your drop statements, and you're DROP TABLE statements will work just fine, regardless of foreign keys. MySQL DROP TABLE foreign keys - The better wayīut fortunately, with the MySQL FOREIGN_KEY_CHECKS variable, you don't have to worry about the order of your DROP TABLE statements at all, and you can write them in any order you like - even the exact opposite - like this: That was the only order in which the MySQL tables could be dropped. The way I used to do this was drop these tables in this exact order: First, imagine that you have database tables named customers, orders, order_details, and orders has a foreign key back to customers, and order_details has a foreign key back to orders. Here's a simple example of how this works. MySQL DROP TABLE foreign keys simple example - The old way After that, you run your MySQL CREATE TABLE statements, then turn the foreign_key_check back on. ![]() MySQL essentially turns off the foreign key checks, letting you drop your tables in any order desired. In short, MySQL has a variable named FOREIGN_KEY_CHECKS that you can set just before and just after all your MySQL DROP TABLE statements, and with this variable set, the order of your drop statements doesn't matter. Until I knew how to properly approach this problem I used to write my DDL (the MySQL drop table statements) in a very specific order - which was very time-consuming - but fortunately there's a much easier solution to this problem with MySQL. When you do this, you'll often run into problems dropping the old database tables because of the foreign key relationships between the tables.įor instance, if an orders table has a foreign key link back to a customers table, you can't drop the customers table until you first drop the orders table - and any other database table that has a foreign key relationship back to the customers table. With MySQL - and any other database - any time you want to rebuild your database schema, the first thing you normally do is drop all your old database tables with MySQL drop table statements, and then rebuild them with MySQL create table statements. Is there something I can do to work around this DROP TABLE foreign keys problem? Solution So after running the SOURCE command the two DROP statements are executed and the school.tmp_student_activity1_association and the school.tmp_student_activity2_association tables will be removed.MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts they keep failing because of all the foreign keys. Create tables within databases How to view tables in MySQL How to change tables with the ALTER TABLE command Drop tables Drop databases Conclusion. The SQL script drop_tmp_school_tables.sql will be: DROP TABLE school.tmp_student_activity1_association ĭROP TABLE school.tmp_student_activity2_association INTO outfile '/tmp/drop_tmp_school_tables.sql' The code will look like this: SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ')įROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'tmp%' Let’s consider we have a database school having the following tables:Īnd we want to remove all the tables starting with tmp. Then the output file is taken by the SOURCE command that executes its (SQL) content. The SELECT statement builds another statement, DROP, that is written to an output file. The following SQL statement deletes all rows in the 'Customers' table, without deleting the table: Example DELETE FROM Customers Test Yourself With Exercises Exercise: Delete all the records from the Customers table where the Country value is 'Norway'. SOURCE /tmp/drop_my_tables.sql Explanation WHERE TABLE_NAME LIKE 'prefix%' AND TABLE_SCHEMA = 'my_database' Syntax DROP TABLE tablename Note: Be careful before dropping a table. Solution SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ') The DROP TABLE statement is used to drop an existing table in a database. ![]()
0 Comments
Leave a Reply. |