[wp-trac] [WordPress Trac] #19207: dbdelta supporting foreign key

[WordPress Trac]

#19207: dbdelta supporting foreign key
-----------------------------+------------------------------
 Reporter:  manutdotcom      |       Owner:
     Type:  feature request  |      Status:  new
 Priority:  normal           |   Milestone:  Awaiting Review
Component:  Database         |     Version:  3.2.1
 Severity:  normal           |  Resolution:
 Keywords:                   |     Focuses:
-----------------------------+------------------------------

Comment (by bpetty):

 I can certainly appreciate the power of foreign key constraints on data
 consistency and integrity, however, there's a few things that make the
 practice of using these difficult and result in less portability and
 flexibility, specifically with regard to WP.

 First I would like to point out that core WP tables would never use these
 itself. So this really only applies to plugins providing their own tables
 using them. I'm actually fairly certain this can still even be implemented
 in a plugin as `dbDelta()` contains the appropriate hooks to make this
 possible.

 There is a history of WordPress having gone from InnoDB to MyISAM and back
 for tables, and there may even still be a number of WP installations
 running MyISAM tables that don't even support these. We could detect this
 and avoid using them, but then what happens with code that relies on those
 constraints to update/delete rows? It's a situation that requires either
 using them 100% of the time, or never. Many would argue that it's already
 bad enough that WP only supports one database backend, this would also
 involve locking users out of the choice to make use of whatever DB table
 type works most efficiently for them. Up to this point, WP has remained
 flexible on this decision.

 When it comes to use cases, there's not a single one you could come up
 with that couldn't just be handled by enforcing that referential integrity
 with standard queries within your own code instead of letting the DB
 handle it. This certainly isn't as awesome as setting a constraint and not
 worrying about it, and can even be less efficient. It is more flexible
 though.

 Then you also have to consider special cases for `dbDelta()`. The purpose
 of this method is to fix any missing columns or indexes. If in the process
 of adding foreign key constraints, it's possible for it to fail if the
 data in those tables already fail to match those constraints, and we have
 no way to handle that situation from within the scope of `dbDelta()`. This
 can easily leave the table schema in a "broken" state if you're assuming
 those constraints have been added. This is yet another reason that
 maintaining those constraints manually is more flexible and reliable.

 So in short, I just don't think this is going to happen in core.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/19207#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform