CODEWITHSUNDEEP
mysqlforeign-keys
sergtk
sergtk

Reputation: 10974

Error "Can't create table..." on adding FOREIGN KEY

I have created the table by script:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS `Table1`;
CREATE TABLE IF NOT EXISTS `Table1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parentId` bigint(20) DEFAULT NULL,
  `name` varchar(1024) NOT NULL,
  `description` varchar(16384) NOT NULL DEFAULT '',
  `imageId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(255)),
  KEY `parentId` (`parentId`),
  KEY `imageId` (`imageId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;


INSERT INTO `Table1` (`id`, `parentId`, `name`, `description`, `imageId`) VALUES
(0, NULL, 'name1', '', NULL),
(12, 0, 'name2', '', NULL);

Then I try to add foreign key:

ALTER TABLE `Table1`
  ADD CONSTRAINT `Table1_ibfk_2` 
    FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`);

And obtain the following error:

ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150)

What is wrong?

I run 

SHOW ENGINE INNODB STATUS;

There LATEST FOREIGN KEY ERROR follows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110504 22:06:55 Error in foreign key constraint of table sandbox/#sql-c28_61:

    FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`):
Cannot resolve table name close to:
 (`id`)
------------

But it does not help me to realize what is wrong.

I use Windows Vista, MySql 5.5.11

UPDATE:

The issue appears when I upgrade from MySql 5.0.67.

Upvotes: 8

Views: 7744

Answers (5)

tomazahlin
tomazahlin

Reputation: 2167

I had the same exception:

[PDOException]                                                                                
  SQLSTATE[HY000]: General error: 1005 Can't create table 'service.#sql-4851_c07' (errno: 150)

The problem I had was, the field I wanted to make foreign key on was not allowed to be NULL :) After I figured out I had to make it allowed to be null, everything started working.

Of course in the original question, that is not the case, I just wanted to let others know of a possible solution.

Upvotes: 1

Krzysztof Madejski
Krzysztof Madejski

Reputation: 8027

In my case it was due to the fact that the field that was a foreign key field had a too long name, ie. foreign key (some_other_table_with_long_name_id). Try sth shorter. Error message is a bit misleading in that case.

Also field definitions have to be the same (watch out for unsigned subtype).

Upvotes: 1

sergtk
sergtk

Reputation: 10974

Found workaround.

Table name should be in lower case after REFERENCES.

ALTER TABLE `Table1`
  ADD CONSTRAINT `Table1_ibfk_2` 
    FOREIGN KEY (`parentId`) REFERENCES `table1` (`id`);

Looks like bug in MySql 5.5.11.

FI. phpmyadmin can't add foreign key.

Export also produces lower case table name.

MySql 5.1.56 works as expected.

Upvotes: 1

Johan
Johan

Reputation: 76753

Background

ERROR 1005 (HY000): Can't create table 'sandbox.#sql-c28_4c' (errno: 150)

When you command MySQL to ALTER TABLE it really does the following steps:

  1. copy the data from the existing table to a new temporary table.
  2. Alter the structure of the new temporary table
  3. delete the old table
  4. rename the temporary table to the old_table_name.

Problem
Your request is failing in step 1.
Note that tablenames link to files.
On linux tablenames are case sensitive

On Windows they are not case sensitive.

Answer
Since you are obviously running linux, the case of the table you are REFERENCES to needs to be the same case as in the definition of that table, probably all lower case.
You are running Windows, so case sensitivity should not be an issue, maybe it's the tool you are using, I've had these issue as well, and using all lowercase for tablenames everywhere solved my issues.

Proper answer
Set the system variabele 

lower_case_table_names=1

To get rid of this issue.

Note that if you have lower_case_table_names=2 on Windows, your Windoze box turns into a case sensitive Linux box as far as MySQL is concerned!

Link
http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

Upvotes: 5

John Kane
John Kane

Reputation: 4453

I think your declaration of your foreign key may be wrong, depending on what you were really after. This seems to be saying that 'parentID' from 'Table1' will reference the 'id' field from 'Table1'.

ALTER TABLE `Table1`
  ADD CONSTRAINT `Table1_ibfk_2` 
  FOREIGN KEY (`parentId`) REFERENCES `Table1` (`id`);

Maybe try this:

ALTER TABLE `Table1`
  ADD CONSTRAINT `Table1_ibfk_2` 
  FOREIGN KEY (`id`) REFERENCES Parent(`parentId`);

Upvotes: 0

Related Questions