Ошибка 23000 mysql

+----+------------+
| id | city_name  |
+----+------------+
|  1 | York       |
|  2 | Manchester |
|  3 | London     |
|  4 | Edinburgh  |
+----+------------+

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`)
)

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);

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`))

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1);

-- Query OK, 1 row affected (0.00 sec)

UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John';

-- ERROR 1452 (23000): Cannot add or update a child row

INSERT INTO `Cities` VALUES (5, 'Liverpool');

-- Cities table:
+----+------------+
| id | city_name  |
+----+------------+
|  1 | York       |
|  2 | Manchester |
|  3 | London     |
|  4 | Edinburgh  |
|  5 | Liverpool  |
+----+------------+

INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);

-- Query OK, 1 row affected (0.00 sec)

SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS';

-- +--------------------+-------+
-- | Variable_name      | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON    |
-- +--------------------+-------+

-- set for the current session:
SET FOREIGN_KEY_CHECKS=0;

-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

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

-- set for the current session:
SET FOREIGN_KEY_CHECKS=1;

-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;

Wondering how to resolve MySQL Error 1452? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Microsoft SQL Server Support Services.

Let’s take a look at how our Support Team is ready to help customers with MySQL Error 1452.

How to resolve MySQL Error 1452?

Usually, this error occurs when we try to execute a data manipulation query into a table that has one or more failing foreign key constraints.

What causes MySQL Error 1452?

The cause of this error is the values we are trying to put into the table are not available in the referencing (parent) table.

When a column of a table is referenced from another table, it is called Foreign Key.

For example, consider a table City that contains the name of a city and its ID.

Also, there is another table Buddies to keep a record of people that we know who lives in different cities.

We have to reference the id column of the City 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 City (id)
)

In the code above, a CONSTRAINT named buddies_ibfk_1 is created for the city_id column, referencing the id column in the City table.

This CONSTRAINT means that only values in the id column can be inserted into the city_id column.

If we try to insert a value that is not present in id column into the city_id column, it will trigger the error as shown below:

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 city (id))

How to resolve it?

Today, let us see the steps followed by our Support Techs to resolve it:

There are two ways to fix the ERROR 1452 in MySQL database server:

1. Firstly, add the value into the referenced table
2. Then, disable the FOREIGN_KEY_CHECKS in the server

1. Add the value into the referenced table

The first option is to add the value we need to the referenced table.

In the example above, add the required id value to the City table.

Now we can insert a new row in the Buddies table with the city_id value that we inserted.

Disabling the foreign key check

2. Disable the FOREIGN_KEY_CHECKS variable in MySQL server.

We 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 our table(s) before inserting or updating.

We 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 we can INSERT or UPDATE rows in our table without triggering a foreign key constraint fails.

After we are done with the manipulation query, we 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;

Turning off FOREIGN_KEY_CHECKS variable will cause the city_id column to reference a NULL column in the City table.

It may cause problems when we need to perform a JOIN query later.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to resolve MySQL Error 1452.

In MySQL, Enter a row in the child table without having row in the parent table

Example of insert row in Child table without having a row in Parent table MySQL

1. Create the table Parent P1 and Child C1.

mysql> create table p1(id integer primary key, name varchar(100));
Query OK, 0 rows affected (0.09 sec)

mysql> create table c1(cid integer primary key, pid integer, foreign key (pid) references p1(id));
Query OK, 0 rows affected (0.09 sec)

2. Insert data in the Parent and child table and child table throw error due to not presence of data in the parents table.

mysql> insert into p1 values (1,'a');
Query OK, 1 row affected (0.03 sec)

