Posted by bjaspan on June 12, 2007 at 1:48am
Once foreign keys are specified during table creation, we will have to make sure to create tables in the correct order. e.g. You can't create system.schema, whose files table has a uid column, before you create user.schema which creates the users table which hold the master uid column.
If this proves too difficult we can use ALTER TABLE to put the foreign key contraints in afterward, but that seems much less desireable.
Comments
Good points
Of course, we have the option of tracking foreign key relationships in the API without actually implementing in the DB, but that would be a lesser solution.
Likely we could learn from the CiviCRM experience introducing foreign key constraints. It raised a host of issues, e.g. with upgrades.
Handling this in core is one thing (there we have the dubious option of rigging the order of table installs), but really we'll need to rethink our module install process. Currently each schema is installed sequentially according to the module install order, which of course could break foreign key references. We have two issue--(a) ensuring the correct order within a given module's schema and (b) ensuring that modules are installed in the correct order. For the first we could (somewhat hackishly) require that module authors specify their tables in an appropriate order. But not for the second.
A recent change to the D6
A recent change to the D6 code ensures that modules are installed in dependency order, so if module foo uses a key in the bar module's table, and it makes the bar module a dependency (which it would have to), bar will be installed before foo.
I think that takes care of install order, but changes made during updates would be a different issue. Currently updates happen in random order (not alphabetic, not by weight) so if you are adding a key during an update that depends on something else that might happen in an update, you'd have a real mess. So we need to get the updates happening in dependency order, too.
issue for "updates not in order"?
do we have an issue for "updates not run in order"? that seems like a critical bug to me. if i depend on module foo, i expect foo to be working during my update and if its update hasn't run it can't be relied upon.
I've mentioned it in several
I've mentioned it in several places, but I guess it really should be an issue. It came up when I was working on the CCK D6 port and found things happening in unexpected ways. I mentioned it in http://drupal.org/node/198508 and http://drupal.org/node/208576, and http://drupal.org/node/209041 and probably other places but what I did not do was create an issue for it :(
I'll go do that.
I posted an issue at
I posted an issue at http://drupal.org/node/211182 but it sounds like no one thinks it is a problem that they don't run in order.
More than that
I don't think Drupal architecture (or part of it) is currently suitable to use foreign keys in the db. An example of this is the profile module that creates the profile_values table that has uid as 'foreign key'. If you create a user with the profile module enabled, then later you decide to disable profile module. Unless we have 'ON DELETE CASCADE' features on the foreign key, deleting users would break data integrity.
Another example would be CCK. If you use CCK to extend page node type to include additional fields, then later you decide to disable CCK module, and delete a page node...
I believe there are many ways around this
for example: remove foreign keys on module disable and verify the integrity before adding of keys when module enabled once again. It's pretty than have user profiles without users.
This requires some attention but possible even in 6.x version via HOOK_enable / _disable