The old MySQL bug (detective story)

In this article I want to describe about one bug that I found in MySQL and aware you from mistakes what you can do.

Some guy from my Telegram chat asked me: "How can I view all foreign key references to a specific table?" I gave him a quick answer: "Look it up in information_schema", but after that I decided to check my answer.

Just created simple table test and table ref1 with field referenced to id field:

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

Looks simple because I like this short syntax. After that I looked into information_schema table REFERENTIAL_CONSTRAINTS

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

and checked again. After that I sowed records in REFERENTIAL_CONSTRAINTS. How is it possible? I was disappointed and decided to save my mind by next test case:

I created one more referenced table ref2 with canonical syntax:

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

and did check again. After that I sow record in REFERENTIAL_CONSTRAINTS. How it possible? I was disappointed and decided to save my mind by next test case:

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

Now, I think, I will try to delete first row from test and will get error (I still think it some feature, not bug) but MySQL drop this row without any warning

delete from test where id = 1;

but when I try to delete second one that referenced by second table I did not succeed

delete from test where id = 2;

So it is not a feature, it is a real BUG! MySQL allows creating tables using short syntax without warnings but constraints are not created and do not guard your data consistently OMG!

I looked in Google and found the bug has been open since 2004 https://bugs.mysql.com/bug.php?id=4919 and it is still not fixed. Hey guys, what are you doing? Hey guys what you during?

I decided to run this test case over other databases and found all databases that I can test on SQLize.online allows short syntax and all of them (MariaDB, PostgreSQL, SQL Server, Oracle) created foreign key constraints in both of cases except MySQL and SQLite.