WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 4 years ago

#19207 closed feature request (maybelater)

dbdelta supporting foreign key

Reported by: manutdotcom Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.2.1
Component: Database Keywords: needs-patch
Focuses: Cc:

Description

Is there a plan to include support for foreign key inside dbdelta function?

Change History (4)

#1 @stephenh1988
7 years ago

  • Cc contact@… added

#2 @dd32
6 years ago

  • Component changed from Upgrade/Install to Database

#3 @bpetty
5 years ago

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.

#4 @chriscct7
4 years ago

  • Keywords needs-patch added
  • Milestone Awaiting Review deleted
  • Resolution set to maybelater
  • Status changed from new to closed

Closing as maybelater. Complete lack of interest on the feature on the ticket over the last 2 years, as well as alot of drawbacks to the proposal as pointed out in comment:3. Feel free to reopen when more interest re-emerges (particularly if there's a patch).

Note: See TracTickets for help on using tickets.