ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

user3289677 picture user3289677 · Feb 9, 2014 · Viewed 570.2k times · Source

I have created tables in MySQL Workbench as shown below :

ORDRE table:

CREATE TABLE Ordre (
  OrdreID   INT NOT NULL,
  OrdreDato DATE DEFAULT NULL,
  KundeID   INT  DEFAULT NULL,
  CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID),
  CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES Kunde (KundeID)
)
  ENGINE = InnoDB;

PRODUKT table:

CREATE TABLE Produkt (
  ProduktID          INT NOT NULL,
  ProduktBeskrivelse VARCHAR(100) DEFAULT NULL,
  ProduktFarge       VARCHAR(20)  DEFAULT NULL,
  Enhetpris          INT          DEFAULT NULL,
  CONSTRAINT Produkt_pk PRIMARY KEY (ProduktID)
)
  ENGINE = InnoDB;

and ORDRELINJE table:

CREATE TABLE Ordrelinje (
  Ordre         INT NOT NULL,
  Produkt       INT NOT NULL,
  AntallBestilt INT DEFAULT NULL,
  CONSTRAINT Ordrelinje_pk PRIMARY KEY (Ordre, Produkt),
  CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID),
  CONSTRAINT Ordrelinje_fk1 FOREIGN KEY (Produkt) REFERENCES Produkt (ProduktID)
)
  ENGINE = InnoDB;

so when I try to insert values into ORDRELINJE table i get:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (srdjank.Ordrelinje, CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID))

I've seen the other posts on this topic, but no luck. Am I overseeing something or any idea what to do?

Answer

Rahul picture Rahul · Feb 9, 2014

Taken from Using FOREIGN KEY Constraints

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

So your error Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails essentially means that, you are trying to add a row to your Ordrelinje table for which no matching row (OrderID) is present in Ordre table.

You must first insert the row to your Ordre table.