Why am I getting "error code 1005 can t create c errno 150 mysql "?

Question

EDIT # 2:- After following Spencer 7593's answer, i.e. creating the table without the foreign keys, and then adding them using the alter table statement, the table was created successfully and the foreign keys were added successfully.

But now I wonder why? Why does it work when we create the table and then add the foreign key constraints using alter table statement, and not when we do it all in the create table statement?

I am creating the following tables in my database. The first two were created successfully but the third one is giving this error.

I checked my database for the points in this answer which I could understand. Please tell me why I am getting this error.

CREATE TABLE a (
    a_id INT(255) NOT NULL AUTO_INCREMENT,
    name VARCHAR(5000),
    code VARCHAR(5000),
    PRIMARY KEY (a_id)
);

CREATE TABLE b (
    b_id INT(255) NOT NULL AUTO_INCREMENT,
    b_name VARCHAR(5000),
    PRIMARY KEY (b_id)
);

CREATE TABLE c ( -- error code 1005 can t create c errno 150 mysql
    a_id INT(255), -- fk
    b_id INT(255), -- fk
    PRIMARY KEY (a_id, b_id),
    FOREIGN KEY (a_id)
        REFERENCES a (a_id),
    FOREIGN KEY (b_id)
        REFERENCES b(b_id)
);

EDIT:-

I executed just these three statements separately (after changing the size of all INT types to 11 - thanks to the comment by Michael Berkowski), and to my surprise, it did create the tables, but gave this log (if I am using the right word):

3   4   01:03:49    CREATE TABLE a (
     a_id INT(11) NOT NULL AUTO_INCREMENT,
     name VARCHAR(5000),
     code VARCHAR(5000),
     PRIMARY KEY (a_id)
 )  0 row(s) affected   0.156 sec
3   5   01:03:54    CREATE TABLE b (
     b_id INT(11) NOT NULL AUTO_INCREMENT,
     b_name VARCHAR(5000),
     PRIMARY KEY (b_id)
 )  0 row(s) affected   0.234 sec
3   6   01:03:58    CREATE TABLE c ( -- error code 1005 can t create c errno 150 mysql
     a_id INT(11), -- fk
     b_id INT(11), -- fk
     PRIMARY KEY (a_id, b_id),
     FOREIGN KEY (a_id)
         REFERENCES a (a_id),
     FOREIGN KEY (b_id)
         REFERENCES b(b_id)
 )  0 row(s) affected   0.203 sec

That is all the three tables are created, but an error is returned at the creation of the third table.

EDIT # 3:-

Output from SHOW CREATE TABLE a, and SHOW CREATE TABLE b:-

