![]() 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 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |