![]() 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 InnoDB permits a foreign key to reference any column or group ofĬolumns.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. Index_name, if given, is used as described previously. This index might be silently dropped later, if you createĪnother index that can be used to enforce the foreign key constraint. Index is created on the referencing table automatically if it does notĮxist. Referencing table, there must be an index where the foreign keyĬolumns are listed as the first columns in the same order. MySQL requires indexes on foreign keys and referenced keys so thatįoreign key checks can be fast and not require a table scan. The first chunk alludes to a Helper index that will be created if necessary in the referencing (child) table if so necessary. This concept is described in the 2nd chunk below. That parent key does not need to be PRIMARY or even UNIQUE. In addition, the referenced (parent) table must have a left-most key available for fast lookup (verification). Nonbinary (character) string columns, the character set and collation The length of string types need not be the same. The size and sign of integer types must be Such as ALTER TABLE `facility` ADD CONSTRAINT `fkZipcode`įOREIGN KEY (`zipcode`) REFERENCES `allzips`(`zipcode`) įrom the MySQL manual page entitled Using FOREIGN KEY Constraints:Ĭorresponding columns in the foreign key and the referenced key must Whether you are trying to establish a Foreign Key constraint on table creation or with ALTER TABLEįOREIGN KEY (index_col_name.) ![]() Pulling the discussion up to more general cases. A trickier one causing a 1215 error due to column-level collation mismatches can be seen in This answer. )COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB CREATE TABLE `user_details` (ĬONSTRAINT `FK_user_details` FOREIGN KEY (`ClientID`) REFERENCES `user_details` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE The point being, if collation is different, it won't work. Note I shrunk the size due to error 1071 on sizing for varchar 255 with that collation and then auto chosen charset. Why do I show this? Because the OP didn't. The below will fail because the collation is different.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |