Старый баг в MySQL. Детективная история

В этой статье я хочу описать одну ошибку, которую я обнаружил в MySQL (не первый, но тем не менее), и предостеречь вас от проблем, к которым она может привести.

Один парень из моего чата в Telegram спросил меня: «Как я могу увидеть все внешние ключи, связанные с определенной таблицей?» Я дал ему быстрый ответ: «Посмотрите в `information_schema`». Я был полностью уверен в ответе, но всё равно решил убедиться лишний раз.

Я создал простую таблицу `test` и таблицу `ref1` с полем `test_id`, связанным с полем id:

			create table test (
 id int primary key,
 first_name varchar(20), 
 last_name varchar(30)
);

create table ref1 (
 id int primary key,
 test_id int references test(id)
);
		

Кажется, просто, потому что я люблю этот короткий синтаксис. После этого я посмотрел на таблицу информационной схемы `REFERENTIAL_CONSTRAINTS` и не нашел никаких записей.

Что за?! Это невероятно! Я люблю этот краткий синтаксис для создания внешних ключей и использовал его много раз!

			SELECT * FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` WHERE REFERENCED_TABLE_NAME = 'test';
		

Я создал еще одну связанную таблицу `ref2` используя стандартный синтаксис.

			create table ref2 (
 id int primary key,
 test_id int,
 foreign key (test_id) references test(id)
);
		

Снова проверил. И чудо свершилось! Я увидел запись в таблице `REFERENTIAL_CONSTRAINTS`. Как это возможно?

			-- add values to test case
insert into test values (1), (2);

-- add ref1 row referenced to first row in test table
insert into ref1(id, test_id) values (1, 1);

-- add ref2 row referenced to second row in test table
insert into ref2(id, test_id) values (1, 2);
		

Я был разочарован и решил провести ещё один тест. Я подумал что возможно ограничение всё-таки создается, но только не отображается.

Теперь я думал, что если я попробую удалить первую строку из `test` то получу ошибку (я все еще сохраняю надежду на лучшее).

			delete from test where id = 1;
		

Но к моему ужасу MySQL спокойно удаляет эту строку без какого-либо предупреждения.  Как же так?! Мой мир рухнул. Я же постоянно использовал эту конструкцию в надежде, что целостность данных обеспечена.

Чтобы вернуть равновесие, делаю второй тест. Пытаюсь удалить вторую строку, связанную со второй таблицей.

И получаю долгожданную ошибку:

			delete from test where id = 2;
		

Итак, это не фича, это реальный БАГ!  MySQL позволяет создавать таблицы с использованием сокращенного синтаксиса без предупреждения, но ограничения не создаются и не защищают ваши данные от несогласованности.

Я поискал в Google и нашел открытую ошибку с 2004 года, которую до сих пор не исправили. Ребята, что вы делаете?  

Для своего душевного спокойствия я решил запустить этот тест на других базах данных и обнаружил, что все базы данных, на которых я мог проверить на SQLize.online, позволяют использовать короткий синтаксис, и все они (MariaDB, PostgreSQL, SQL Server, Oracle) создают ограничения внешнего ключа в обоих случаях, кроме MySQL и SQLite.

Если Вам понравилась статья, Вы можете поддержать автора.