Error message: Cannot delete or update a parent row: a foreign key constraint fails

Question

When executing my doDelete.php, this error message appears:

" Cannot delete or update a parent row: a foreign key constraint fails (fyp.book, CONSTRAINT book_user_key FOREIGN KEY (id) REFERENCES user (id) ON DELETE NO ACTION ON UPDATE NO ACTION) "

the Delete query:

$queryDelete = "DELETE FROM user  WHERE id = $theUserID";

the retrieve information query from 3 tables:

$query2 = "SELECT * FROM user,country,book where book.id=user.id AND user.country_id=country.country_id ORDER BY  `user`.`id` ASC";

The Relationship between my 'book' and 'user' table is that a user is able to add a book. Hence, the user ID is placed in the book table to identify which user adds a book. However when I want to delete a user from my php, the above error message appeared. Here's a snippet of the retrieval of user ID to delete the user. How do I fix this?

<td><form method="post" action="doDelete.php"><input type="hidden" name="theUserID" value="<?php echo $rows['id']; ?>" /><input type="submit" value="Delete" /</form></td>

Here is the relevant tables

CREATE SCHEMA IF NOT EXISTS `fyp` ;
USE `fyp` ;



-- -----------------------------------------------------
-- Table `fyp`.`country`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `fyp`.`country` ;

CREATE TABLE IF NOT EXISTS `fyp`.`country` (
`country_id` INT NOT NULL AUTO_INCREMENT,
`country` VARCHAR(45) NOT NULL,
PRIMARY KEY (`country_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `fyp`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `fyp`.`user` ;

CREATE TABLE IF NOT EXISTS `fyp`.`user` (
`id` INT NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`email_address` VARCHAR(45) NOT NULL,
`date_of_birth` DATE NOT NULL,
`country_id` INT NOT NULL,
`gender_id` INT NOT NULL,
`role_id` INT NOT NULL,
`last_login` TIMESTAMP NULL,
PRIMARY KEY (`id`),
INDEX `fk_user_country1_idx` (`country_id` ASC),
INDEX `fk_user_gender1_idx` (`gender_id` ASC),
INDEX `fk_user_role1_idx` (`role_id` ASC),
CONSTRAINT `user_country_key`
FOREIGN KEY (`country_id`)
REFERENCES `fyp`.`country` (`country_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `user_gender_key`
FOREIGN KEY (`gender_id`)
REFERENCES `fyp`.`gender` (`gender_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `user_role_key`
FOREIGN KEY (`role_id`)
REFERENCES `fyp`.`role` (`role_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `fyp`.`book`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `fyp`.`book` ;

CREATE TABLE IF NOT EXISTS `fyp`.`book` (
`book_id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(45) NOT NULL,
`ISBN` VARCHAR(45) NOT NULL,
`book_desc` VARCHAR(100) NOT NULL,
`year_published` VARCHAR(45) NOT NULL,
`year_of_birth` YEAR NOT NULL,
`image` VARCHAR(45) NULL,
`genre_id` INT NOT NULL,
`publisher_id` INT NOT NULL,
`user_id` INT NOT NULL,
 PRIMARY KEY (`book_id`),
 INDEX `fk_book_publishers1_idx` (`publisher_id` ASC),
 INDEX `fk_book_user1_idx` (`user_id` ASC),
 INDEX `fk_book_genre1_idx` (`genre_id` ASC),
 CONSTRAINT `book_publishers_key`
 FOREIGN KEY (`publisher_id`)
 REFERENCES `fyp`.`publishers` (`publisher_id`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `book_user_key`
 FOREIGN KEY (`user_id`)
 REFERENCES `fyp`.`user` (`id`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `book_genre_key`
 FOREIGN KEY (`genre_id`)
 REFERENCES `fyp`.`genre` (`genre_id`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
 ENGINE = InnoDB;

Answer 1

Indeed, you have a constraint in book table, that does not let you delete a user as long as there are books added by that user:

CONSTRAINT `book_user_key`
FOREIGN KEY (`user_id`)
REFERENCES `fyp`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION

You can change it to

ON DELETE CASCADE

so that deleting the user will delete all their books too.

Or you can set the constraint to

ON DELETE SET NULL

and remove NOT NULL from book.user_id: this will keep the books of the deleted user, but set their user_id to NULL.

Answer 2

You cannot create a user record because it has related records in book table. It is how foreign key constraint works. You created this constraint in this line:

FOREIGN KEY (`user_id`) REFERENCES `fyp`.`user` (`id`) 
ON DELETE NO ACTION
ON UPDATE NO ACTION

To solve this problem you can:

1. Delete records in book table for given user id before deleting a given user from user table. This will require executing 2 DELETE statements one by one.
2. Modify a foreign key and use ON DELETE CASCADE. It will cause that records in book table will be deleted together with related records in user table automatically.

I prefer solution 1 because it gives you more control. It is also less error prone because you have to always explicitly delete records from 2 tables. In the second case it is possible that you will accidentally delete a lot of data from your database.

Answer 3

I Believe you have 1 or more user_id (which you want to delete) data in Book table.

Since the user_id in Book table is a FOREIGN KEY to id in User Table, then any delete operation on User table will be restricted by the Constraint (book_user_key).

You can check whether the data is exist or not by using this query in SQL:

SELECT * FROM fyp.book WHERE user_id = ***[UserIDWhichYouWantToDelete]***

So the next thing is, what are you going to do if you already have that "User" data in Book table?

You can find more literature about FOREIGN_KEY on many website.