473,327 Members | 1,896 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,327 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 2495
"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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.