Yummy
The reading is thick to the point that you can chew it into a grainy pulp, but I’m really convinced that foreign key constraints are da shizzle, as they say.
“ON DELETE CASCADE” and “ON UPDATE CASCADE” says most of the SQL you traditionally write for webapps goes away.
For the layperson [sane person?], let’s say you have the following tables (comma-separated fields):
Domains (domain)
bloodmoney.net
antesonic.org
Users (name, domain, password)
brandon, antesonic.org, pw
brandon, bloodmoney.net, pw
somebody, bloodmoney.net, pw
Let’s say field #2 in Users (the ‘domain’ field) was given a foreign key constraint to ‘domain’ in Domains. If you tried to change a domain in Users from bloodmoney.net to brandon.com, the update would fail because it didn’t match a domain in Domains. In real life, you would never want a query like this to be successful, but in many less-advanced databases (as in, Access forevermore and MySQL before 5.0) would dumbly allow this, corrupting your data in a big way.
If you have ON UPDATE CASCADE, if you changed antesonic.org to antesonic.net, it would change in Users. This is normally handled with program logic.
In fact, this was one of the biggest problems in SUM, and one of the main reasons I resisted allowing deletion; if you killed a “type of material,” what happened to all the material of that type? If you had ON DELETE CASCADE, it would be deleted automatically and there would be no inconsistencies in the database.