Skip to:
Content
Pages
Categories
Search
Top
Bottom

Re: Accidently emptied the wp_users table


Jeff Sayre
Participant

@jeffsayre

Should emptying the wp_users table ideally not also delete the connected user data in all the other tables, including xprofile? Or is that not how MySQL works?

No. The way the MySQL tables are set up for the *presses, is that there is no referential integrity. So, if you go into the DB’s backend and change a record or delete a record, it does not cascade the changes to sibling or child tables.

For example, assuming that you were allowed to do this by the table’s schema, if you changed the user ID in the wp_users table, it would not automatically update that ID in all other related tables to reflect the new ID number. Of course, the record ID fields in the *presses are automatically incremented, but you get the point.

Up until recently (past several years or so?), MySQL did not even offer the option of setting up tables with referential integrity. Now it does in a less-than-desirable way. It is not as simple to do in MySQL as it is in PostgreSQL, but it is straight forward enough. It requires that the tables have foreign keys and are created with the Type = InnoDB extension.

Personally, I’m a big PostgreSQL fan, but I do not want to start a PGsql vs MySQL flame war. If you use the *presses, you use MySQL. It’s as simple as that.

But, referential integrity is still not usually set up in many MySQL DBs. So, this is nothing peculiar to the *presses. Why? Because the developers make sure that the code takes care of the updates and cleanup.

This is one, of many, reasons why you really need to know what you are doing in the DB’s backend.

Skip to toolbar