"Ian Davies" <ia********@virgin.net> wrote in message
news:Qd*******************@newsfe5-gui.ntli.net...
I have created a database with about 17 tables. I have been creating
foreign
keys some of which have worked but when creating others I get the message
below
*************************
1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to errno 150, table
creation failed because a foreign key constraint was not correctly formed.
***************************
I have checked to see If there are any obvious differences between the
tables that allowed the foreign keys to create and those that wouldnt. But
I
could not find any differences
Here's a MySQL forum thread that mentions this error:
http://forums.mysql.com/read.php?22,...3805#msg-43805
There are restrictions on foreign keys mentioned in that thread:
- Both tables must be InnoDB tables.
- The foreign key field must have an index on it.
- The foreign key field and the referenced field must be of the same
datatype.
- If using integers, the fields must be UNSIGNED integers (this restriction
is a surprise to me!)
See also
http://dev.mysql.com/doc/refman/5.0/...nstraints.html.
The referenced field must also have an index on it. I think the standard is
that the referenced field must have a UNIQUE index, but this doesn't seem to
be a strict requirement for InnoDB.
The docs also talks about the 1005 error briefly, and says, "You can use
SHOW ENGINE INNODB STATUS to display a detailed explanation of the most
recent InnoDB foreign key error in the server."
I assume you'd do this immediately after getting the error message, to make
sure it's reporting about the most recent error.
Regards,
Bill K.
