MySQL Creating tables with Foreign Keys giving errno: 150
I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.
Here is the SQL for all 3 tables:
CREATE TABLE role_groups (
`role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
`name` varchar(20),
`description` varchar(200),
PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `roles` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
`description` varchar(200),
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;
create table role_map (
`role_map_id` int not null `auto_increment`,
`role_id` int not null,
`role_group_id` int not null,
primary key(`role_map_id`),
foreign key(`role_id`) references roles(`role_id`),
foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;
Any help would be greatly appreciated.
mysql foreign-keys mysql-error-150
add a comment |
I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.
Here is the SQL for all 3 tables:
CREATE TABLE role_groups (
`role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
`name` varchar(20),
`description` varchar(200),
PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `roles` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
`description` varchar(200),
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;
create table role_map (
`role_map_id` int not null `auto_increment`,
`role_id` int not null,
`role_group_id` int not null,
primary key(`role_map_id`),
foreign key(`role_id`) references roles(`role_id`),
foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;
Any help would be greatly appreciated.
mysql foreign-keys mysql-error-150
1
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
4
What's with the back-ticks aroundauto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.
– Bill Karwin
Sep 21 '09 at 23:39
add a comment |
I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.
Here is the SQL for all 3 tables:
CREATE TABLE role_groups (
`role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
`name` varchar(20),
`description` varchar(200),
PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `roles` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
`description` varchar(200),
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;
create table role_map (
`role_map_id` int not null `auto_increment`,
`role_id` int not null,
`role_group_id` int not null,
primary key(`role_map_id`),
foreign key(`role_id`) references roles(`role_id`),
foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;
Any help would be greatly appreciated.
mysql foreign-keys mysql-error-150
I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.
Here is the SQL for all 3 tables:
CREATE TABLE role_groups (
`role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
`name` varchar(20),
`description` varchar(200),
PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `roles` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50),
`description` varchar(200),
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;
create table role_map (
`role_map_id` int not null `auto_increment`,
`role_id` int not null,
`role_group_id` int not null,
primary key(`role_map_id`),
foreign key(`role_id`) references roles(`role_id`),
foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;
Any help would be greatly appreciated.
mysql foreign-keys mysql-error-150
mysql foreign-keys mysql-error-150
edited Dec 28 '17 at 19:03
Bill Karwin
374k61513667
374k61513667
asked Sep 21 '09 at 22:55
user176842
1
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
4
What's with the back-ticks aroundauto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.
– Bill Karwin
Sep 21 '09 at 23:39
add a comment |
1
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
4
What's with the back-ticks aroundauto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.
– Bill Karwin
Sep 21 '09 at 23:39
1
1
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
4
4
What's with the back-ticks around
auto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.– Bill Karwin
Sep 21 '09 at 23:39
What's with the back-ticks around
auto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.– Bill Karwin
Sep 21 '09 at 23:39
add a comment |
19 Answers
19
active
oldest
votes
I had the same problem with ALTER TABLE ADD FOREIGN KEY
.
After an hour, I found that these conditions must be satisfied to not get error 150:
The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with
ALTER TABLE
.The two tables must both support foreign key constraints, i.e.
ENGINE=InnoDB
. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is
PRIMARY KEY
orUNIQUE KEY
.The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK
REFERENCES Parent(a,b,c)
then the Parent's PK must not be defined on columns in order(a,c,b)
.
The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is
UNSIGNED
, be sure to defineUNSIGNED
for the corresponding column in the Child table field.
Exception: length of strings may be different. For example,
VARCHAR(10)
can referenceVARCHAR(20)
or vice versa.
Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).
If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.
Neither the Parent table nor the Child table can be a
TEMPORARY
table.Neither the Parent table nor the Child table can be a
PARTITIONED
table.If you declare a FK with the
ON DELETE SET NULL
option, then the FK column(s) must be nullable.If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.
Hope this helps.
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
This includes things likeint(11) unsigned NOT NULL
vsint(11) NOT NULL
.
– Glen Solsberry
May 1 '13 at 20:37
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
|
show 12 more comments
MySQL’s generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:
You can get the actual error message by running SHOW ENGINE INNODB STATUS;
and then looking for LATEST FOREIGN KEY ERROR
in the output.
For example, this attempt to create a foreign key constraint:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
fails with the error Can't create table 'test.t2' (errno: 150)
. That doesn’t tell anyone anything useful other than that it’s a foreign key problem. But run SHOW ENGINE INNODB STATUS;
and it will say:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
It says that the problem is it can’t find an index. SHOW INDEX FROM t1
shows that there aren’t any indexes at all for table t1
. Fix that by, say, defining a primary key on t1
, and the foreign key constraint will be created successfully.
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.
– jatrim
Aug 29 '14 at 0:09
add a comment |
Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.
Often, the 'unsigned' property on an ID column will catch you out.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
add a comment |
What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.
If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.
You may want to check out the manual entry too:
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. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
— MySQL 5.1 reference manual.
add a comment |
For people who are viewing this thread with the same problem:
There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:
MySQL Foreign Key Errors and Errno 150
add a comment |
For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.
add a comment |
Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEY
could be not PRIMARY KEY
. Te answer which become useful for me is:
A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
add a comment |
As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;
) instead of just an error code.
One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId
use idx_userActionMapping_userId
.
add a comment |
Helpful tip, use SHOW WARNINGS;
after trying your CREATE
query and you will receive the error as well as the more detailed warning:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
So in this case, time to re-create my table!
add a comment |
Please make sure at first that
- you are using InnoDB tables.
- field for FOREIGN KEY has the same type and length (!) as source field.
I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.
add a comment |
This is usually happening when you try to source file into existing database.
Drop all the tables first (or the DB itself).
And then source file with SET foreign_key_checks = 0;
at the beginning and SET foreign_key_checks = 1;
at the end.
add a comment |
I've found another reason this fails... case sensitive table names.
For this table definition
CREATE TABLE user (
userId int PRIMARY KEY AUTO_INCREMENT,
username varchar(30) NOT NULL
) ENGINE=InnoDB;
This table definition works
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;
whereas this one fails
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;
The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out. Hope that helps someone else.
add a comment |
MySQL Workbench 6.3 for Mac OS.
Problem: errno 150 on table X when trying to do Forward Engineering on a DB diagram, 20 out of 21 succeeded, 1 failed. If FKs on table X were deleted, the error moved to a different table that wasn't failing before.
Changed all tables engine to myISAM and it worked just fine.
add a comment |
Also worth checking that you aren't accidentally operating on the wrong database. This error will occur if the foreign table does not exist. Why does MySQL have to be so cryptic?
add a comment |
Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.
add a comment |
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, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned
subtype).
add a comment |
When the foraign key constraint is based on varchar
type, then in addition to the list provided by marv-el
the target column must have an unique constraint.
add a comment |
(Side notes too big for a Comment)
There is no need for an AUTO_INCREMENT
id in a mapping table; get rid of it.
Change the PRIMARY KEY
to (role_id, role_group_id)
(in either order). This will make accesses faster.
Since you probably want to map both directions, also add an INDEX
with those two columns in the opposite order. (There is no need to make it UNIQUE
.)
More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
add a comment |
I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
add a comment |
protected by Kermit Jul 10 '14 at 23:11
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
19 Answers
19
active
oldest
votes
19 Answers
19
active
oldest
votes
active
oldest
votes
active
oldest
votes
I had the same problem with ALTER TABLE ADD FOREIGN KEY
.
After an hour, I found that these conditions must be satisfied to not get error 150:
The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with
ALTER TABLE
.The two tables must both support foreign key constraints, i.e.
ENGINE=InnoDB
. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is
PRIMARY KEY
orUNIQUE KEY
.The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK
REFERENCES Parent(a,b,c)
then the Parent's PK must not be defined on columns in order(a,c,b)
.
The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is
UNSIGNED
, be sure to defineUNSIGNED
for the corresponding column in the Child table field.
Exception: length of strings may be different. For example,
VARCHAR(10)
can referenceVARCHAR(20)
or vice versa.
Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).
If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.
Neither the Parent table nor the Child table can be a
TEMPORARY
table.Neither the Parent table nor the Child table can be a
PARTITIONED
table.If you declare a FK with the
ON DELETE SET NULL
option, then the FK column(s) must be nullable.If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.
Hope this helps.
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
This includes things likeint(11) unsigned NOT NULL
vsint(11) NOT NULL
.
– Glen Solsberry
May 1 '13 at 20:37
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
|
show 12 more comments
I had the same problem with ALTER TABLE ADD FOREIGN KEY
.
After an hour, I found that these conditions must be satisfied to not get error 150:
The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with
ALTER TABLE
.The two tables must both support foreign key constraints, i.e.
ENGINE=InnoDB
. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is
PRIMARY KEY
orUNIQUE KEY
.The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK
REFERENCES Parent(a,b,c)
then the Parent's PK must not be defined on columns in order(a,c,b)
.
The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is
UNSIGNED
, be sure to defineUNSIGNED
for the corresponding column in the Child table field.
Exception: length of strings may be different. For example,
VARCHAR(10)
can referenceVARCHAR(20)
or vice versa.
Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).
If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.
Neither the Parent table nor the Child table can be a
TEMPORARY
table.Neither the Parent table nor the Child table can be a
PARTITIONED
table.If you declare a FK with the
ON DELETE SET NULL
option, then the FK column(s) must be nullable.If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.
Hope this helps.
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
This includes things likeint(11) unsigned NOT NULL
vsint(11) NOT NULL
.
– Glen Solsberry
May 1 '13 at 20:37
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
|
show 12 more comments
I had the same problem with ALTER TABLE ADD FOREIGN KEY
.
After an hour, I found that these conditions must be satisfied to not get error 150:
The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with
ALTER TABLE
.The two tables must both support foreign key constraints, i.e.
ENGINE=InnoDB
. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is
PRIMARY KEY
orUNIQUE KEY
.The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK
REFERENCES Parent(a,b,c)
then the Parent's PK must not be defined on columns in order(a,c,b)
.
The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is
UNSIGNED
, be sure to defineUNSIGNED
for the corresponding column in the Child table field.
Exception: length of strings may be different. For example,
VARCHAR(10)
can referenceVARCHAR(20)
or vice versa.
Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).
If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.
Neither the Parent table nor the Child table can be a
TEMPORARY
table.Neither the Parent table nor the Child table can be a
PARTITIONED
table.If you declare a FK with the
ON DELETE SET NULL
option, then the FK column(s) must be nullable.If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.
Hope this helps.
I had the same problem with ALTER TABLE ADD FOREIGN KEY
.
After an hour, I found that these conditions must be satisfied to not get error 150:
The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with
ALTER TABLE
.The two tables must both support foreign key constraints, i.e.
ENGINE=InnoDB
. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is
PRIMARY KEY
orUNIQUE KEY
.The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK
REFERENCES Parent(a,b,c)
then the Parent's PK must not be defined on columns in order(a,c,b)
.
The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is
UNSIGNED
, be sure to defineUNSIGNED
for the corresponding column in the Child table field.
Exception: length of strings may be different. For example,
VARCHAR(10)
can referenceVARCHAR(20)
or vice versa.
Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).
If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.
Neither the Parent table nor the Child table can be a
TEMPORARY
table.Neither the Parent table nor the Child table can be a
PARTITIONED
table.If you declare a FK with the
ON DELETE SET NULL
option, then the FK column(s) must be nullable.If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.
Hope this helps.
edited Mar 18 '18 at 20:52
Bill Karwin
374k61513667
374k61513667
answered Jan 12 '11 at 20:39
marv-elmarv-el
2,2711102
2,2711102
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
This includes things likeint(11) unsigned NOT NULL
vsint(11) NOT NULL
.
– Glen Solsberry
May 1 '13 at 20:37
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
|
show 12 more comments
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
This includes things likeint(11) unsigned NOT NULL
vsint(11) NOT NULL
.
– Glen Solsberry
May 1 '13 at 20:37
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
3
3
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
It does help, thank you.
– Timo Huovinen
May 9 '12 at 9:26
4
4
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
one more thing worth adding: if the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK
– Kip
May 9 '12 at 20:44
25
25
This includes things like
int(11) unsigned NOT NULL
vs int(11) NOT NULL
.– Glen Solsberry
May 1 '13 at 20:37
This includes things like
int(11) unsigned NOT NULL
vs int(11) NOT NULL
.– Glen Solsberry
May 1 '13 at 20:37
4
4
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
ALTER TABLE table_name ENGINE=InnoDB;
– TolMera
Jul 24 '13 at 3:15
12
12
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
If the table is defined ENGINE=MyISAM it doesn't generate errno 150 because it ignores foreign key declarations. It's like saying the best way to avoid trouble with your automobile engine is to drive a boat. :-)
– Bill Karwin
Oct 11 '13 at 17:18
|
show 12 more comments
MySQL’s generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:
You can get the actual error message by running SHOW ENGINE INNODB STATUS;
and then looking for LATEST FOREIGN KEY ERROR
in the output.
For example, this attempt to create a foreign key constraint:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
fails with the error Can't create table 'test.t2' (errno: 150)
. That doesn’t tell anyone anything useful other than that it’s a foreign key problem. But run SHOW ENGINE INNODB STATUS;
and it will say:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
It says that the problem is it can’t find an index. SHOW INDEX FROM t1
shows that there aren’t any indexes at all for table t1
. Fix that by, say, defining a primary key on t1
, and the foreign key constraint will be created successfully.
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.
– jatrim
Aug 29 '14 at 0:09
add a comment |
MySQL’s generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:
You can get the actual error message by running SHOW ENGINE INNODB STATUS;
and then looking for LATEST FOREIGN KEY ERROR
in the output.
For example, this attempt to create a foreign key constraint:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
fails with the error Can't create table 'test.t2' (errno: 150)
. That doesn’t tell anyone anything useful other than that it’s a foreign key problem. But run SHOW ENGINE INNODB STATUS;
and it will say:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
It says that the problem is it can’t find an index. SHOW INDEX FROM t1
shows that there aren’t any indexes at all for table t1
. Fix that by, say, defining a primary key on t1
, and the foreign key constraint will be created successfully.
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.
– jatrim
Aug 29 '14 at 0:09
add a comment |
MySQL’s generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:
You can get the actual error message by running SHOW ENGINE INNODB STATUS;
and then looking for LATEST FOREIGN KEY ERROR
in the output.
For example, this attempt to create a foreign key constraint:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
fails with the error Can't create table 'test.t2' (errno: 150)
. That doesn’t tell anyone anything useful other than that it’s a foreign key problem. But run SHOW ENGINE INNODB STATUS;
and it will say:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
It says that the problem is it can’t find an index. SHOW INDEX FROM t1
shows that there aren’t any indexes at all for table t1
. Fix that by, say, defining a primary key on t1
, and the foreign key constraint will be created successfully.
MySQL’s generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:
You can get the actual error message by running SHOW ENGINE INNODB STATUS;
and then looking for LATEST FOREIGN KEY ERROR
in the output.
For example, this attempt to create a foreign key constraint:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
fails with the error Can't create table 'test.t2' (errno: 150)
. That doesn’t tell anyone anything useful other than that it’s a foreign key problem. But run SHOW ENGINE INNODB STATUS;
and it will say:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
It says that the problem is it can’t find an index. SHOW INDEX FROM t1
shows that there aren’t any indexes at all for table t1
. Fix that by, say, defining a primary key on t1
, and the foreign key constraint will be created successfully.
answered Aug 12 '13 at 5:47
andrewdotnandrewdotn
23k16698
23k16698
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.
– jatrim
Aug 29 '14 at 0:09
add a comment |
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.
– jatrim
Aug 29 '14 at 0:09
4
4
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.– jatrim
Aug 29 '14 at 0:09
SHOW ENGINE INNODB STATUS
helped me immediately identify a problem I'd been trying to diagnose for nearly an hour. Thanks.– jatrim
Aug 29 '14 at 0:09
add a comment |
Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.
Often, the 'unsigned' property on an ID column will catch you out.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
add a comment |
Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.
Often, the 'unsigned' property on an ID column will catch you out.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
add a comment |
Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.
Often, the 'unsigned' property on an ID column will catch you out.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.
Often, the 'unsigned' property on an ID column will catch you out.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
edited Jan 11 '13 at 10:31
answered Oct 27 '09 at 15:58
Jon WinstanleyJon Winstanley
16.2k1865105
16.2k1865105
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
add a comment |
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
1
1
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
thanks it was true.
– Mahdi_Nine
Mar 17 '11 at 11:46
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
In my experience, it's worth using MySQL's SHOW CREATE TABLE on your main table to check exactly what flags are set against your main index column, then copy them to your foreign key column. There might be things there, like "unsigned" that aren't obvious.
– Ambulare
Sep 29 '14 at 16:09
add a comment |
What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.
If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.
You may want to check out the manual entry too:
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. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
— MySQL 5.1 reference manual.
add a comment |
What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.
If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.
You may want to check out the manual entry too:
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. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
— MySQL 5.1 reference manual.
add a comment |
What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.
If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.
You may want to check out the manual entry too:
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. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
— MySQL 5.1 reference manual.
What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.
If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.
You may want to check out the manual entry too:
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. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
— MySQL 5.1 reference manual.
edited Jan 19 '16 at 16:32
Sergey Brunov
9,83843165
9,83843165
answered Sep 21 '09 at 23:14
Brent Writes CodeBrent Writes Code
12.5k54152
12.5k54152
add a comment |
add a comment |
For people who are viewing this thread with the same problem:
There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:
MySQL Foreign Key Errors and Errno 150
add a comment |
For people who are viewing this thread with the same problem:
There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:
MySQL Foreign Key Errors and Errno 150
add a comment |
For people who are viewing this thread with the same problem:
There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:
MySQL Foreign Key Errors and Errno 150
For people who are viewing this thread with the same problem:
There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:
MySQL Foreign Key Errors and Errno 150
answered Jun 12 '12 at 23:44
juacalajuacala
1,4311315
1,4311315
add a comment |
add a comment |
For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.
add a comment |
For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.
add a comment |
For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.
For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.
answered Feb 23 '12 at 12:48
Eric LawlerEric Lawler
2,0291619
2,0291619
add a comment |
add a comment |
Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEY
could be not PRIMARY KEY
. Te answer which become useful for me is:
A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
add a comment |
Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEY
could be not PRIMARY KEY
. Te answer which become useful for me is:
A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
add a comment |
Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEY
could be not PRIMARY KEY
. Te answer which become useful for me is:
A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEY
could be not PRIMARY KEY
. Te answer which become useful for me is:
A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40));
CREATE TABLE userroles(
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id));
answered Nov 26 '14 at 14:22
I159I159
9,7242273101
9,7242273101
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
add a comment |
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
This was my problem. Awesome error reporting, MySQL...
– Brian Stinar
Sep 12 '16 at 23:25
add a comment |
As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;
) instead of just an error code.
One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId
use idx_userActionMapping_userId
.
add a comment |
As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;
) instead of just an error code.
One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId
use idx_userActionMapping_userId
.
add a comment |
As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;
) instead of just an error code.
One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId
use idx_userActionMapping_userId
.
As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;
) instead of just an error code.
One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userId
use idx_userActionMapping_userId
.
answered Jun 4 '15 at 4:18
MuchMoreMuchMore
14814
14814
add a comment |
add a comment |
Helpful tip, use SHOW WARNINGS;
after trying your CREATE
query and you will receive the error as well as the more detailed warning:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
So in this case, time to re-create my table!
add a comment |
Helpful tip, use SHOW WARNINGS;
after trying your CREATE
query and you will receive the error as well as the more detailed warning:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
So in this case, time to re-create my table!
add a comment |
Helpful tip, use SHOW WARNINGS;
after trying your CREATE
query and you will receive the error as well as the more detailed warning:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
So in this case, time to re-create my table!
Helpful tip, use SHOW WARNINGS;
after trying your CREATE
query and you will receive the error as well as the more detailed warning:
---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
| Warning | 150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error | 1005 | Can't create table 'exampleTable' (errno:150) |
+---------+------+-------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ---------------+
So in this case, time to re-create my table!
answered Nov 27 '13 at 10:40
sturrockadsturrockad
2,1211318
2,1211318
add a comment |
add a comment |
Please make sure at first that
- you are using InnoDB tables.
- field for FOREIGN KEY has the same type and length (!) as source field.
I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.
add a comment |
Please make sure at first that
- you are using InnoDB tables.
- field for FOREIGN KEY has the same type and length (!) as source field.
I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.
add a comment |
Please make sure at first that
- you are using InnoDB tables.
- field for FOREIGN KEY has the same type and length (!) as source field.
I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.
Please make sure at first that
- you are using InnoDB tables.
- field for FOREIGN KEY has the same type and length (!) as source field.
I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.
edited Jul 29 '16 at 7:58
Inzimam Tariq IT
4,12562349
4,12562349
answered May 29 '16 at 13:25
JuljanJuljan
1,2831115
1,2831115
add a comment |
add a comment |
This is usually happening when you try to source file into existing database.
Drop all the tables first (or the DB itself).
And then source file with SET foreign_key_checks = 0;
at the beginning and SET foreign_key_checks = 1;
at the end.
add a comment |
This is usually happening when you try to source file into existing database.
Drop all the tables first (or the DB itself).
And then source file with SET foreign_key_checks = 0;
at the beginning and SET foreign_key_checks = 1;
at the end.
add a comment |
This is usually happening when you try to source file into existing database.
Drop all the tables first (or the DB itself).
And then source file with SET foreign_key_checks = 0;
at the beginning and SET foreign_key_checks = 1;
at the end.
This is usually happening when you try to source file into existing database.
Drop all the tables first (or the DB itself).
And then source file with SET foreign_key_checks = 0;
at the beginning and SET foreign_key_checks = 1;
at the end.
answered Mar 27 '13 at 20:14
wholenewstrainwholenewstrain
18416
18416
add a comment |
add a comment |
I've found another reason this fails... case sensitive table names.
For this table definition
CREATE TABLE user (
userId int PRIMARY KEY AUTO_INCREMENT,
username varchar(30) NOT NULL
) ENGINE=InnoDB;
This table definition works
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;
whereas this one fails
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;
The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out. Hope that helps someone else.
add a comment |
I've found another reason this fails... case sensitive table names.
For this table definition
CREATE TABLE user (
userId int PRIMARY KEY AUTO_INCREMENT,
username varchar(30) NOT NULL
) ENGINE=InnoDB;
This table definition works
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;
whereas this one fails
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;
The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out. Hope that helps someone else.
add a comment |
I've found another reason this fails... case sensitive table names.
For this table definition
CREATE TABLE user (
userId int PRIMARY KEY AUTO_INCREMENT,
username varchar(30) NOT NULL
) ENGINE=InnoDB;
This table definition works
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;
whereas this one fails
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;
The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out. Hope that helps someone else.
I've found another reason this fails... case sensitive table names.
For this table definition
CREATE TABLE user (
userId int PRIMARY KEY AUTO_INCREMENT,
username varchar(30) NOT NULL
) ENGINE=InnoDB;
This table definition works
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;
whereas this one fails
CREATE TABLE product (
id int PRIMARY KEY AUTO_INCREMENT,
userId int,
FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;
The fact that it worked on Windows and failed on Unix took me a couple of hours to figure out. Hope that helps someone else.
answered Aug 2 '14 at 5:54
TimTim
196113
196113
add a comment |
add a comment |
MySQL Workbench 6.3 for Mac OS.
Problem: errno 150 on table X when trying to do Forward Engineering on a DB diagram, 20 out of 21 succeeded, 1 failed. If FKs on table X were deleted, the error moved to a different table that wasn't failing before.
Changed all tables engine to myISAM and it worked just fine.
add a comment |
MySQL Workbench 6.3 for Mac OS.
Problem: errno 150 on table X when trying to do Forward Engineering on a DB diagram, 20 out of 21 succeeded, 1 failed. If FKs on table X were deleted, the error moved to a different table that wasn't failing before.
Changed all tables engine to myISAM and it worked just fine.
add a comment |
MySQL Workbench 6.3 for Mac OS.
Problem: errno 150 on table X when trying to do Forward Engineering on a DB diagram, 20 out of 21 succeeded, 1 failed. If FKs on table X were deleted, the error moved to a different table that wasn't failing before.
Changed all tables engine to myISAM and it worked just fine.
MySQL Workbench 6.3 for Mac OS.
Problem: errno 150 on table X when trying to do Forward Engineering on a DB diagram, 20 out of 21 succeeded, 1 failed. If FKs on table X were deleted, the error moved to a different table that wasn't failing before.
Changed all tables engine to myISAM and it worked just fine.
edited Aug 24 '15 at 4:49
answered Aug 24 '15 at 4:44
Eduardo ChongkanEduardo Chongkan
58659
58659
add a comment |
add a comment |
Also worth checking that you aren't accidentally operating on the wrong database. This error will occur if the foreign table does not exist. Why does MySQL have to be so cryptic?
add a comment |
Also worth checking that you aren't accidentally operating on the wrong database. This error will occur if the foreign table does not exist. Why does MySQL have to be so cryptic?
add a comment |
Also worth checking that you aren't accidentally operating on the wrong database. This error will occur if the foreign table does not exist. Why does MySQL have to be so cryptic?
Also worth checking that you aren't accidentally operating on the wrong database. This error will occur if the foreign table does not exist. Why does MySQL have to be so cryptic?
answered Jan 22 '13 at 14:27
SystemParadoxSystemParadox
4,81233245
4,81233245
add a comment |
add a comment |
Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.
add a comment |
Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.
add a comment |
Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.
Make sure that the foreign keys are not listed as unique in the parent. I had this same problem and I solved it by demarcating it as not unique.
answered Jun 4 '13 at 15:21
RazaRaza
476715
476715
add a comment |
add a comment |
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, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned
subtype).
add a comment |
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, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned
subtype).
add a comment |
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, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned
subtype).
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, as @Jon mentioned earlier - field definitions have to be the same (watch out for unsigned
subtype).
answered Oct 7 '14 at 12:48
KangurKangur
6,01231824
6,01231824
add a comment |
add a comment |
When the foraign key constraint is based on varchar
type, then in addition to the list provided by marv-el
the target column must have an unique constraint.
add a comment |
When the foraign key constraint is based on varchar
type, then in addition to the list provided by marv-el
the target column must have an unique constraint.
add a comment |
When the foraign key constraint is based on varchar
type, then in addition to the list provided by marv-el
the target column must have an unique constraint.
When the foraign key constraint is based on varchar
type, then in addition to the list provided by marv-el
the target column must have an unique constraint.
edited May 23 '17 at 12:03
Community♦
11
11
answered May 14 '15 at 14:42
RalphRalph
90.4k40229330
90.4k40229330
add a comment |
add a comment |
(Side notes too big for a Comment)
There is no need for an AUTO_INCREMENT
id in a mapping table; get rid of it.
Change the PRIMARY KEY
to (role_id, role_group_id)
(in either order). This will make accesses faster.
Since you probably want to map both directions, also add an INDEX
with those two columns in the opposite order. (There is no need to make it UNIQUE
.)
More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
add a comment |
(Side notes too big for a Comment)
There is no need for an AUTO_INCREMENT
id in a mapping table; get rid of it.
Change the PRIMARY KEY
to (role_id, role_group_id)
(in either order). This will make accesses faster.
Since you probably want to map both directions, also add an INDEX
with those two columns in the opposite order. (There is no need to make it UNIQUE
.)
More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
add a comment |
(Side notes too big for a Comment)
There is no need for an AUTO_INCREMENT
id in a mapping table; get rid of it.
Change the PRIMARY KEY
to (role_id, role_group_id)
(in either order). This will make accesses faster.
Since you probably want to map both directions, also add an INDEX
with those two columns in the opposite order. (There is no need to make it UNIQUE
.)
More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
(Side notes too big for a Comment)
There is no need for an AUTO_INCREMENT
id in a mapping table; get rid of it.
Change the PRIMARY KEY
to (role_id, role_group_id)
(in either order). This will make accesses faster.
Since you probably want to map both directions, also add an INDEX
with those two columns in the opposite order. (There is no need to make it UNIQUE
.)
More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
answered Feb 14 '18 at 3:00
Rick JamesRick James
66.8k55899
66.8k55899
add a comment |
add a comment |
I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
add a comment |
I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
add a comment |
I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.
I encountered the same problem, but I check find that I hadn't the parent table. So I just edit the parent migration in front of the child migration. Just do it.
answered Jun 30 '18 at 6:02
韩向飞韩向飞
244
244
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
add a comment |
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
this should have been a comment instead of an answer
– hannad rehman
Jun 30 '18 at 6:36
add a comment |
protected by Kermit Jul 10 '14 at 23:11
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
1
Could you post the error output and tell us which command (of the three) is causing the error?
– dave
Sep 21 '09 at 23:08
4
What's with the back-ticks around
auto_increment
? That's not valid. Auto_increment is a keyword, not an identifier.– Bill Karwin
Sep 21 '09 at 23:39