Assuming the foreign keys patch to the schema API makes it in, there are
four ways we can approach foreign keys in Drupal 6.

1. Don't use them at all. Just provide the schema API for contrib
modules with higher database requirements than core.

This is the path of least resistance, but Dries said that he would consider
referential integrity stuff, so we should try.

2. Configure foreign keys as RESTRICT on delete. This will have no
effect on MyISAM, but it will improve code quality because operations
creating inconsistency would fail. Such operations would then be
rewritten to manipulate the database in a consistent way.

This seems the most sensible given the state of affairs (MyISAM, ...etc.).

3. Configure foreign keys as CASCADE on delete and have extra PHP run
whenever a query manipulates a database using MyISAM. Basically,
whenever a DELETE query gets sent to a MyISAM system, we would convert
it to a SELECT query to identify the rows that would be deleted. Then,
we would delete the records attached with foreign keys. Finally, we
would run the original DELETE query. This would have to be recursive.

This is moving the database layer work into application code, which is not
a good thing.

4. Configure foreign keys as CASCADE on delete and have modules
conditionally run queries in their deletion hooks depending on the
storage engine. If a module doesn't check the storage engine type, the
DELETE it runs would simply be redundant.

Same as my comment on #3.

Personally, I think #2 would be a nice first step in Drupal 6. It would
at least force us to clean up the database interaction so #3 or #4 could
be seamlessly added later.