Skip to content

Mysql and Foreign Keys

If you ever try to use Foreign Keys in Mysql (of course, only the InnoDB tabletype allows this), don't forget, that the columns in both tables must have exactly the same definition.

If one column is bigint, the other also must be bigint. If one column is unsigned, the other table also needs to be an unsigned column. If you forget about this, mysql will give you a mystical error message (#1005 cannot create table), but it does not tell you, why it can't create the table.

Older versions of mysql (3.x, 4.0.x) also need an index on both columns in both tables, mysql since 4.1.something does create the index for you, if needed. This has the drawback, that you cannot reference char, varchar or text columns in mysql.

How can anyone get real work done with this *censored*?