#SQLITESTUDIO FOREIGN KEY NOT WORKING UPDATE#
Everything you do such as update or delete is bound to the rigid relationship. Referential Integrity (RI)įrom now on, you have to respect the relationship. mysql> alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (id) on delete cascade on update cascade Now, we can create the foreign key constraint without errors. mysql> alter table orders modify column customer_id int(11) unsigned not null So next, let's make them equivalent in size and sign by modifying the column customer_id as unsigned. Is it too picky? Sorry, the integer column pair pursuits exactly the same. That's why we cannot create the foreign key constraint. So the final verdict is that they are different. mysql> desc orders Īs you can see above, the column customer_id is not unsigned in the first table orders. Now, let's go deeper to compare the difference between the referencing and referenced column. That's why we got ERROR 1215 (HY000) in the first place. For nonbinary (character) string columns, the character set and collation must be the same. The length of string types need not be the same. The size and sign of integer types must be the same. Corresponding columns in the foreign key and the referenced key must have similar data types.
#SQLITESTUDIO FOREIGN KEY NOT WORKING MANUAL#
I quote one precondition of adding a foreign key constraint from MySQL 8.0 Reference Manual below to sustain my point. But how different is "different"? how same is "same"? Sometimes, they should be exactly the same even in every tiny specification. Same data type of two columns is good to go. Column Definition Mismatchīasically, we can't build referential relationship between two different data types of columns. If you still got "ERROR 1215 (HY000): Cannot add foreign key constraint", you can go on reading this post because things are getting complicated. mysql> alter table customers add index (email) mysql> alter table customers add index customers_email_idx (email) For example, adding an index with an explicit name. The cure is pretty easy, just add indexes on columns that are required to have. So please make sure that you have indexes on the parent and child columns before adding any foreign key constraints. One obvious mistake is that you do not have indexes both on the referenced and referencing columns. We have to find out the root cause by ourselves. It only shows that it failed to create a foreign key constraint, but I don't see any hints or useful information in it.
Maybe the message of ERROR 1215 (HY000) is somewhat shallow. ERROR 1215 (HY000) Cannot add foreign key constraint Rationale and Solutions for Cannot add foreign key constraint
0 kommentar(er)
