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:
| id | title | content | category_id |
| 1 | test1 | testing | 1 |
| 2 | test2 | testing | 1 |
| . | ….. | ……. | . |
| 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:
//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