MySQL and Foreign Keys

Posted by ads on Friday, 2006-06-30
Posted in [Other-Dbs]

If you ever try to use Foreign Keys in MySQL (of course, only the InnoDB engine supports this), don’t forget, that the columns in both tables must have exactly the same definition.

If one column is bigint, the other must also 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. Starting with MySQL 4.1.something does create the index for you, if necessary. 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?

Categories: [Other-Dbs]
Tags: [Innodb] [Mysql]