ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails - (Connecting XML data to MySQL database)

Question

I am trying to connect an XML file with the MySQL database.

The query for table is

CREATE TABLE Patient (
    IDNo INT PRIMARY KEY,
    F_Name VARCHAR(255),
    L_Name VARCHAR(255),
    Allergies VARCHAR(255),
    Address VARCHAR(255),
    BloodType VARCHAR(10),
    PatiantTeleNoID int,
    BedID INT,
    EmpNo INT,
    FOREIGN KEY (BedID) REFERENCES Location(BedID),
    FOREIGN KEY (EmpNo) REFERENCES Staff(EmpNo)
);

The code snippet for XML code is

<PatientData>
  <Patient>
      <IDNo>6</IDNo>
      <F_Name>Susan Smith</F_Name>
      <L_Name>Susan Smith</L_Name>
      <Allergies>Aspirin</Allergies>
      <Address>45, Temple Rd, Haputhale</Address>
      <BloodType>O+</BloodType>
      <PatiantTeleNoID>66677788</PatiantTeleNoID>
      <BedID>3</BedID>
      <EmpNo>6</EmpNo>
  </Patient>
</PatientData>

I entered the following snippet to MySQL Command line Client,

LOAD XML
INFILE "E:/ ---path--- /patient_data.xml"
INTO TABLE suwapiyasa.Patient
ROWS IDENTIFIED BY '<Patient>';

But I get the following error when entering the code in MtSQLCommand Line Client.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (suwapiyasa.patient, CONSTRAINT patient_ibfk_2 FOREIGN KEY (EmpNo) REFERENCES staff (EmpNo))

Please help me to solve this issue. I so appreciate your hints.

You are trying to insert a patient with a EmpNo that has no record in the staff table — Siebe Jongebloed, Sep 02 '23 at 11:54
Foreign Key means you have another table in database that uses the same key. The error is linking the two tables. You only posted one table. — jdweng, Sep 02 '23 at 14:55

Tharindu · Answer 1 · 2023-09-02T17:14:38.953

Thank you guys. The problem is that I had added data to foreign keys, too. When I removed the data of BedID and EmpNo in XML, the error was solved.

   <PatientData>
      <Patient>
          <IDNo>6</IDNo>
          <F_Name>Susan Smith</F_Name>
          <L_Name>Susan Smith</L_Name>
          <Allergies>Aspirin</Allergies>
          <Address>45, Temple Rd, Haputhale</Address>
          <BloodType>O+</BloodType>
          <PatiantTeleNoID>66677788</PatiantTeleNoID>
      </Patient>
    </PatientData>

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails - (Connecting XML data to MySQL database)

1 Answers1