AaronCrane.co.uk

MySQL and foreign-key support

[Previously published here.]

(This started out as a polite response on a mailing list to someone saying “You guys are out of date, MySQL is a pretty decent db now”. But it got a bit ranty, so I thought I’d put it here instead. Enjoy!)

Current releases of MySQL certainly have many of the checklist features of a real DBMS. However, this does not constitute a defensible claim that MySQL is a real DBMS.

MySQL still, after all these years, has a whole host of arbitrary limitations (like the limits on key length, and the inability to use TEXT columns in a foreign key, and the lack of recursion in stored functions). And there are still important features missing, whatever table engine you use (like CHECK constraints, and deferred constraint checking).

Worst of all, though, MySQL seems to deliberately make it hard to use the features it does have. A recent project needed transactions and foreign-key references in a MySQL 5.0 database (the current stable release, please note), so we used InnoDB tables. The situation was broadly equivalent to this:

CREATE TABLE actor (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE film (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE film_actor (
  film_id INT NOT NULL REFERENCES film,
  actor_id INT NOT NULL REFERENCES actor,
  PRIMARY KEY (film_id, actor_id)
) ENGINE=InnoDB;

Simple enough, yes?

No, actually. As it happens, using REFERENCES as a column constraint doesn’t work at all, and you also have to explicitly specify the primary-key columns of the referenced table. So the only permitted syntax for foreign keys is this (which you’ll notice is much more verbose than the obvious version):

CREATE TABLE film_actor (
  film_id INT NOT NULL,
  actor_id INT NOT NULL,
  PRIMARY KEY (film_id, actor_id),
  FOREIGN KEY (film_id) REFERENCES film (id),
  FOREIGN KEY (actor_id) REFERENCES actor (id)
) ENGINE=InnoDB;

So guess what happens when you use one of the naughty-but-convenient syntaxes?

That’s right — MySQL just silently ignores what you said! No error, no warning, just a blithe “sure, no problem mate, I’ll get right on it”, without actually doing what you asked.

Why, yes, this did cost me a day of debugging, thanks for asking.

I’m aware of MySQL’s propensity for handling compatibility requirements by ‘helpfully’ ignoring any bits of syntax that the engine doesn’t have the semantics for. But what made me so fucking angry about this was that the engine does have the semantics for this! How hard could it be to just do the right thing?

Hate.