creating foreign fields

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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


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

I am stumped
Can anyone help

Re: creating foreign fields

Quoted text here. Click to load it

Here's a MySQL forum thread that mentions this error:,19755,43805#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
- If using integers, the fields must be UNSIGNED integers (this restriction
is a surprise to me!)

See also
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.

Bill K.

Re: creating foreign fields

Thanks Bill
The problem was one of your suggestions

The foreign key field and the referenced field were not always of the same
datatype. When corrected the foreing keys created ok


Quoted text here. Click to load it
Quoted text here. Click to load it

Site Timeline