Delete post results in "Cannot delete or update a parent row: a foreign key constraint fails"

Question

I am using wordpress 4.9.6 with mysqlnd 5.0.12-dev as db. I have created my own tables which uses post_id as a Foreign Key Constraint.

When I want to delete a post in my backend I get:

WordPress database error: [Cannot delete or update a parent row: a foreign key constraint fails (`my_own_database`.`wp_product`, CONSTRAINT `wp_product_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `wp_posts` (`ID`))]
DELETE FROM `wp_posts` WHERE `ID` = 300

Error in deleting.

The table I am referencing the post_id to looks like the following:

CREATE TABLE wp_product(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    post_id BIGINT(20) UNSIGNED,
    product_id BIGINT(20) NOT NULL,
    daily_netProfit DECIMAL(30,15) NOT NULL,
    daily_grossProfit DECIMAL(30,15) NOT NULL,
    daily_costs DECIMAL(30,15) NOT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (post_id) REFERENCES wp_posts(ID),
    FOREIGN KEY (product_id) REFERENCES {$wpdb->prefix}products(id)
);

Any suggestions how to delete the post, when pressing the delete button in my backend?

I appreciate your replies!

Answer 1

Your MySQL table structure creates a reference and constraint relationship between your wp_product table and wp_posts. MySQL is doing exactly what you have instructed it to do. In this case, preventing you from deleting a record in wp_posts that is 'related' to records in your wp_product table.

In a nutshell, this is what foreign keys are supposed to do; maintain data integrity. Therefore you cannot delete a row in wp_posts while a corresponding/related record exists in wp_product.

I'd suggest taking a look at the MySQL documentation on Using FOREIGN KEY Constraints. Per your comments below I'd suggest looking at 'Referential Actions' to set the action to CASCADE.

Review the linked documentation to drill down to the specifics that match your requirements.