Unveiling a MySQL Bug: Inconsistent Foreign Key Constraints

Hey folks! In this article, I want to share an interesting bug that I stumbled upon in MySQL. It's all about those pesky foreign key constraints and how they can behave inconsistently. So, let's dive in and uncover this bug together!

The Bug: Missing Foreign Key References in Information Schema

The story begins with a question from a guy in my Telegram chat. He asked, "Hey, how can I view all foreign key references to a specific table?" Without thinking twice, I replied, "Just look it up in information_schema" But then, something caught my attention, and I decided to investigate my answer further.

To test things out, I created two simple tables: "test" and "ref1." The latter had a field that referenced the "id" field in the "test" table. Surprisingly, when I checked the REFERENTIAL_CONSTRAINTS table in the information_schema, I couldn't find any records. Can you believe it? I was shocked because I've used this syntax countless times before!

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

The Quest to Uncover the Truth

Determined to get to the bottom of this mystery, I decided to try a different approach. I created another table called "ref2" using the more traditional syntax for defining foreign key constraints.

CREATE TABLE ref2 (
    id INT PRIMARY KEY,
    test_id INT,
    FOREIGN KEY (test_id) REFERENCES test(id)
);

And guess what? When I checked the REFERENTIAL_CONSTRAINTS table again, the records magically appeared. Talk about inconsistency!

The Nail in the Coffin

To test things further, I added some data and attempted to delete rows from the "test" table. Brace yourselves for what I discovered next. MySQL allowed me to delete the first row without issuing any warnings. At first, I thought it was some kind of "cool" feature, but when I tried to delete the second row, which was referenced by the second table, it failed miserably.

-- Adding values for the test case
INSERT INTO test VALUES (1), (2);

-- Inserting a row in ref1 referencing the first row in the test table
INSERT INTO ref1(id, test_id) VALUES (1, 1);

-- Inserting a row in ref2 referencing the second row in the test table
INSERT INTO ref2(id, test_id) VALUES (1, 2);

-- Deleting the first row from the test table
DELETE FROM test WHERE id = 1;

-- Attempting to delete the second row, which is referenced by the second table
DELETE FROM test WHERE id = 2;

Houston, We Have a Bug!

As you can imagine, I was flabbergasted! It turned out that it wasn't a cool feature at all but a genuine BUG! MySQL lets you create tables using the short syntax without a care in the world, but those sneaky constraints are nowhere to be found, jeopardizing the consistency of your precious data. Can you believe it?

The Never-Ending Bug Story

Out of curiosity, I hit up Google and found out that this bug has been haunting MySQL since 2004 (https://bugs.mysql.com/bug.php?id=4919). And guess what? It's still not fixed! Seriously, MySQL team, what's going on over there?

Comparing with Other Databases

To ease my troubled mind, I decided to run the same test case on other databases using SQLize.online. Surprisingly, all the databases I tested (MariaDB, PostgreSQL, SQL Server, and Oracle) supported the short syntax and created foreign key constraints consistently. Kudos to them! However, MySQL and SQLite just had to be the odd ones out.

Conclusion

So there you have it, folks! The bug in MySQL that causes inconsistent foreign key constraints is a real headache. While the short syntax may seem tempting and convenient, it's essential to be cautious. Consider using the canonical syntax to ensure the reliability and integrity of your data.

As long as this bug remains unfixed, it's crucial to stay alert and explore workarounds or alternative solutions when dealing with foreign key constraints in MySQL. Knowing the limitations and quirks of your database system empowers you to make informed decisions and keep your data in good hands. Stay curious and happy coding!