Kleidophobia
The old Greek word for “key” was KLEIS so someone who fears keys would be a kleidophobe, and a xenokleidophobe specifically fears foreign keys, which brings me to the MySQL 3.23 manual …
5.4.5.1 Reasons NOT to Use Foreign Keys constraints
There are so many problems with foreign key constraints that we don’t know where to start:
Foreign key constraints make life very complicated …,
The speed impact is terrible …,
There is also a need to hold locks on many more tables …,
And so on.
And so on.
Of course those fierce words quietly disappeared from the manual some time after InnoDB came out with foreign key support in 2001. But since then the attempts to get foreign key support outside of InnoDB have usually gotten stuck.
* SolidDB had it, and I made a big deal out of that while predicting a bright future for the SolidDB storage engine.
* Falcon had it, although there were bugs in the implementation (they’re marked “to be fixed later”).
* PBXT had it
* IBMDB2I had it.
All of those storage engines are, for one reason or another, gone. So, is any independent storage engine left that supports foreign keys? I know of only one: ScaleDB. ScaleDB is targeted for clusters, and I’m not sure how much traction its makers are getting in that market, but I’m always joyous when I see a company paying attention to the standard feature requirements.
Anyway, to finish our look at MySQL documentation on the matter, I’ll quote once more:
“Foreign key support generic for all storage engines is scheduled for MySQL 6.x.”
This ancient promise is still in the current manual but I predict it too will silently disappear. The promise originally was made because there was progress on a project to do a lot for foreign keys, and the detailed plan is still publicly available on the mysql.com page for Worklog Item #148 (click the “High Level Architecture” button to view the important part). Though the project got put on the shelf, the worklog document is still good for seeing what, at the time, everyone agreed needed work.
The document’s “Feature Comparison Chart” compared InnoDB feature details with the ISO standard, SQL Server, DB2, Oracle, and PostgreSQL. It’s a long list. I’ll just pick the five standard “core” items where every vendor follows the standard. Except InnoDB.
1. References Primary/Unique
InnoDB lets foreign keys refer to keys that are not primary or unique.
For example:
CREATE TABLE pk (s1 CHAR, KEY (s1), s2 INT);
CREATE TABLE fk (s1 CHAR, FOREIGN KEY (s1) REFERENCES pk(s1));
INSERT INTO pk VALUES ('a',1),('a',2);
INSERT INTO fk VALUES ('a');
So now there’s a foreign key that points to two different parent-key rows. Guess what happens if you delete only one of the referenced-key rows, thus:
DELETE FROM pk WHERE s2 = 2;
If you guessed “that’s illegal because you can’t delete a row if there’s a foreign-key referring to it”, congratulations.
If you guessed “that’s legal because the foreign key is still referencing a valid undeleted row”, congratulations.
Either answer is logical, and it doesn’t matter which one is right.
What matters is that neither logic nor precedent are of any help when InnoDB goes boldly beyond both theory and common practice.
2. Default Primary Reference
It ought to be possible to just say that the foreign key references table PK, and not have to list the columns — if PK has a primary key, the DBMS can figure it out. But, partially as a result of the previous problem, InnoDB doesn’t figure it out.
3. Statement Check
Sometimes a check for a foreign-key violation should occur when the statement is truly over, when all other changes (due to triggers or cascading updates) have finished, so that the check isn’t affected by some temporary situation that would be cleared up before the statement ended.
4. Column REFERENCES
This looks okay, and there’s no error or warning:
CREATE TABLE pk (s1 INT); CREATE TABLE fk (s1 INT REFERENCES pk(s1));
… but, woe to the uninitiated, it doesn’t make a foreign key. You can’t make a foreign key by putting a REFRENCES clause in the column definition.
5. REFERENCES Privilege
I suppose that newcomers might also try this:
GRANT REFERENCES ON t TO joe;
and think that they’re affecting security for foreign keys. In fact they’re creating a privilege that does nothing.
The current situation is less than perfect, and I cast the blame on all the closet xenokleidophobes. You know who you are.
Lack of deferred key checking has been a big problem with InnoDB’s implementation. Many people come from worlds where they can interact with several tables during a transaction and expect that referential integrity will be checked at commit time.