There is a DB that contains 2 tables
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 | | . | ..... | |______|________|
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
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
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] );
$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?