creating foreign fields

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

I am stumped
Can anyone help

Re: creating foreign fields

"Ian Davies" wrote in message
news:QdPAf.57907$zt1.12257@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,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
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/en/innodb-foreign-key-co 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.

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

Ian


"Bill Karwin" wrote in message
news:dr394r026b5@enews4.newsguy.com...
> "Ian Davies" wrote in message
> news:QdPAf.57907$zt1.12257@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,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
> 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/en/innodb-foreign-key-co 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.
>
>