Creating A Failed Foreign Key Relationship

[scottah]

after attempting to create the failed relationship, sqlyog pops up with a helpful possible reason list.

Just wondering if reason #2 is now obsolete..?

“2) No index was found on selected columns. You need to explicitly create index on the selected columns”

Quote:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The index on the foreign key is created automatically. This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.

http://mysql.com/doc/refman/5.0/en/innodb-…onstraints.html

[peterlaursen]

Well yes .. and .. no

Wiht MySQL 5 you still need to have created an index on the referenced column (in the other table) in advance. But the index on the FK-column itself is created by MySQL itself if it is not there in advance.

So when you try to reference a column in another table that has no index, building the FK would fail and an error msg. should show. But it could be more specific.

But actually SQLyog DOES NOT prevent building the FK if the index on the FK-column is not there!

[scottah]

Ahh k. Fair enough!

[Author]

Posts