How to resolve ERROR 1452 (23000) in mysql

Question

I’m trying to popular the following database:

DROP DATABASE IF EXISTS library;

CREATE DATABASE library;

USE library;

CREATE TABLE AUTHOR (
  NAME VARCHAR(50) NOT NULL,
  COUNTRY VARCHAR(50) NOT NULL,
  TITLE VARCHAR(50) NOT NULL,
  PRIMARY KEY (NAME,TITLE)
) ENGINE = INNODB;

CREATE TABLE BOOK (
  TITLE VARCHAR(50) NOT NULL,
  ISBN CHAR(10) NOT NULL,
  PUBLISHER VARCHAR(50) NOT NULL,
  SUBJECT VARCHAR(50) NOT NULL,
  PRIMARY KEY (ISBN)
) ENGINE = INNODB;

CREATE TABLE WRITES (
  NAME VARCHAR(50) NOT NULL,
  TITLE VARCHAR(50) NOT NULL,
  ISBN CHAR(10) NOT NULL,
  PRIMARY KEY (NAME,ISBN)
) ENGINE = INNODB;

ALTER TABLE WRITES ADD CONSTRAINT FK_BOOK_WRITES
  FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN)
  ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE WRITES ADD CONSTRAINT FK_AUTHOR_WRITES
  FOREIGN KEY(NAME) REFERENCES AUTHOR(NAME)
  ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Dorothy King","England","Springtime Gardening");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jon Nkoma","Kenya","Birds of Africa");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Won Lau","China","Early Tangs Paintings");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Bert Kovalsco","United States","Baskets for Today");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Tom Quercos","Mexico","Mayan Art");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jimmy Chan","China","Early Tangs Paintings");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Dorothy King","England","Autumn Annuals");
INSERT INTO AUTHOR (NAME,COUNTRY,TITLE) VALUES("Jane East","United States","Springtime Gardening");

INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Springtime Gardening","0816354219","Harding","Nature");
INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Early Tangs Paintings","036487547X","Ballman","Art");
INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Birds of Africa","0115012141","Loraine","Nature");
INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Springtime Gardening","0560001428","Swift-Key","Nature");
INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Baskets for Today","0816537054","Harding","Art");
INSERT INTO BOOK (TITLE,ISBN,PUBLISHER,SUBJECT) VALUES("Autumn Annuals","0816885060","Harding","Nature");

INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jimmy Chan","Early Tangs Paintings","036487547X");
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Dorothy King","Autumn Annuals","0816885060");
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jane East","Springtime Gardening",0560001428);
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Bert Kovalsco","Baskets for Today","0816537054");
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Won Lau","Early Tangs Paintings","036487547X");
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Jon Nkoma","Birds of Africa","0115012141");
INSERT INTO WRITES (NAME,TITLE,ISBN) VALUES("Dorothy King","Springtime Gardening","0816354219");

But I get following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`library`.`WRITES`, CONSTRAINT `FK_BOOK_WRITES` FOREIGN KEY (`ISBN`) REFERENCES `BOOK` (`ISBN`))

There’s already a similar question, but it didn’t help me much.

Answer 1

In reality there is a "gambiarra" that you can do, but if there is error of constraint your data will not be intact in relation to foreign key, but to remedy your problem and at least you can enter your data, disable the foreign key check at the beginning of your script and enable again at the end, that way.

Disable : SET FOREIGN_KEY_CHECKS = 0;

Enable: SET FOREIGN_KEY_CHECKS = 1;

I don’t advise doing this, but it solves your problem for now.