Update results, Before deleting the related category

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 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?

Read more here:: Update results, Before deleting the related category

Leave a Reply

Your email address will not be published. Required fields are marked *