Integrity Constraints

The problem with MySQL is that only a few installations default to innodb. Most use MyISAM tables because they are more “beginner friendly”. Ive seen other hosts that dissallow innodb tables because they don’t understand how to include the tables file size in the websites data quota.

Because of this WordPress, and pretty much every other PHP-based CMS that I’ve seen out there, has to think that it will be using the lowest common denominator, so MyISAM tables with no references are required so it can be as universal as possible.

I haven’t seen a script that will go through and change the tables to innodb and add in the constraints, but with the standard DB Tables it’s not hard to do. The relationships are pretty well defined so setting my foreign keys shoud be relativey easy.

I will add that the WP system has been set up to use the PHP code to enforce the integrity where it can. That’s why it’s always best to use the built-in WordPress functions rather then trying to roll your own.

Thanks very much for your informative response! It sort of answers another question I’ve had, too, which is why WordPress was so tightly tied to one particular database implementation.

If I can create a script that upgrades the database with constraints, I’ll post it somewhere in case there is interest. I guess I’d probably use “on cascade delete” qualifications also, to make sure that deletes aren’t broken in software that assumes no keys.

Cheers.

Actually, for just the WordPress core, I think it might be simply this:

/** ALTER TABLE wp_posts ADD CONSTRAINT FOREIGN KEY (post_parent) REFERENCES wp_posts(ID) ON DELETE CASCADE; **/
ALTER TABLE wp_posts ADD CONSTRAINT FOREIGN KEY (post_author) REFERENCES wp_users(ID) ON DELETE CASCADE;
ALTER TABLE wp_postmeta ADD CONSTRAINT FOREIGN KEY (post_id) REFERENCES wp_posts(ID) ON DELETE CASCADE;
ALTER TABLE wp_usermeta ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE;
ALTER TABLE wp_term_taxonomy ADD CONSTRAINT FOREIGN KEY (term_id) REFERENCES wp_terms(term_id) ON DELETE CASCADE;
ALTER TABLE wp_term_relationships ADD CONSTRAINT FOREIGN KEY (term_taxonomy_id) REFERENCES wp_term_taxonomy(term_taxonomy_id) ON DELETE CASCADE;

The first one is commented out because post_parent column is marked as NOT NULL, with a DEFAULT of 0, rather than null. I’m not sure if any code depends on that zero value.

I’m using dozens of plugins, including BuddyPress. Maybe it can become a WordPress “good practice” to include an option for adding integrity constraints to your plugin, an option when you install, if InnoDB is currently being used?

It’s a whole lot eaiser (and better practice) for plugin and theme authors to take the time to do the correct coding to look after this without needing any DB constraints. as I said before, we’re looking at the lowest-common-denominator, so doing this in code correctly will ensure that it’s done all of the time, where as adding constraints only if the DB tables are onnodb would only work part of the time. As bad as it sounds it’s relaly up to the quaility of the plugins/themes that you’re uisng to take care of this themselves.

As for the 0 as ‘post_parent, that’s the indication to the system that that post/page/etc is a top-level post (no parent). The system uses this mainly in building menus but also in a few other queries that are done, so it is pretty important to have that there.