By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,872 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,872 IT Pros & Developers. It's quick & easy.

creating foreign fields

P: n/a
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
Jan 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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.
Jan 23 '06 #2

P: n/a
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" <bi**@karwin.com> wrote in message
news:dr*********@enews4.newsguy.com...
"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.

Jan 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.