1

There is a DB that contains 2 tables posts and categories, Each category could contain many posts, So I'm using one-to-many relation between the two tables.

Using the id of the categories and a column in the posts table that should be an integer that refers to the category.

Here is how the two tables look like:

           posts table
______________________________________
| id | title | content | category_id |                                       
|____|_______|_________|_____________|
| 1  | test1 | testing |     1       |   
| 2  | test2 | testing |     1       |   
| .  | ..... | ....... |     .       |   
|____|_______|_________|_____________|


       categories table
       _________________
       | c_id | c_name |                                       
       |______|________|
       |   1  |  cat1  |     
       |   2  |  cat2  |        
       | .    | .....  |       
       |______|________|

I'm using c_id from the categories table and the category_id in the posts table to make the relation, So if a post has category_id = 1, Then it belongs to cat1.

Currently ON DELETE => CASCADE and ON UPDATE => CASCADE.

So when a category is deleted, the related posts are deleted.

I want to change that not to lose the posts, And instead move the results to another category called uncategorized with c_id = 100.

So I tried:

global $wpdb;

//Update the posts that related to the deleted category.
$wpdb->update( 'posts', ["category_id" => 100], ['category_id' => $remove_id] );

//Delete the category
$wpdb->delete( 'categories', ['c_id' => $remove_id] );

Where $remove_id is the id of the category I want to delete.

But when I try to delete a category, I get the following error:

WordPress database error: [Cannot add or update a child row: a foreign key constraint fails 

(`myposts`.`posts`, CONSTRAINT `posts_categories` FOREIGN KEY (`category_id`) REFERENCES 

`categories` (`c_id`) ON DELETE CASCADE ON UPDATE CASCADE)]
UPDATE `posts` SET `category_id` = '100' WHERE `category_id` = '2'

What should I do to update the category_id, Before deleting the category?

  • 1
    This is not really a WP question, so it's off topic here. Just to help you out: ON DELETE => CASCADE works on one-to-many relationships, not on many-to-many relationships. If a single post can have multiple categories and a category can have multiple posts, then that's not a one-to-many relationship. – Fayaz Aug 21 '18 at 5:39