![]() 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 If there was also a permanent table called customers, this DROP TABLE statement would not delete it because TEMPORARY is specified.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. This DROP TABLE example will only delete the temporary table called customers. DROP TEMPORARY TABLE IF EXISTS customers Drop Temporary Tableįinally, let's look at an example that shows how to use the DROP TABLE statement to drop a temporary table. This example would delete the customers and suppliers tables and would not raise an error if one of the tables didn't exist. If we were worried that one of the tables doesn't exist and we don't want to raise an error, we could modify our DROP TABLE statement as follows: DROP TABLE IF EXISTS customers, suppliers This DROP TABLE statement example would delete two tables - customers and suppliers. Let's look at an example where we want to drop more than one table using the DROP TABLE statement:įor example: DROP TABLE customers, suppliers This DROP TABLE example would delete the table called customers. Drop One Tableįirst, let's look at a simple DROP TABLE example that shows how to use the DROP TABLE statement to drop one table in MySQL. Let's look at an example that shows how to drop a table using the MySQL DROP TABLE statement. ![]() It has no impact or effect on the DROP TABLE statement but is included in the syntax to make porting the tables to different databases easier. If specified, the DROP TABLE statement will not raise an error if one of the tables does not exist. table_name1, table_name2 The tables to remove from the database, if removing more than one table in the DROP TABLE statement. table_name The name of the table to remove from the database. It specifies that only temporary tables should be dropped by the DROP TABLE statement. Parameters or Arguments TEMPORARY Optional. However, the full syntax for the MySQL DROP TABLE statement is: DROP TABLE In its simplest form, the syntax for the DROP TABLE statement in MySQL is: DROP TABLE table_name
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |