Can’t create foreign keys but don’t know why
Easily overlooked problem creating foreign key relationships in mysql
Just a very quick one, really as a aide-mémoire for when this inevitably happens again and leaves me scratching my head.
So, if you’re creating a foreign key relationships between two tables in your mysql / mariadb database, and you get errors along the lines of:
|
1 |
errno: 150 "Foreign key constraint is incorrectly formed" |
or
|
1 |
Failed to add the foreign key constaint. Missing index for constraint 'BLAH' in the referenced table 'whatever' |
and you’ve checked that your statement is correctly constructed and otherwise correct, check that the tables in question are the same collation using show create table TABLENAME
It is easy to miss, but foreign keys can only exist between tables of the same table collation and type. So, if they’re different, you’ll need to do an alter table, e.g.
|
1 |
alter table TABLENAME convert to character set utf8 collate utf8_unicode_ci; |
Hope this helps someone!
Leave a Reply