mysql> insert into p1 values (2,'b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into c1 values (2,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

3. Disable the foreign key check and enable it.

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into c1 values (3,5);
Query OK, 1 row affected (0.02 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

4. After enabling the foreign key check, the insert query throw error again.

mysql> insert into c1 values (4,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

5. Verify the data in both P1 and C1 tables.

mysql> select * from c1;
+-----+------+
| cid | pid  |
+-----+------+
|   3 |    5 |
+-----+------+
1 row in set (0.00 sec)

mysql> select * from p1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

This is another article written in order to handle error which is generated from MySQL Database Server. The error as specified in the title is ‘Cannot add or update a child row: a foreign key constraint fails’. The error itself is triggered when a query is being executed. In the context of this article, the query which is being executed is performed to add a record in the table.

The scenario is actually similar or in other words it is the scenario which is used in other article titled ‘MySQL Database generate ERROR 1215 (HY000): Cannot add foreign key constraint‘ which can be visited in this link.

The relationship between tables can be viewed in the following image :

Basically, the scenario is following the above ERD (Entity Relationship Diagram) with the availability of three tables. The error triggered when a query which is performed to insert a new record is being executed in table user_role. Below is the example of the query executed through MySQL Command Console :

mysql> insert into user_role(id_user,id_role) values(7,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_app`.`user_role`, CONSTRAINT `user_role_fk1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 

The following is actually a command which is can be performed to solve the above problem :

mysql> set  foreign_key_checks=0;
Query OK, 0 rows affected (0,01 sec)

After performing the above query, try to add the record again by executing the following query to add a new record :

mysql> insert into user_role(id_user,id_role) values(7,1);
Query OK, 1 row affected (0,02 sec)

mysql> insert into user_role(id_user,id_role) values(7,2);
Query OK, 1 row affected (0,02 sec)

mysql> insert into user_role(id_user,id_role) values(8,2);
Query OK, 1 row affected (0,07 sec)

mysql> insert into user_role(id_user,id_role) values(8,3);
Query OK, 1 row affected (0,01 sec)

So, based on the above output, the query has already successfully executed. Try to select the already inserted record in the above by executing the following query :

mysql> select * from user_role;
+---------+---------+
| id_user | id_role |
+---------+---------+
|       7 |       1 |
|       7 |       2 |
|       8 |       2 |
|       8 |       3 |
+---------+---------+
4 rows in set (0,01 sec)

mysql> 

So, based on the output presented above as a query result, the query for adding a new record has executed succesfuly.

To understand error 1452, first we need to create a table and relate that to another table with
the help of a foreign key constraint.

Creating the first table −

mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );
Query OK, 0 rows affected (0.43 sec)

After creating the first table successfully, we will create the second table −

mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );
Query OK, 0 rows affected (0.48 sec)

Now, we have created both tables. Then both the tables are related with the help of alter
command as well as adding foreign key constraint. The syntax is as follows −

alter table yourFirstTable add constraint anyConstraintName foreign key(column_name which is
acts foreign key in second table) yourSecondTable(column_name which acts primary key in
second table).

Now, the above query is used to relate both the tables. This is given as follows −

mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references
primaryTable1(Fk_pk);
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now, both the tables are related. The records are inserted into the table ‘foreignTable’ as
follows −

mysql> INSERT into ForeignTable values(1,'John',1);

This results in an error that is shown in the below output −

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`business`.`foreigntable`, CONSTRAINT `constFKPK` FOREIGN KEY (`Fk_pk`)
REFERENCES `primarytable1` (`fk_pk`))

In the above output, we are getting the error ‘Cannot add or update a child row: a foreign key
constraint fails’. We can remove this error by inserting the records into the table primaryTable1
as shown below −

mysql> INSERT into primaryTable1 values(1,'ComputerScience');
Query OK, 1 row affected (0.14 sec)

After inserting the records into the table primaryTable1, we can insert the required records into
the table ForeignTable without any errors. This is shown below −

mysql> INSERT into ForeignTable values(1,'John',1);
Query OK, 1 row affected (0.13 sec)

Now, we can display the table records of ForeignTable with the help of the select command,
which is as follows −

mysql> SELECT * from ForeignTable;

The output of the above query is −

+------+------+-------+
| id   | name | Fk_pk |
+------+------+-------+
| 1    | John | 1     |
+------+------+-------+
1 row in set (0.00 sec)

We can also display the table records of primarytable1 with the help of the select command,
which is as follows −

mysql> SELECT * from primarytable1;

The output of the above query is −

+-------+-----------------+
| Fk_pk | DeptName        |
+-------+-----------------+
| 1     | ComputerScience |
+-------+-----------------+
1 row in set (0.00 sec)

The error 1452 — Cannot add or update a child row: a foreign key constraint fails occurs when data record is initially inserted into the ForeignTable.

Note: First, add the record into the second table i.e primarytable1 to avoid the above error.

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:
,
CONSTRAINT idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_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 latin1` 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. 

До сих мы создавали таблицы и связи между ними представляли. Просто я не хотел усложнять примеры и при этом иметь возможность устанавливать любые значения и отключить защиту базы данных от неверных значений.

Когда я показывал LEFT JOIN, то без проблем смог вставить в таблицу телефонов запись с cityid равным 20, хотя реально такого города не существовало. База данных может защитить нас от таких глупостей.

А что, если мы попытаемся удалить город с номером 1 (Торонто)? Без защиты это можно сделать и тогда все телефоны, которые были созданы с указанием на город Торонто будут указывать в никуда.

Обе защиты можно реализовать с помощью одной и то же вещи – внешнего ключа.

Давайте вернемся в таблицу телефонов и сначала исправим ее. Сначала вставим в базу данных хотя бы одну строку, в которой cityId будет нереальный, например, 100.

INSERT INTO phone (firstname, cityid) values ('Bad Guy', 100);

Теперь попробуем создать связь между двумя таблицами:

ALTER TABLE phone ADD FOREIGN KEY (cityid) references city (cityid);

В Результате должна быть ошибка, которая не самая интеллектуальная, но все же она выглядит так:

ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`testdb`.`#sql-7c_f`, CONSTRAINT `#sql-7c_f_ibfk_1` 
FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

Даже переводить эту ошибку не буду, потому что она абсолютно непонятная и не отражает реальную проблему. А проблема в том, что мы создали запись с телефоном для города, которого не существует, а цель ключа как раз в том, чтобы защищать данные от подобных случаев, только корректные данные должны быть в базе. Исправим ситуацию, давайте изменим cityid на NULL для некорректной записи, которую мы только вставили:

UPDATE phone set cityid = null where cityid =100;

Повторяем попытку создать связь между таблицами:

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city (cityid);

На этот раз проблем не должно быть и есть смысл рассмотреть команду:

ПОДПРАВИТЬ ТАБЛИЦУ phone 
ДОБАВИТЬ ВНЕШНИЙ КЛЮЧ (колонка в таблице phone) 
ССЫЛАЕТСЯ другую таблицу (колонка в этой таблице);

Итак, мы добавляем внешний ключ. Что это такое? Это умное слово, которое на самом деле означает какую-то фигню, которая связывает две таблицы. Для нас главное понимать направление связи. Мы создаем внешний ключ для таблицы phone, и она ссылается на cityid, а не наоборот:

ALTER TABLE phone

Внешний ключ создается для колонки cityid в таблицы телефонов:

ALTER TABLE phone ADD FOREIGN KEY (cityid)

Мы ссылаемся на таблицу city:

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city

И в этой таблице city связь идет на колонку cityid.

ALTER TABLE phone ADD FOREIGN KEY (cityid) REFERENCES city (cityid);

Если заменить слово REFFERENCES на ->

ALTER TABLE phone ADD FOREIGN KEY (cityid) -> city (cityid);

И убрать все остальные служебные слова и пробелы, за исключением имен колонок и таблиц, то останется:

Phone(cityid) -> city(cityid)

Именно такое направление – cityid из телефонов ссылается на cityid в таблице city.

Почему направление так важно? Мы можем ссылаться только на уникальную колонку, но базы данных могут идти дальше и говорить, что ссылаться можно только на первичный ключ, который тоже по своей природе обеспечивает уникальность. Это уже вопрос реализации конкретной базы.

Колонка cityid является первичным ключом, поэтому на нее мы можем ссылаться. Колонка cityid в таблице телефонов – это просто число, там нет никакой уникальности, поэтому на него ссылаться нельзя.

А может уникальная колонка из одной таблицы ссылаться на уникальную колонку в другой таблицы? Может. Просто в этом случае связь будет один к одному.

Если у вас есть таблица городов:

+--------+-----------+
| cityid | cityname  |
+--------+-----------+
|      1 | Toronto   |
|      2 | Vancouver |
|      3 | Montreal  |
+--------+-----------+

И каких-то еще данных:

+--------+-----------+
| id     | name      |
+--------+-----------+
|      1 | Something |
|      2 | Another   |
|      3 | One more  |
+--------+-----------+

То вы сможете связать эти две таблицы по колонкам cityid -> id или в обратном направлении, это уже не имеет значения, потому что в обоих случаях ключ. Но в этом случае в обеих таблицах что-то уникальное и значит связь будет один к одному – одна запись в таблице городов соответствует одной записи в другой таблице. Вторую запись с уже существующим id вставить не получится, в том то и смысл уникальности и первичных ключей.

В нашем случае колонка cityid в таблицы телефонов – это просто число, и мы можем создать несколько телефонов с одним и тем же id города и у нас получается связь многие к одному – много телефонов в одном городе.

А можно сказать, что несколько телефонов принадлежать нескольким городам. Может же быть такое, что номер зарегистрирован в двух городах?

Или такой вариант – человек может обладать несколькими номерами или один номер принадлежит двум людям (мужу с женой). К сожаления напрямую внешними ключами такое реализовать нельзя, мы можем ссылаться только на уникальное значение, но чуть позже мы рассмотрим, как же можно обойти это ограничение и реализовать все же связь многие ко многим.

За счет того, что мы навели такую связь в нашей таблице обязательно должны быть только корректные id городов из таблицы city или несуществующие значения (NULL). Посмотрим, какие у нас есть города:

select * from city;
+--------+-----------+
| cityid | cityname  |
+--------+-----------+
|      1 | Toronto   |
|      2 | Vancouver |
|      3 | Montreal  |
+--------+-----------+

За счет установленной связи мы можем вставлять в таблицу телефонов записи с cityid одним из этих значений или NULL.

Попробуем вставить сейчас запись с cityid = 100:

INSERT INTO phone (firstname, cityid) values ('Bad Guy', 100);

До создания ключа эта операция закончилась успешно, а на этот раз она накрылось медным тазом, а точнее со следующей ошибкой?

ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`testdb`.`phone`, CONSTRAINT `phone_ibfk_1` 
FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

Попробуй перевести этот тест, потому что тут есть смысл:

Ошибка 1452 (23000): Не могу добавить или обновить дочернюю запись
Ошибка ограничения внешнего ключа (testdb.phone ограничение phone_idfk_1
ВНЕШНИЙ КЛЮЧ (cityid) ссылающийся на city (cityid))

Самая последняя строка очень похожа на ту команду, которую мы выполняли при создании внешнего ключа и именно его ограничение мы пытаемся нарушить, когда пытаемся вставить запись для города с id 100, а такого города не существует.

В city можно вставлять что угодно, потому что эта таблица ни на кого не ссылается, на нее ссылаются.

А что если попробовать удалить какой-то город из таблицы city:

delete from city where cityid = 1;

Снова ошибка:

1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`testdb`.`phone`, 
CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

Нельзя удалять записи из таблицы, если есть другая таблица, которая содержит записи для данного города. Если бы ключа не было, то мы могли бы удалять что угодно, а так нельзя. Нужно сначала удалить все телефоны, которые ссылаются на этот город, а потом удаление произойдет успешно. Так что нужно выполнить две команды:

delete from phone where cityid = 1;
delete from city where cityid = 1;

Я выполнять эти команды не буду, но поверьте мне, они выполнятся успешно, потому что после удаления всех телефонов с cityid равным 1, мы сможем удалить и город с этим id.

Итак, у нас получается два правила:

Если таблица ссылается НА ДРУГУЮ, то в связующую колонку (в нашем случае cityid в таблице phone) можно вставить только такое значение, которе есть в таблице city, иначе мы нарушаем правила связи. Удалять из этой таблицы можно любые записи.

Если ДРУГАЯ таблица ссылается на нашу, как это происходит с city, то в такую таблицу можно вставлять что угодно, мы связь не нарушим. А вот удалить можно только если мы не нарушаем связь, то есть в другой таблице нет записей с удаляемым id.

Давайте еще раз взглянем на ошибку:

1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`testdb`.`phone`, 
CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`))

В самой последней строке говориться CONSTRAINT `phone_ibfk_1`. Что за фигня, какое еще ограничение. Мы же ничего такого не создавали, мы создавали только внешний ключ. Проблема в том, что foreign key и есть ограничение constraint – ограничение на вставку и запись, о котором мы говорили выше. Мы просто использовали команду ADD FOREIGN KEY без указания имени ограничения и поэтому база данных выбрала такое странное имя phone_ibfk_1 за нас.

Если мы не указали имени, как его узнать? Тут все зависит от базы данных, для MySQL можно было бы выполнить команду:

SHOW CREATE TABLE phone;

В результате мы должны увидеть вот такой SQL:

CREATE TABLE `phone` (
  `phoneid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `cityid` int(11) DEFAULT NULL,
  PRIMARY KEY (`phoneid`),
  KEY `cityid` (`cityid`),
  CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

Опа, оказывается мы можем увидеть полный SQL создания таблицы! Причем в предпоследней строке указано ограничение внешнего ключа:

  CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)