CREATE TABLE `a` (
   `a_id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(5000) DEFAULT NULL,
   `code` varchar(5000) DEFAULT NULL,
   PRIMARY KEY (`a_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b` (
   `b_id` int(11) NOT NULL AUTO_INCREMENT,
   `b_name` varchar(5000) DEFAULT NULL,
   PRIMARY KEY (`b_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

If you are running into errors, please post the exact statements you are using. These look like abbreviations, and indeed they work correctly: http://sqlfiddle.com/#!2/10d9b — Michael Berkowski, Apr 21 '14 at 19:39
But separately, `INT(255)` is suspect. What kind of data do you want those columns to contain? (see http://stackoverflow.com/questions/7171312/what-is-the-max-number-if-i-store-int255-in-mysql) — Michael Berkowski, Apr 21 '14 at 19:41
possible duplicate of [Error Code: 1005. Can't create table '...' (errno: 150)](http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150) — Mureinik, Apr 21 '14 at 19:42
@Mureinik That is why I gave a link to that question and mentioned that I checked what I could understand, and did not find anything wrong. — Solace, Apr 21 '14 at 19:58
The `(255)` is not a length specifier, it's only a hint for display width. It has no effect and is not relevant to this question. — Bill Karwin, Apr 21 '14 at 20:10
@MichaelBerkowski Correctly... partly correctly because I executed them in a separate database, they created all the three tables but gave an error on the third statement. I have edited my question. — Solace, Apr 21 '14 at 20:11
After successful creation of `A,B`, can you post the output from `SHOW CREATE TABLE A` and `SHOW CREATE TABLE B`? Something about how they were ultimately created is hindering the key definitions of `C`. — Michael Berkowski, Apr 21 '14 at 20:15
CAN SOMEBODY TELL ME WHY I GOT A NEGATIVE RANKING ON THIS QUESTION.. — Solace, Apr 21 '14 at 21:13
@hjpotter92 Referring to the second point on [the answer by Piskvor here](http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work), I think indices will not be useful for my small database. — Solace, Apr 22 '14 at 10:12
@Zarah Since you were able to add the FK's after creation and those of use testing for you here had no issues, it seems like it could have been an undiagnosable problem in the client, like `A,B` weren't really fully created before `C` (which is necessary for the FKs) I don't know what else. It's really difficult to say. Don't worry about the downvote. You showed your research but some will ignore or not notice it. — Michael Berkowski, Apr 22 '14 at 10:47
@MichaelBerkowski Thank you. This error is really confusing for me, since today I have created another database (even dropped the previous one) and trying to execute the same code again, and it is giving the same error on other tables, which were created successfully yesterday. Thank you for your recognition that I did and showed my research. — Solace, Apr 22 '14 at 11:07

score 1 · Answer 1 · answered Apr 21 '14 at 20:08

1

The most usual cause of this error is that the foreign key column definition does not match the definition of the referenced column. Another reason for the error is that the column referenced does not exist in the referenced table.

But the OP CREATE TABLE statements don't have that problem; those work with both MyISAM and InnoDB as default storage engine. (Not sure about any other storage engines, we don't see a storage engine specified, and I'm just guessing that the default is either InnoDB or MyISAM.)

SHOW VARIABLES LIKE 'default_storage_engine'

To debug this, try running the create table c WITHOUT the foreign key constraint definitions, and verify the table is created successfully. (The length modifier value of 255 strikes us as odd. Why not allow it to default to 11?)

CREATE TABLE c 
( a_id  INT COMMENT 'FK ref a.a_id'
, b_id  INT COMMENT 'FK ref b.b_id'
, PRIMARY KEY (a_id, b_id)
);

Then try adding the foreign key constraints:

ALTER TABLE c ADD CONSTRAINT FK_c_a
FOREIGN KEY (a_id) REFERENCES a (a_id);

Ensure that column a_id exists in a, and that the definition matches. (Is it possible that the column is named id (rather than a_id) in table a?)

answered Apr 21 '14 at 20:08

spencer7593

106,611
15
112
140

The first snippet (i.e. create table without the foreign key statements) created the table successfully. Can you please clarify what FK_c_a is? – Solace Apr 21 '14 at 20:53
@Zarah: the `CONSTRAINT name` is optional. In that example, `FK_c_a` is the name I assign to the constraint. The convention I follow is `FK_` + table_name + referenced_table_name, when there is more than one FK of that pattern, I typically include a *role* in the constraint name. – spencer7593 Apr 21 '14 at 21:46
This worked perfectly fine. Thank you so very much. The table was created and the foreign keys were added. But I wonder why didn't it work in the CREATE TABLE statement - updating my question! – Solace Apr 22 '14 at 08:00
@Zarah: I don't have a good answer for why that third create table statement throws an error when the statement includes the foreign key definitions. (I'm not able to reproduce the error, the `CREATE TABLE` statements in your question work fine for me, I tested on both MySQL 5.1 and MySQL 5.5 (on Linux). My next recommendation would be run these statements from the MySQL command line client, narrow it down to a problem on the MySQL server, to remove the possibility it's due to the client and driver; verify that this is actually a reproducible problem on the MySQL server. – spencer7593 Apr 23 '14 at 15:35

Why am I getting "error code 1005 can t create c errno 150 mysql "?

1 Answers1

Linked