I’ve set up two tables:
CREATE TABLE A
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE B
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
id2 INTEGER,
book TEXT,
FOREIGN KEY(id2) REFERENCES A(id)
);
After I insert data into A
, it looks like this:
1 John
2 Amy
3 Peter
After I insert data into B
, it looks like this:
1 1 Lord of the Rings
2 1 Catch 22
3 2 Sum of All Fears
4 3 Hunt for Red October
I then execute the following statement:
delete from a where id=1;
I get the following: "Error: foreign key constraint failed"
I then restart sqlite3
and try again but this time I enter this first:
PRAGMA foreign_keys = 1;
it still doesn’t work……
I’ve set up two tables:
CREATE TABLE A
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE B
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
id2 INTEGER,
book TEXT,
FOREIGN KEY(id2) REFERENCES A(id)
);
After I insert data into A
, it looks like this:
1 John
2 Amy
3 Peter
After I insert data into B
, it looks like this:
1 1 Lord of the Rings
2 1 Catch 22
3 2 Sum of All Fears
4 3 Hunt for Red October
I then execute the following statement:
delete from a where id=1;
I get the following: "Error: foreign key constraint failed"
I then restart sqlite3
and try again but this time I enter this first:
PRAGMA foreign_keys = 1;
it still doesn’t work……
-
Группа:
Администратор -
Сообщений:
8995 -
Регистрация:
20.02.2010
04.03.2011 01:05
Для многих своих таблиц Magento старается поддерживать целостность (непротиворечивость) данных между таблицами, и для этих целей использует ограничения для значений внешних ключей.
Выглядит это примерно так:
ALTER TABLE `directory_country_region_name` ADD CONSTRAINT `FK_DIRECTORY_REGION_NAME_REGION` FOREIGN KEY (`region_id`) REFERENCES `directory_country_region` (`region_id`) ON DELETE CASCADE ON UPDATE CASCADE ;
Если вы обновляете данные напрямую через SQL, вы можете столкнуться с сообщением «Foreign key constraint fails».
Сообщение означает, что обновляя записи одной таблицы, вы разорвали созданную командой «ADD CONSTRAINT» связь между её значениями и значениями другой таблицы, и MySQL видит, что целостность данных нарушена.
Соответственно, MySQL откажется выполнить вашу операцию и сообщит об ошибке «Foreign key constraint fails».
Если вы сознательно временно нарушаете целостность данных, то временно отключите проверку внешних ключей командой:
SET FOREIGN_KEY_CHECKS=0;
После выполнения команд, нарушающих ссылочную целостность и команд, восстанавливающих ссылочную целостность, верните контроль ссылочной целостности обратной командой:
SET FOREIGN_KEY_CHECKS=1;
- Группа:
Администратор - Сообщений:
8995 - Регистрация:
20.02.2010
04.03.2011 01:05
Для многих своих таблиц Magento старается поддерживать целостность (непротиворечивость) данных между таблицами, и для этих целей использует ограничения для значений внешних ключей.
Выглядит это примерно так:
ALTER TABLE `directory_country_region_name` ADD CONSTRAINT `FK_DIRECTORY_REGION_NAME_REGION` FOREIGN KEY (`region_id`) REFERENCES `directory_country_region` (`region_id`) ON DELETE CASCADE ON UPDATE CASCADE ;
Если вы обновляете данные напрямую через SQL, вы можете столкнуться с сообщением «Foreign key constraint fails».
Сообщение означает, что обновляя записи одной таблицы, вы разорвали созданную командой «ADD CONSTRAINT» связь между её значениями и значениями другой таблицы, и MySQL видит, что целостность данных нарушена.
Соответственно, MySQL откажется выполнить вашу операцию и сообщит об ошибке «Foreign key constraint fails».
Если вы сознательно временно нарушаете целостность данных, то временно отключите проверку внешних ключей командой:
SET FOREIGN_KEY_CHECKS=0;
После выполнения команд, нарушающих ссылочную целостность и команд, восстанавливающих ссылочную целостность, верните контроль ссылочной целостности обратной командой:
SET FOREIGN_KEY_CHECKS=1;
When doing:
DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1
It errors:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails
(paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY
(advertiser_id) REFERENCES jobs (advertiser_id))
Here are my tables:
CREATE TABLE IF NOT EXISTS `advertisers` (
`advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(128) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`fax` varchar(255) NOT NULL,
`session_token` char(30) NOT NULL,
PRIMARY KEY (`advertiser_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES
(1, 'TEST COMPANY', '', '', '', '', '', '');
CREATE TABLE IF NOT EXISTS `jobs` (
`job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`advertiser_id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`shortdesc` varchar(255) NOT NULL,
`longdesc` text NOT NULL,
`address` varchar(255) NOT NULL,
`time_added` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`moderated` tinyint(1) NOT NULL,
PRIMARY KEY (`job_id`),
KEY `advertiser_id` (`advertiser_id`,`active`,`moderated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES
(1, 1, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);
Finding out why Foreign key creation fail
When MySQL is unable to create a Foreign Key, it throws out this generic error message:
ERROR 1215 (HY000): Cannot add foreign key constraint
– The most useful error message ever.
Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.
mysql> SHOW ENGINE INNODB STATUS;
That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:
------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table: 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: , CONSTRAINTidx_name
FOREIGN KEY (employee_id
) REFERENCESemployees
(id
) The index in the foreign key in table isidx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
This output could give you some clue about the actual reason why MySQL could not create your Foreign Key
Reason #1 – Missing unique index on the referenced table
This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:
In the all below examples, we’ll use a simple ‘Employee to Department” relationship:
mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1;
Database changed
mysql> CREATE TABLE employees(
-> id int,
-> name varchar(20),
-> department_id int
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE departments(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.07 sec)
As you may have noticed, we have not created the table with PRIMARY KEY
or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id
column and departments.id
column:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint
Let’s look at the detailed error:
mysql> SHOW ENGINE INNODB STATUS;
------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b: FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(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. 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-constraints.html for correct foreign key definition.
This is because we don’t have any unique index on the referenced table i.e. departments
. We have two ways of fixing this:
Option 1: Primary Keys
Let’s fix this by adding a primary key departments.id
mysql> ALTER TABLE departments ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
Option 2: Unique Index
mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
Reason #2 – Different data types on the columns
MySQL requires the columns involved in the foreign key to be of the same data types.
mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1;
Database changed
mysql> CREATE TABLE employees(
-> id int,
-> name varchar(20),
-> department_id int,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE departments(
-> id char(20),
-> name varchar(20),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.07 sec)
You may have noticed that employees.department_id
is int
while departments.id
is char(20)
. Let’s try to create a foreign key now:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint
Let’s fix the type of departments.id
and try to create the foreign key again:
mysql> ALTER TABLE departments MODIFY id INT;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
It works now!
Reason #3 – Different collation/charset type on the table
This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):
Let’s start from scratch to explain this scenario:
mysql> CREATE DATABASE foreign_key_1; Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1; Database changed
mysql> CREATE TABLE employees(
-> id int,
-> name varchar(20),
-> department_id int,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE departments(
-> id int,
-> name varchar(20),
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)
You may notice that we are using a different character set (utf8
and latin
1` for both these tables. Let’s try to create the foreign key:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint
It failed because of different character sets. Let’s fix that.
mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:
mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'
Reason #4 – Different collation types on the columns
This is a rare reason, similar to reason #3 above but at a column level.
Let’s try to reproduce this from scratch:
mysql> CREATE DATABASE foreign_key_1; Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1; Database changed
mysql> CREATE TABLE employees(
-> id int,
-> name varchar(20),
-> department_id char(26) CHARACTER SET utf8,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE departments(
-> id char(26) CHARACTER SET latin1,
-> name varchar(20),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
We are using a different character set for employees.department_id
and departments.id
(utf8
and latin1
). Let’s check if the Foreign Key can be created:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint
Nope, as expected. Let’s fix that by changing the character set of departments.id
to match with employees.department_id
:
mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
It works now!
Reason #5 -Inconsistent data
This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.
Let’s setup some inconsistent data to reproduce this problem:
mysql> CREATE DATABASE foreign_key_1; Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1; Database changed
mysql> CREATE TABLE employees(
-> id int,
-> name varchar(20),
-> department_id int,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE departments(
-> id int,
-> name varchar(20),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
Let’s insert a department_id
in employees
table that will not exist in departments.id
:
mysql> INSERT INTO employees VALUES (1, 'Amber', 145);
Query OK, 1 row affected (0.01 sec)
Let’s create a foreign key now and see if it works:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b`, CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))
This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments
table or by deleting all the employees with the missing department. We’ll do the first option now:
mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row affected (0.00 sec)
Let’s try to create the Foreign Key again:
mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 1 row affected (0.24 sec)
Records: 1 Duplicates: 0 Warnings: 0
It worked this time.
So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.
If you are using MySQL 8.x, the error message will be a little different:
SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible.
The MySQL ERROR 1452
happens when you try to execute a data manipulation query into a table that has one or more failing foreign key constraints.
The cause of this error is the values you’re trying to put into the table are not available in the referencing (parent) table.
Let’s see an example of this error with two MySQL tables.
Suppose you have a Cities
table that contains the following data:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
+----+------------+
Then, you create a Friends
table to keep a record of people you know who lives in different cities.
You reference the id
column of the Cities
table as the FOREIGN KEY
of the city_id
column in the Friends
table as follows:
CREATE TABLE `Friends` (
`firstName` varchar(255) NOT NULL,
`city_id` int unsigned NOT NULL,
PRIMARY KEY (`firstName`),
CONSTRAINT `friends_ibfk_1`
FOREIGN KEY (`city_id`) REFERENCES `Cities` (`id`)
)
In the code above, a CONSTRAINT
named friends_ibfk_1
is created for the city_id
column, referencing the id
column in the Cities
table.
This CONSTRAINT
means that only values recoded in the id
column can be inserted into the city_id
column.
(To avoid confusion, I have omitted the id
column from the Friends
table. In real life, You may have an id
column in both tables, but a FOREIGN KEY
constraint will always refer to a different table.)
When I try to insert 5
as the value of the city_id
column, I will trigger the error as shown below:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
The response from MySQL:
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(`test_db`.`friends`, CONSTRAINT `friends_ibfk_1`
FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`))
As you can see, the error above even describes which constraint you are failing from the table.
Based on the Cities
table data above, I can only insert numbers between 1
to 4
for the city_id
column to make a valid INSERT
statement.
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1);
-- Query OK, 1 row affected (0.00 sec)
The same error will happen when I try to update the Friends
row with a city_id
value that’s not available.
Take a look at the following example:
UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John';
-- ERROR 1452 (23000): Cannot add or update a child row
There are two ways you can fix the ERROR 1452
in your MySQL database server:
- You add the value into the referenced table
- You disable the
FOREIGN_KEY_CHECKS
in your server
The first option is to add the value you need to the referenced table.
In the example above, I need to add the id
value of 5
to the Cities
table:
INSERT INTO `Cities` VALUES (5, 'Liverpool');
-- Cities table:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
| 5 | Liverpool |
+----+------------+
Now I can insert a new row in the Friends
table with the city_id
value of 5
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);
-- Query OK, 1 row affected (0.00 sec)
Disabling the foreign key check
The second way you can fix the ERROR 1452
issue is to disable the FOREIGN_KEY_CHECKS
variable in your MySQL server.
You can check whether the variable is active or not by running the following query:
SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS';
-- +--------------------+-------+
-- | Variable_name | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON |
-- +--------------------+-------+
This variable causes MySQL to check any foreign key constraint added to your table(s) before inserting or updating.
You can disable the variable for the current session only or globally:
-- set for the current session:
SET FOREIGN_KEY_CHECKS=0;
-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;
Now you can INSERT
or UPDATE
rows in your table without triggering a foreign key constraint fails
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Natalia', 8);
-- Query OK, 1 row affected (0.01 sec)
UPDATE `Friends` SET city_id = 17 WHERE `firstName` = 'John';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0
After you’re done with the manipulation query, you can set the FOREIGN_KEY_CHECKS
active again by setting its value to 1
:
-- set for the current session:
SET FOREIGN_KEY_CHECKS=1;
-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;
But please be warned that turning off your FOREIGN_KEY_CHECKS
variable will cause the city_id
column to reference a NULL
column in the cities
table.
It may cause problems when you need to perform a JOIN
query later.
Now you’ve learned the cause of ERROR 1452
and how to resolve this issue in your MySQL database server. Great work! 👍
Дата: 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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
I need to update rows in a child table
,but I am getting following error while I run the code,
Company Table
CREATE TABLE `company`
(
`Company_id` int(11) NOT NULL AUTO_INCREMENT,
`Company_name` varchar(255) DEFAULT NULL,
`BuildingNameNumber` varchar(255) NOT NULL,
`Street_name` varchar(255) NOT NULL,
`Area` varchar(255) NOT NULL,
`Landmark` varchar(255) NOT NULL,
`PIN_code` int(6) NOT NULL,
`PhoneMobile_number` char(15) NOT NULL,
`Website` varchar(255) NOT NULL,
`Fax` varchar(255) NOT NULL,
`Email` varchar(255) NOT NULL,
`Upload_logo` varchar(255) DEFAULT NULL,
`tin_number` varchar(50) NOT NULL,
`CreatedOn` datetime DEFAULT NULL,
`CreatedBy` varchar(255) DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`UpdatedBy` varchar(255) DEFAULT NULL,
`is_active` bit(1) DEFAULT b'0',
PRIMARY KEY (`Company_id`),
);
Service Table
CREATE TABLE `service` (
`service_id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`serial_number` varchar(256) DEFAULT NULL,
`service_name` varchar(256) DEFAULT NULL,
`price` float(10,2) NOT NULL DEFAULT '0.00',
`tax` float(5,2) NOT NULL DEFAULT '0.00',
`is_active` bit(1) NOT NULL DEFAULT b'1',
PRIMARY KEY (`service_id`),
KEY `company_id` (`company_id`),
CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
);
When I trying to update the service table I am getting error as
**Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`etailershop`.`service`, CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE) 0.031 sec**
I was trying to find the error, I do understand the foreign key constraint but I could not find what is wrong with this, similar structure and constraint tables are working fine.
0 / 0 / 0 Регистрация: 24.05.2014 Сообщений: 38 |
|
1 |
|
MySQL 15.07.2014, 13:22. Показов 42112. Ответов 3
#1451 — Cannot delete or update a parent row: a foreign key constraint fails (`admin_bd`.`pics`, CONSTRAINT `fk_pics_h1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) Понимаю, что дело в связях, но не знаю, как убрать эту связь по house_id с fk_pics_h1? Подскажите, плз.
__________________ 0 |
70 / 70 / 13 Регистрация: 01.09.2011 Сообщений: 379 |
|
16.07.2014, 03:45 |
2 |
На запись в таблице houses, которую вы хотите удалить ссылаются записи из таблицы pics, нужно не убирать связь, а изменить параметры ON DELETE и ON UPDATE для таблицы pics относительно внешнего ключа house_id, если вам нужно что бы записи из pics, принадлежащие house_id, при удалении из house тоже удалялись то ON UPDATE CASCADE ON DELETE CASCADE, если не нужно что бы удалялись, то ON UPDATE SET NULL ON DELETE SET NULL. 1 |
0 / 0 / 0 Регистрация: 24.05.2014 Сообщений: 38 |
|
17.07.2014, 01:43 [ТС] |
3 |
На запись в таблице houses, которую вы хотите удалить ссылаются записи из таблицы pics, нужно не убирать связь, а изменить параметры ON DELETE и ON UPDATE для таблицы pics относительно внешнего ключа house_id, если вам нужно что бы записи из pics, принадлежащие house_id, при удалении из house тоже удалялись то ON UPDATE CASCADE ON DELETE CASCADE, если не нужно что бы удалялись, то ON UPDATE SET NULL ON DELETE SET NULL. А вы не подскажете, как это сделать? Или, может, статью какую-нибудь хорошую посоветуете. 0 |
zebulun 70 / 70 / 13 Регистрация: 01.09.2011 Сообщений: 379 |
||||
17.07.2014, 19:24 |
4 |
|||
0 |
The MySQL ERROR 1452
happens when you try to execute a data manipulation query into a table that has one or more failing foreign key constraints.
The cause of this error is the values you’re trying to put into the table are not available in the referencing (parent) table.
Let’s see an example of this error with two MySQL tables.
Suppose you have a Cities
table that contains the following data:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
+----+------------+
Then, you create a Friends
table to keep a record of people you know who lives in different cities.
You reference the id
column of the Cities
table as the FOREIGN KEY
of the city_id
column in the Friends
table as follows:
CREATE TABLE `Friends` (
`firstName` varchar(255) NOT NULL,
`city_id` int unsigned NOT NULL,
PRIMARY KEY (`firstName`),
CONSTRAINT `friends_ibfk_1`
FOREIGN KEY (`city_id`) REFERENCES `Cities` (`id`)
)
In the code above, a CONSTRAINT
named friends_ibfk_1
is created for the city_id
column, referencing the id
column in the Cities
table.
This CONSTRAINT
means that only values recoded in the id
column can be inserted into the city_id
column.
(To avoid confusion, I have omitted the id
column from the Friends
table. In real life, You may have an id
column in both tables, but a FOREIGN KEY
constraint will always refer to a different table.)
When I try to insert 5
as the value of the city_id
column, I will trigger the error as shown below:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
The response from MySQL:
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(`test_db`.`friends`, CONSTRAINT `friends_ibfk_1`
FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`))
As you can see, the error above even describes which constraint you are failing from the table.
Based on the Cities
table data above, I can only insert numbers between 1
to 4
for the city_id
column to make a valid INSERT
statement.
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1);
-- Query OK, 1 row affected (0.00 sec)
The same error will happen when I try to update the Friends
row with a city_id
value that’s not available.
Take a look at the following example:
UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John';
-- ERROR 1452 (23000): Cannot add or update a child row
There are two ways you can fix the ERROR 1452
in your MySQL database server:
- You add the value into the referenced table
- You disable the
FOREIGN_KEY_CHECKS
in your server
The first option is to add the value you need to the referenced table.
In the example above, I need to add the id
value of 5
to the Cities
table:
INSERT INTO `Cities` VALUES (5, 'Liverpool');
-- Cities table:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
| 5 | Liverpool |
+----+------------+
Now I can insert a new row in the Friends
table with the city_id
value of 5
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);
-- Query OK, 1 row affected (0.00 sec)
Disabling the foreign key check
The second way you can fix the ERROR 1452
issue is to disable the FOREIGN_KEY_CHECKS
variable in your MySQL server.
You can check whether the variable is active or not by running the following query:
SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS';
-- +--------------------+-------+
-- | Variable_name | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON |
-- +--------------------+-------+
This variable causes MySQL to check any foreign key constraint added to your table(s) before inserting or updating.
You can disable the variable for the current session only or globally:
-- set for the current session:
SET FOREIGN_KEY_CHECKS=0;
-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;
Now you can INSERT
or UPDATE
rows in your table without triggering a foreign key constraint fails
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Natalia', 8);
-- Query OK, 1 row affected (0.01 sec)
UPDATE `Friends` SET city_id = 17 WHERE `firstName` = 'John';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0
After you’re done with the manipulation query, you can set the FOREIGN_KEY_CHECKS
active again by setting its value to 1
:
-- set for the current session:
SET FOREIGN_KEY_CHECKS=1;
-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;
But please be warned that turning off your FOREIGN_KEY_CHECKS
variable will cause the city_id
column to reference a NULL
column in the cities
table.
It may cause problems when you need to perform a JOIN
query later.
Now you’ve learned the cause of ERROR 1452
and how to resolve this issue in your MySQL database server. Great work! 👍