473,394 Members | 1,932 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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
Jan 22 '06 #1
2 2498
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Justin | last post by:
I am extremely new at SQL Server2000 and t-sql and I'm looking to create a simple trigger. For explanation sake, let's say I have 3 columns in one table ... Col_1, Col_2 and Col_3. The data type...
8
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
3
by: CAD Fiend | last post by:
Hello, I have a land development project that has a many-to-many relationship. I have ONE question regarding table structures, and ONE question on how to make a Form with two Subforms, below. ...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
0
by: ckiraly | last post by:
Greetings everyone - I am new to MSSQL 2005, and have started a database design project for my company. The issue I have is in a specific instance of foreign key creation. Here is the whole...
2
by: astolpho | last post by:
I am using a slightly outdated reference book on J2EE programming. It gives 2 methods of creating a database used in its casestudies. The first is an ANT script that gives the following output: ...
9
by: sheenaa | last post by:
Hello frdz, I m working with SQL SERVER 2005. My problem is with the creation of foreign key for some table thru which i m not able to insert the data. This table are samples i have included...
2
by: jarea | last post by:
I have read quite a bit about this error but I have yet to find the solution to my problem. I am trying to execute the following mysql statement: alter table line_items add...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.