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;
philipxy
14.7k6 gold badges36 silver badges81 bronze badges
asked Sep 21, 2009 at 22:55
2
These conditions must be satisfied to not get error 150 re ALTER TABLE ADD FOREIGN KEY:
-
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 KEYorUNIQUE 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 defineUNSIGNEDfor 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
TEMPORARYtable. -
Neither the Parent table nor the Child table can be a
PARTITIONEDtable. -
If you declare a FK with the
ON DELETE SET NULLoption, 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.
-
If there are any other FK’s in other tables pointing at the same field you are attempting to create the new FK for, and they are malformed (i.e. different collation), they will need to be made consistent first. This may be a result of past changes where
SET FOREIGN_KEY_CHECKS = 0;was utilized with an inconsistent relationship defined by mistake. See @andrewdotn’s answer below for instructions on how to identify these problem FK’s.
philipxy
14.7k6 gold badges36 silver badges81 bronze badges
answered Jan 12, 2011 at 20:39
marv-elmarv-el
2,6011 gold badge14 silver badges2 bronze badges
16
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, 2013 at 5:47
andrewdotnandrewdotn
31.8k8 gold badges99 silver badges128 bronze badges
2
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;
answered Oct 27, 2009 at 15:58
Jon WinstanleyJon Winstanley
22.7k21 gold badges73 silver badges115 bronze badges
1
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.
answered Sep 21, 2009 at 23:14
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, 2012 at 23:44
juacalajuacala
2,1251 gold badge21 silver badges22 bronze badges
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, 2012 at 12:48
Eric L.Eric L.
3,1622 gold badges22 silver badges20 bronze badges
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, 2014 at 14:22
I159I159
29k31 gold badges94 silver badges130 bronze badges
0
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, 2015 at 4:18
MuchMoreMuchMore
1681 silver badge4 bronze badges
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.
answered May 29, 2016 at 13:25
JuljanJuljan
2,3211 gold badge17 silver badges20 bronze badges
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, 2013 at 10:40
sturrockadsturrockad
4,3822 gold badges18 silver badges19 bronze badges
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, 2013 at 20:14
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, 2014 at 5:54
TimTim
5747 silver badges21 bronze badges
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.
answered Aug 24, 2015 at 4:44
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, 2013 at 14:27
SystemParadoxSystemParadox
7,9775 gold badges48 silver badges56 bronze badges
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, 2013 at 15:21
RazaRaza
2,9972 gold badges29 silver badges34 bronze badges
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, 2014 at 12:48
KangurKangur
7,7143 gold badges28 silver badges30 bronze badges
(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, 2018 at 3:00
Rick JamesRick James
131k11 gold badges126 silver badges214 bronze badges
When the foreign 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.
jww
95.1k88 gold badges397 silver badges862 bronze badges
answered May 14, 2015 at 14:42
RalphRalph
118k56 gold badges282 silver badges378 bronze badges
execute below line before creating table :
SET FOREIGN_KEY_CHECKS = 0;
FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables.
— Specify to check foreign key constraints (this is the default)
SET FOREIGN_KEY_CHECKS = 1;
— Do not check foreign key constraints
SET FOREIGN_KEY_CHECKS = 0;
When to Use :
Temporarily disabling referential constraints (set FOREIGN_KEY_CHECKS to 0) is useful when you need to re-create the tables and load data in any parent-child order
answered Sep 6, 2019 at 11:50
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, 2018 at 6:02
1
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;
philipxy
14.7k6 gold badges36 silver badges81 bronze badges
asked Sep 21, 2009 at 22:55
2
These conditions must be satisfied to not get error 150 re ALTER TABLE ADD FOREIGN KEY:
-
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 KEYorUNIQUE 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 defineUNSIGNEDfor 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
TEMPORARYtable. -
Neither the Parent table nor the Child table can be a
PARTITIONEDtable. -
If you declare a FK with the
ON DELETE SET NULLoption, 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.
-
If there are any other FK’s in other tables pointing at the same field you are attempting to create the new FK for, and they are malformed (i.e. different collation), they will need to be made consistent first. This may be a result of past changes where
SET FOREIGN_KEY_CHECKS = 0;was utilized with an inconsistent relationship defined by mistake. See @andrewdotn’s answer below for instructions on how to identify these problem FK’s.
philipxy
14.7k6 gold badges36 silver badges81 bronze badges
answered Jan 12, 2011 at 20:39
marv-elmarv-el
2,6011 gold badge14 silver badges2 bronze badges
16
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, 2013 at 5:47
andrewdotnandrewdotn
31.8k8 gold badges99 silver badges128 bronze badges
2
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;
answered Oct 27, 2009 at 15:58
Jon WinstanleyJon Winstanley
22.7k21 gold badges73 silver badges115 bronze badges
1
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.
answered Sep 21, 2009 at 23:14
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, 2012 at 23:44
juacalajuacala
2,1251 gold badge21 silver badges22 bronze badges
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, 2012 at 12:48
Eric L.Eric L.
3,1622 gold badges22 silver badges20 bronze badges
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, 2014 at 14:22
I159I159
29k31 gold badges94 silver badges130 bronze badges
0
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, 2015 at 4:18
MuchMoreMuchMore
1681 silver badge4 bronze badges
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.
answered May 29, 2016 at 13:25
JuljanJuljan
2,3211 gold badge17 silver badges20 bronze badges
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, 2013 at 10:40
sturrockadsturrockad
4,3822 gold badges18 silver badges19 bronze badges
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, 2013 at 20:14
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, 2014 at 5:54
TimTim
5747 silver badges21 bronze badges
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.
answered Aug 24, 2015 at 4:44
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, 2013 at 14:27
SystemParadoxSystemParadox
7,9775 gold badges48 silver badges56 bronze badges
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, 2013 at 15:21
RazaRaza
2,9972 gold badges29 silver badges34 bronze badges
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, 2014 at 12:48
KangurKangur
7,7143 gold badges28 silver badges30 bronze badges
(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, 2018 at 3:00
Rick JamesRick James
131k11 gold badges126 silver badges214 bronze badges
When the foreign 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.
jww
95.1k88 gold badges397 silver badges862 bronze badges
answered May 14, 2015 at 14:42
RalphRalph
118k56 gold badges282 silver badges378 bronze badges
execute below line before creating table :
SET FOREIGN_KEY_CHECKS = 0;
FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables.
— Specify to check foreign key constraints (this is the default)
SET FOREIGN_KEY_CHECKS = 1;
— Do not check foreign key constraints
SET FOREIGN_KEY_CHECKS = 0;
When to Use :
Temporarily disabling referential constraints (set FOREIGN_KEY_CHECKS to 0) is useful when you need to re-create the tables and load data in any parent-child order
answered Sep 6, 2019 at 11:50
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, 2018 at 6:02
1
Introduction
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:
create table parent (
id int not null primary key,
name char(80)
) engine=innodb;
create table child (
id int not null,
name char(80),
parent_id int,
foreign key(parent_id) references parent(id)
) engine=innodb;
As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:
- InnoDB (both innodb_plugin and XtraDB)
- PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/)
- SolidDB for MySQL (http://sourceforge.net/projects/soliddb/)
- ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf)
- MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html)
MariaDB foreign key syntax is documented at https://mariadb.com/kb/en/mariadb/foreign-keys/ (and MySQL at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).
However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.
Constraint name not unique
Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:
-------------- CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.45 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") | | Warning | 1022 | Can't write; duplicate key in table 't2' | +---------+------+--------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:
show engine innodb status -------------- ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 12:37:48 7f44a1111700 Error in foreign key constraint creation for table `test`.`t2`. A foreign key constraint of name `test`.`test` already exists. (Note that internally InnoDB adds 'databasename' in front of the user-defined constraint name.) Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.14 sec) -------------- CREATE TABLE t2 ( id int(11) NOT NULL PRIMARY KEY, a int(11) NOT NULL, b int(11) NOT NULL, c int not null, CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id), CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 121) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 121 | Create or Alter table `test`.`t2` with foreign key constraint failed. Foreign key constraint `test/test` already exists on data dictionary. Foreign key constraint names need to be unique in database. Error in foreign key definition: CONSTRAINT `test` FOREIGN KEY (`b`) REFERENCES `test`.`t2` (`id`). | | Error | 1005 | Can't create table 'test.t2' (errno: 121) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
No index
There should be an index for columns in a referenced table that contains referenced columns as the first columns.
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.46 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' 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 `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:
LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:31 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key a (a) references t1(a)) engine=innodb: 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. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
create table t1(a int, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.16 sec) -------------- create table t2(a int, b int, constraint b foreign key (b) references t1(b), constraint a foreign key a (a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Error close to foreign key a (a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Referenced table not found
A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:34 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t11(f1): Cannot resolve table name close to: (f1)
Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:
create table t1 (f1 integer primary key) engine=innodb -------------- Query OK, 0 rows affected (0.11 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t11(f1) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- 150730 13:50:36 Error in foreign key constraint of table `test`.`t1`: Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary close to foreign key (f1) references t11(f1).
Temporary tables
Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+--------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- show engine innodb status -------------- LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_1: foreign key(a) references t1(a)) engine=innodb: Cannot resolve table name close to: (a)) engine=innodb -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.04 sec) -------------- create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `tmp`.`t2`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb. | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -------------- alter table t1 add foreign key(b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `tmp`.`t1`Ï with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary close to foreign key(b) references t1(a). | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Column count does not match
There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.17 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-4856_1' (errno: 150) -------------- show warnings -------------- +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Error | 1005 | Can't create table 'test.#sql-4856_1' (errno: 150) | +-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) -----------------+ show engine innodb status; -----------------+ LATEST FOREIGN KEY ERROR ------------------------ 150730 15:15:57 Error in foreign key constraint of table test/#sql-4856_1: foreign key(a,b) references t1(a): Syntax error close to: 2015-07-30 13:44:35 7f30e1520700 Error in foreign key constraint of table tmp/#sql2612_2_2: foreign key(b) references t1(a): Cannot resolve table name close to: (a)
The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:
create table t1(a int not null primary key, b int, key(b)) engine=innodb -------------- Query OK, 0 rows affected (0.14 sec) -------------- alter table t1 add foreign key(a,b) references t1(a) -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. Foreign key constraint parse error in foreign key(a,b) references t1(a) close to ). Too few referenced columns, you have 1 when you should have 2. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Incorrect cascading
A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.40 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------+ | Error | 1005 | Can't create table `test`.`#sql-2612_2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+--------------------------------------------------------------------------------------------- --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:37 7f30e1520700 Error in foreign key constraint of table test/#sql-2612_2: foreign key (f1) references t1(f1) on update set null: You have defined a SET NULL condition though some of the columns are defined as NOT NULL.
Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:
create table t1 (f1 integer not null primary key) engine=innodb -------------- Query OK, 0 rows affected (0.10 sec) -------------- alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null -------------- ERROR 1005 (HY000): Can't create table 'test.#sql-2b40_2' (errno: 150) -------------- show warnings -------------- +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Alter table `test`.`t1` with foreign key constraint failed. You have defined a SET NULL condition but column f1 is defined as NOT NULL in foreign key (f1) references t1(f1) on update set null close to on update set null. | | Error | 1005 | Can't create table 'test.#sql-2b40_2' (errno: 150) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Incorrect types
Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.47 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -------------- show warnings -------------- +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table 'test/t2' 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 `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | | Warning | 1215 | Cannot add foreign key constraint | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) --------+ show engine innodb status; --------+ LATEST FOREIGN KEY ERROR ------------------------ 2015-07-30 13:44:39 7f30e1520700 Error in foreign key constraint of table test/t2: foreign key(a) references t1(f1)) engine=innodb: 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. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.
But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb -------------- Query OK, 0 rows affected (0.15 sec) -------------- create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb -------------- ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) -------------- show warnings -------------- +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 150 | Create table `test`.`t2` with foreign key constraint failed. Field type or character set for column a does not mach referenced column f1 close to foreign key(a) references t1(f1)) engine=innodb | | Error | 1005 | Can't create table 'test.t2' (errno: 150) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Conclusions
There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!
References
- https://mariadb.atlassian.net/browse/MDEV-6697
- https://mariadb.atlassian.net/browse/MDEV-8524
Дата: 2.12.2016
Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru
Функционирование внешних ключей в MySQL имеет много нюансов и ограничений из-за чего существует немало возможностей получить ошибку при работе с ними. Одна из проблем состоит в том, что сообщения об ошибках содержат мало полезной информации и не указывают причину возникновения ошибки. В данной статье дается объяснение как получить дополнительную информацию об ошибке и приведен полный список причин возникновения ошибок внешних ключей. Каждая причина снабжена уникальным буквенно-цифровым кодом (А4, Б1, ..), использующимся в сводной таблице в конце статьи, которая поможет вам быстро диагностировать проблему.
Внешний ключ — это поле (или набор полей) в таблице, называемой дочерней, которое ссылается на поле (или набор полей) в таблице, называемой родительской. Дочерняя и родительская таблицы могут совпадать, т.е. таблица будет ссылаться на саму себя. Внешние ключи позволяют связать записи в двух таблицах по определенным полям так, что при обновлении поля в родительской автоматически происходит изменение записи в дочерней таблице.
В MySQL внешние ключи не реализованы на уровне сервера, их поддержка зависит от используемого хранилища данных. Содержание статьи справедливо для InnoDB (в том числе и для XtraDB).
Как получить больше данных об ошибке
После получения ошибки выполните SHOW ENGINE INNODB STATUS и смотрите содержимое секции LATEST FOREIGN KEY ERROR. Этот способ имеет следующие недостатки:
- требует привилегии SUPER
- содержит информацию о последней ошибке, связанной с внешними ключами, из-за чего нужно выполнять SHOW ENGINE INNODB STATUS сразу после возникновения ошибки, что не всегда удобно/возможно
- используются внутренние имена таблиц (например, ‘test.#sql-d88_b’), что затрудняет диагностику
- порой содержит мало полезной информации или таковая вообще отсутствует.
Альтернатива: использовать MariaDB версий больше 5.5.45 и 10.0.21, в которых сообщения об ошибках значительно улучшены и указывают причину возникновения ошибки.
Errno 150
Если в сообщении об ошибке содержится errno 150 (или errno 121), значит парсер MySQL не смог распознать ошибку и передал команду (create/alter) на выполнение в InnoDB. В этом разделе перечислены ситуации, приводящие к ошибкам, содержащим errno 150.
А1. Нет индекса в родительской таблице. Набор полей, на которые ссылается дочерняя таблица, должен быть проиндексирован (или являться левой частью другого индекса). Порядок полей в индексе должен быть таким же как в определении внешнего ключа. Сюда же относится случай отсутствия нужной колонки в родительской таблице (нет колонки, нет и индекса).
Неочевидный момент: на колонке родительской таблицы есть индекс — полнотекстовый (fulltext). Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть обычным (btree).
Другой неочевидный момент: на колонке родительской таблицы есть индекс — префиксный. Но внешний ключ всё равно не создается и сервер ругается на отсутствие индекса. Это происходит потому, что индекс должен быть определен на всей длине колонки.
Строго говоря, поля в дочерней таблице тоже должны быть проиндексированы, но если нет подходящего индекса, MySQL автоматически его создаст при добавлении внешнего ключа (в совсем уж древних версиях требовалось предварительное создание индекса).
Примеры
create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-16 06:37:39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key (a) references t1(b)) engine=innodb:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————
— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1
create table t2 (a int) engine=innodb;
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1005 (HY000): Cannot create table ‘test.#sql-a64_1’ (errno: 150)
— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1215 (HY000): Cannot add foreign key constraint
— аналогично и для оператора CREATE
drop table t2;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
Обратите внимание, если внешний ключ уже существует и в результате изменений (alter table) возникает ситуация отсутствия индекса в родительской таблице, то код ошибки будет 1025:
create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
alter table t1 drop a;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_5′ to ‘.testt1′ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220 7:14:25 Error in foreign key constraint of table test/t2:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT «t2_ibfk_1» FOREIGN KEY («a») REFERENCES «t1» («a»)
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `test`.`#sql-d6c_5` to `test`.`t1` failed!
———
А2. Родительская таблица не найдена в словаре данных InnoDB. Это означает, что родительская таблица должна существовать и быть постоянной InnoDB таблицей. Не временной InnoDB таблицей, так как информация о временных таблицах не сохраняется в словаре данных InnoDB. И уж тем более не представлением.
Примеры
mysql> create table t1 (a int, index(a)) engine=myisam;
mysql> create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-17 16:30:09 0x364c Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
————
А3. Синтаксическая ошибка. Внешние ключи реализованы на уровне хранилища, и в старых версиях парсер сервера MySQL не распознавал синтаксические ошибки внешних ключей, из-за чего их было трудно идентифицировать.
Примеры
Например, в определении внешнего ключа количество столбцов дочерней таблицы не совпадает с количеством столбцов родительской таблицы:
create table t1(id int not null primary key, b int, key(b)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)
alter table t1 add foreign key(id,b) references t1(id);
ERROR 1005 (HY000): Can‘t create table ‘test.#sql-d88_b’ (errno: 150)
show warnings;
+——-+——+—————————————————+
| Level | Code | Message |
+——-+——+—————————————————+
| Error | 1005 | Can‘t create table ‘test.#sql-d88_b’ (errno: 150) |
+——-+——+—————————————————+
— понять, что причина в синтаксической ошибке
— можно только из:
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22:28:23 Error in foreign key constraint of table test/#sql-d88_b:
foreign key(id,b) references t1(id):
Syntax error close to:
— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don‘t match
Другой пример: попробуем создать внешний ключ на поле типа text:
create table t1 (a text , index(a(50))) engine=innodb;
create table t2 (a text, foreign key (a) references t1(a)) engine=innodb;
ERROR 1170 (42000): BLOB/TEXT column ‘a’ used in key specification without a key length
— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:
create table t2 (a text, foreign key (a(50)) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.
А4. Несовпадение типов данных. Столбцы дочерней таблицы, входящие в определение внешнего ключа, должны иметь такие же типы данных, что и столбцы родительской таблицы, на которые они ссылаются, вплоть до атрибутов: знак и кодировка/сопоставление.
Примеры
— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 (a int unsigned, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 03:00:47 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————
Если несоответствие типов данных возникает во время изменения таблицы при уже существующем внешнем ключе, то ошибка будет иметь вид:
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
MariaDB [test]> alter table t1 modify a int unsigned;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt1′ (errno: 150)
А5. Некорректно задано действие внешнего ключа. Если в определении внешнего ключа указано ON UPDATE SET NULL и/или ON DELETE SET NULL, то соответствующие столбцы дочерней таблицы не должны быть определены как NOT NULL.
Примеры
create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int not null, foreign key (a) references t1(a) on delete set null) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
— в старых версиях будет:
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 06:24:42 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a) on delete set null) engine=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
————
Если коллизия возникает при уже существующем внешнем ключе, то:
create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a) on delete set null) engine=innodb;
alter table t2 modify a int not null;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt2′ (errno: 150)
А6. Дочерняя таблица является временной InnoDB таблицей. Внешние ключи можно создавать только в постоянной, несекционированной InnoDB таблице.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create temporary table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 4:22:26 Error in foreign key constraint of table temp/#sql318_4_1:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— в новых версиях ошибка будет иметь вид:
ERROR 1215 (HY000): Cannot add foreign key constraint
А7. Родительская таблица является секционированной таблицей. На данный момент (MySQL 5.7 и MariaDB 10.1) внешние ключи не поддерживаются для секционированных таблиц (partitioned tables). Иными словами, ни родительская, ни дочерняя таблица не должны иметь секции. В случае, когда внешний ключ ссылается на секционированную таблицу диагностика ошибки затруднена ошибкой вывода show engine innodb status:
Примеры
create table t1 (a int, index(a)) partition by range (a)
(partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19:38:14 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)
— в новых версиях ошибка будет иметь вид:
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint
Если разбивать на секции родительскую таблицу после создания внешнего ключа, то
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
alter table t1 PARTITION BY HASH(a) PARTITIONS 8;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR
Errno 121
Такой результат возникает только в одном случае.
Б1. Неуникальное имя ограничения. Обратите внимание: речь не о имени внешнего ключа. Если при создании внешнего ключа вы указываете не обязательное ключевое слово CONSTRAINT, то идущий после него идентификатор должен быть уникальным в пределах базы данных.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;
create table t3 (a int, CONSTRAINT q1 foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t3’ (errno: 121)
— в 5.7 будет другая ошибка
ERROR 1022 (23000): Cannot write; duplicate key in table ‘t3’
show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161130 3:31:11 Error in foreign key constraint creation for table `test`.`t3`.
A foreign key constraint of name `test`.`q1`
already exists. (Note that internally InnoDB adds ‘databasename’
in front of the user-defined constraint name.)
Note that InnoDB FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
———
Нет ошибок
Внешний ключ не создается, и нет никаких ошибок. Это может происходить по следующим причинам:
В1. Дочерняя таблица не является InnoDB таблицей. В этом случае для совместимости с другими субд парсер MySQL просто проигнорирует конструкцию внешнего ключа.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=myisam;
Query OK, 0 rows affected (0.33 sec)
MariaDB [test]> show create table t2G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
В2. Не соответствует синтаксису MySQL. Стандарт SQL разрешает указывать внешний ключ сразу при объявлении колонки с помощью конструкции REFERENCES (например, … a int references t1(a), …), однако MySQL игнорирует такую форму записи. Единственный способ создать в нем внешний ключ — это использовать отдельный блок FOREIGN KEY:
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, …)
REFERENCES tbl_name (index_col_name,…)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Несоответствие данных
В этой части собраны ошибки, которые возникают из-за нарушения ссылочной целостности, т.е. наличие в дочерней таблице записей, которым нет соответствия в родительской таблице.
Г1. Удаление родительской таблицы. Нельзя удалить родительскую таблицу при наличии внешнего ключа.
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
drop table t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Удаление следует понимать в расширенном варианте как удаление из множества InnoDB таблиц. Например, если мы сменим (alter table) движок родительской таблицы на MyISAM, то с точки зрения ограничения внешнего ключа родительская таблица перестанет существовать (т.к. она должна быть постоянной innodb таблицей):
alter table t1 engine=myisam;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Сначала нужно удалить внешний ключ (или всю дочернюю таблицу, что удалит в том числе и внешний ключ). Если вы не знаете какие таблицы являются дочерними для заданной таблицы, то это можно определить через запрос к information_schema:
select table_name from information_schema.key_column_usage
where table_schema = «test» and references_table_name = «t1»;
Г2. Изменение данных в родительской таблице. Если в определении внешнего ключа не задано действие при update/delete, то такие операции над родительской таблицей могут привести к несогласованности данных, т.е. появлению в дочерней таблице записей не имеющих соответствия в родительской таблице.
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
insert into t1 values(1);
insert into t2 values(1);
update t1 set a=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))
Г3. Изменение данных в дочерней таблице. Если insert/update записи в дочерней таблицы приводит к несогласованности данных, то
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
insert into t2 values(15);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
Г4. Добавление внешнего ключа на не пустую таблицу. При попытке добавить внешний ключ на таблицу, в которой есть записи, не удовлетворяющие условию внешнего ключа (т.е. не имеющие соответствия в родительской таблице), будет ошибка:
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;
insert into t2 values(2);
alter table t2 add foreign key (a) references t1(a);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-3f0_4`, CONSTRAINT `#sql-3f0_4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
Г5. Не уникальный ключ в родительской таблице. По стандарту SQL набор полей, на которые ссылается внешний ключ, должен быть уникальным. Однако, реализация внешних ключей в InnoDB позволяет иметь несколько «родителей». Из-за этого возникает трудно диагностируемая ошибка:
Примеры
create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, index(a)) engine=innodb;
insert into t1 values (1),(1);
insert into t2 values(1);
delete from t1 where a=1 limit 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1`(`a`))
Сводная таблица
По вертикали расположены коды ошибок MySQL, которые возникают при работе с внешними ключами («нет ошибок» соответствует ситуации, когда сервер не генерирует ошибку, но и не создает внешний ключ). По горизонтали — идентификаторы причин, которые могут привести к ошибке. Плюсы на пересечении указывают какие причины приводят к той или иной ошибке.
| А1 | А2 | А3 | А4 | А5 | А6 | А7 | Б1 | В1 | В2 | Г1 | Г2 | Г3 | Г4 | Г5 | |
| MySQL error 1005 | + | + | + | + | + | + | + | + | |||||||
| MySQL error 1022 | + | ||||||||||||||
| MySQL error 1025 | + | + | + | ||||||||||||
| MySQL error 1215 | + | + | + | + | + | ||||||||||
| MySQL error 1217 | + | + | |||||||||||||
| MySQL error 1239 | + | ||||||||||||||
| MySQL error 1451 | + | + | |||||||||||||
| MySQL error 1452 | + | + | |||||||||||||
| нет ошибок | + | + |
P.S. Если ваш случай не рассмотрен в статье, то задавайте вопрос на форуме SQLinfo. Вам ответят, а статья будет расширена.
Дата публикации: 2.12.2016
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.