[development] Referential integrity -- finally?
morbus at disobey.com
Sat Jan 20 00:00:46 UTC 2007
> In other words: I'd happily accept patches that add foreign keys to
> Drupal tables. It woud not be perfect as we can't use cascading
> deletes, for example. Nevertheless, it would be a great step in the
> right direction -- assuming we can make it all work in a SQL-compliant
> way without leaving MyISAM users in the cold.
I'm personally not a fan of it, primarily because it caused me headaches
in the past. I had to deal with a rather large import to CiviCRM, which
is all InnoDB and foreign keys and, for speed reasons (2 million
records), could not use their API. This forced me to add the records to
the tables manually. And, since the import data was always changing, I
had to code the importer in such a way that it could start over - delete
all the import data and prepare the database for a fresh import. Foreign
keys added tons and tons of time to this - both for the import (though,
I have no true facts to back this up) and for the actual deletion, since
the rows had to be deleted in quite a specific way, otherwise the
foreign keys would complain about splintered data.
Likewise, all the foreign keys in CiviCRM actively caused lookups to
slow down tremendously - for a 2 million person database (with roughly
three tables having 2 million rows, and one having nearly 10 million), I
eventually had to remove all the foreign key constraints (along with a
number of other optimizations that were contributed back to dlobo and
To some degree, "referential integrity" sounds all awesome and "yay,
save me from myself" but, really, Drupal's been working just fine
without it, and I see no huge reason to just "add it on".
And, for the sake of those not on infra, my comments from there:
For what it's worth, I'm not a fan of InnoDB myself. A default
configuration of InnoDB uses a single database file that can grow quite
large (2GB+ for one of my CiviCRM installations), and then there's all
the index-y and cache tables (whose purpose I never really grasped).
Unless you have binary logging turned on (which, in turn, create more
files), you have a single point of failure. Single points of failures
are never a good thing. Yes, there's a configuration option in newer
MySQLs to force InnoDB back to one file per table (as per MyISAM).
Still, the likelihood of any of our common users being able to tweak
that IS NULL.
Morbus Iff ( i still fail to see what this has to do with morocco )
Culture: http://www.disobey.com/ and http://www.gamegrene.com/
aim: akaMorbus / skype: morbusiff / icq: 2927491 / jabber.org: morbus
More information about the development