sw3432
sw3432

Reputation:

MySQL error 1005 on table create

My table definition:

CREATE TABLE x (
    a INT NOT NULL,
    FOREIGN KEY (a) REFERENCES a (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

which produces the following error:

ERROR 1005 (HY000): Can't create table './abc/x.frm' (errno: 150)

What does this mean?

Upvotes: 0

Views: 2922

Answers (5)

Patrick Böker
Patrick Böker

Reputation: 3243

This error is also thrown when the referenced table does not use the InnoDB storage format.

Upvotes: 1

Ivo Havener
Ivo Havener

Reputation: 51

This also happens if there are duplicate foreign key names.

Say for example that you have two very similar tables with long names. To "save" time, you copy and paste the foreign key names from the first table's CREATE, then get distracted and fail to update the part of the table name that is different. They happen to share a foreign key to another table, resulting in one or more identical foreign key names.

Have you noticed that bashing your head against the monitor isn't as satisfying with an LCD?

Upvotes: 1

nathan
nathan

Reputation: 4732

use perror with the "errno" error number to get the error message (perror 150):

MySQL error code 150: Foreign key constraint is incorrectly formed

Upvotes: 3

Galen
Galen

Reputation: 30170

Maybe this is why

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

from: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Upvotes: 4

Byron Whitlock
Byron Whitlock

Reputation: 53871

Probably that a.id doesn't exist.

Upvotes: 1

Related Questions