Years ago, the mysql folks had an essay online explaining why foreign key constraints belong in your code and not in your database. I can't find a copy of that essay any more, but it's an excellent symptom of so many things which have plagued this database.
One problem many people face is how you cannot have both auto-updating "created" timestamps and "modified" timestamps in the same table definition (thanks MySQL!). Solutions:
All of those are generally awful solutions, but we've opted for number 2, setting them in our code, because MySQL triggers have proven so problematic. That has led to some interesting problems.
We've had tables where somehow these values have either not been set or not been updated. How did this happen? Who knows? The problem seems to have gone away, but it's potentially there.
We now have the annoying problem where another client has read-only (thank goodness for small favors) access to our database. We have several tables with "titles" (brands, series, episodes, etc.) and we need to remove those and put them into a central "title" table. Unfortunately, this other client is maintained by another team who cannot update their client due to a major release coming out.
The classic strategy for this is to keep both sets of titles and merely have the "title" table populated by triggers on the other tables (see the Move Column database refactoring). This is a temporary solution, but it's introduced some very subtle issues. Initially we had triggers just like this (with corresponding UPDATE and DELETE triggers):
CREATE TRIGGER tr_brand_insert AFTER INSERT ON brand
FOR REACH ROW BEGIN
INSERT INTO title (entity_pid, title_type_id, value, created, modified)
VALUES(NEW.pid, 1, NEW.title, now(), now());
END;
Our tests failed. We were getting annoying errors about trying to set "NOT NULL" columns to a NULL value. MySQL thoughtfully reported the SQL which triggered the trigger, but not the trigger itself. I found myself staring at SQL which did not have the column which MySQL reported caused the problem (thanks MySQL!). Fortunately, since I'm working on this now, it was quickly obvious what was happening. (This is what led me to cursing earlier today).
Resolving that meant I had to rewrite the above trigger. The problem is that it's possible to have an empty string for a title (long story). So the above trigger became this:
CREATE TRIGGER tr_${table}_insert AFTER INSERT ON $table
FOR EACH ROW BEGIN
IF COALESCE(NEW.title, '') != '' THEN
INSERT INTO title (entity_pid, title_type_id, value, created, modified)
VALUES(NEW.pid, 1, NEW.title, now(), now());
END IF;
END;
As soon as you start inserting conditional logic into your database, you're probably introducing bugs. Sure enough, it happened when we tried to update a title by setting it to a non-null value. Our update trigger would try to update a non-existent row. That's when one of my colleagues pointed out an elegant solution. MySQL allows INSERT ... ON DUPLICATE KEY UPDATE.... This is very handy. Try to INSERT a record and if it violates a unique constraint, it will turn the INSERT into an UPDATE.
Except that doesn't work. Remember the created/modified restriction that MySQL imposes? Because we handled this in our code, when we INSERT a record, we set both the "created" and "modified" columns. If that INSERT gets switched to an UPDATE, then we'll also wind up updating our "created" column. Grr ...
As a result, that nice, simple UPDATE trigger (there are actually 10 variations of this) has become this:
CREATE TRIGGER tr_brand_update AFTER UPDATE ON brand
FOR EACH ROW BEGIN
IF COALESCE(NEW.title, '') != '' THEN
IF EXISTS(SELECT 1 FROM title WHERE entity_pid=OLD.pid) THEN
UPDATE title SET value=NEW.title
WHERE entity_pid=OLD.pid AND title_type_id=1 AND value=OLD.title;
ELSE
INSERT INTO title (entity_pid, title_type_id, value, created, modified)
VALUES(NEW.pid, 1, NEW.title, now(), now());
END IF;
END IF;
END;
So far, the tests are passing, but a combination of MySQL limitations and issues in our code have taken a relatively simple problem and made it a real headache.
Update: And if you can spot the three bugs I missed, you're a better MySQL programmer than I am :)
Can you convert the old tables to back-compatibility views on the new table(s)?
BTW, that essay was in the actual MySQL documentation. And it was removed, of course, once foreign key constraints were added.
Re:If they are readonly...
Ovid on 2009-02-25T10:21:28
We have no desire to add views to provide backwards-compatibility. This should (usually) be considered a "last resort" because otherwise, you layer on view after view every time you want to change a feature and thing become a mess. Plus, they're willing to make the switch, just not yet.
that essay was in the actual MySQL documentation. And it was removed, of course, once foreign key constraints were added.
The Wayback Machine to the rescue. It's even worse than I remember it:
There are so many problems with foreign key constraints that we don't know where to start:
- Foreign key constraints make life very complicated ...
Re:Missing Essay
Ovid on 2009-02-25T10:25:08
Thanks Smylers. Wow, I remember that essay being:
- Longer
- Not so full of the Dumb
I really like the bit about:
FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order
.
But they don't totally trash foreign keys! In fact, they make it clear that their is one (and only one) good reason for them.
The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons (sic).
Thanks for starting my day off with a laugh
:) Re:Missing Essay
Aristotle on 2009-03-06T15:39:37
I remember that essay being longer and not so full of the Dumb
That’s probably because there also exists a later revision of that page that stayed around for much longer. The first, short, really stupid version lasted 10 months before it was replaced with the tempered version that stayed around for 4 years.