[development] Foreign keys in Drupal 6

[Barry Jaspan]

David previously listed four options for using foreign keys in Drupal
on this mailing list.  I previously voted in favor of making foreign
keys RESTRICT on delete so the database simply refuses to delete a row
that is a foreign key of another table row.

I now realize that isn't possible.  When we disable a module, we do
not delete its tables, but we stop calling its hooks.  e.g. In
RESTRICT mode, if we disable the book module, it becomes impossible to
delete any node previously in a book because the book table will have
a row with a foreign key into the node table for that node.  Not good.
(Incidentally, this also reveals that node_delete() will need to call
nodeapi('delete') before deleting the row from the node table.)

This leaves us to choose between not using foreign keys at all or
using CASCADE on delete.  If we use CASCADE and rely on it to delete
rows, we will have to implement the cascading-delete logic within
Drupal itself for engines that do not support it.  I don't think we
want to deal with this.  Also, it will require using an abstract API
for all DELETE SQL queries which many here have opposed for anything
but DDL statements.

Frankly, the more I think about it, the more I realize referential
integrity is just not doable for Drupal 6.  However, if we still want
to try, my suggestion is:

1.  We set foreign keys as CASCADE on delete.

2.  We specify that modules MAY NOT depend on the CASCADE deletion;
they must continue to execute their own DELETE queries, because the db
engine may not support CASCADE deletion.  The DELETE queries will be
redundant but harmless (deletion is infrequent enough not to be a
performance issue, right?).

3.  On engines that do support CASCADE deletion, the fact that we are
using CASCADE means that deleting nodes or other objects referenced by
disabled modules will continue to work; the dependent rows will be
deleted.  On non-supporting engines, the rows created by disabled
modules will continue to exist, but that is just status quo.

Comments?

Barry