Да, мы можем создавать внешний ключ прямо при создании таблицы при выполнении CREATE TABLE. Но для нас очень важно то, что мы можем так еще и увидеть название ограничения.

С помощью ALTER TABLE мы могли добавлять колонки

ALTER TABLE phone ADD колонка

И удалять:

ALTER TABLE phone DROP колонка

В этой части мы с помощью похожей команды добавили внешний ключ:

ALTER TABLE phone ADD FOREIGN KEY

Может мы и удалить можем с помощью:

ALTER TABLE phone DROP FOREIGN KEY

Да, можем, просто нужно передать после этого имя ограничения, которое мы хотим удалить:

ALTER TABLE phone DROP FOREIGN KEY phone_ibfk_1;

Если вы не хотите видеть имя ограничения по умолчанию, то его можно указать при создании связи. Просто нужно добавить после имени таблицы и перед FOREIGN KEY указать ADD CONSTRAINT имя_ограничения:

ALTER TABLE phone ADD CONSTRAINT fk_city FOREIGN KEY (cityid) REFERENCES city (cityid);

Проверяем, что получилось:

SHOW CREATE TABLE phone;

Результат:

CREATE TABLE `phone` (
  `phoneid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `cityid` int(11) DEFAULT NULL,
  PRIMARY KEY (`phoneid`),
  KEY `fk_city` (`cityid`),
  CONSTRAINT `fk_city` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Теперь в предпоследней строке запроса мы видим ограничение CONSTRAINT с именем, которое мы указали при создании.

Удалим это именованное ограничение:

ALTER TABLE phone DROP FOREIGN KEY fk_city;

Вернемся к правилам, которые поддерживают ключи. Если мы хотим удалить данные из таблицы, на которую ссылаются (из таблицы городов), то мы должны удалить все телефоны или изменить их id. То есть должны выполнить команды:

delete from phone where cityid = 1;
delete from city where cityid = 1;

или

update phone set cityid = null where cityid = 1;
delete from city where cityid = 1;

В первом перед удалением города я удаляю все телефоны, где id города равен 1, а во втором случае я меняю на null. Оба варианта работают, потому что мы делаем так, чтобы в таблице телефонов не осталось записей с нужным cityid на рушить связь.

Было бы круто, если бы база данных могла делать это за нас. И она может. Просто при создании внешнего ключа нужно указать, что мы хотим сделать со связанными данными – удалить или установить в null. Чтобы удалять, мы можем добавить к команде создания внешнего ключа три магических слова ON DELETE CASCADE – при удалении каскадно удалять и из связанных таблиц:

ALTER TABLE phone 
ADD CONSTRAINT fk_city 
FOREIGN KEY (cityid) REFERENCES city (cityid);
ON DELETE CASCADE

В этом случае при попытке выполнить команду удалении города, все записи телефонов с этим cityid будут так же удалены, то есть достаточно выполнить только одну команду:

DELETE FROM city WHERE cityid = 1;

Удалять из телефонов не нужно, произойдет все автоматически.

Чтобы обнулять выполняем команду создания внешнего ключа и добавляем ON DELETE SET NULL – при удалении устанавливать связанные записи в NULL:

ALTER TABLE phone 
ADD CONSTRAINT fk_city 
FOREIGN KEY (cityid) REFERENCES city (cityid);
ON DELETE SET NULL

Теперь при удалении записи из города:

DELETE FROM city WHERE cityid = 1;

реально будет автоматически выполняться:

UPDATE  phone SET cityid = null WHERE cityid = 1;
DELETE FROM city WHERE cityid = 1;

Стоит ли использовать ON DELETE SET NULL или ON DELETE SET DELETE? Лично я никогда не использую и вам не рекомендую. Весь смысл от внешнего ключа – это защита данных, чтобы вы не вставляли не существующие id городов и не удаляли города, для которых есть данные. Используя ON DELETE вы отключаете вторую защиту и если случайно попытаетесь удалить город, то будут удалены все телефоны и это может привести к потере данных. Если вы реально хотите удалить данные, то делайте это явно и не стоит возлагать эти вещи на базу данных.