SQL Error 02291 - Issues with foreign keys

Question

We are trying to insert data to our tables, however we have run into an error and can't see the problem. This is what we are getting -

INSERT INTO Item(Manifest_barcode,Trip_ID,Item_weight,Pickup_customer,Delivery_customer,Category) VALUES (159601450,73495,2156,166,184,'A') Error report - SQL Error: ORA-02291: integrity constraint (HR.SYS_C009055) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"

We have checked the order of the creation of the tables, and as far as we can tell everything is in the right place. All of the foreign keys seem to be correct as well.

This is how we are dropping/creating the tables -

DROP TABLE Item;
DROP TABLE Trip;
DROP TABLE Vehicle;
DROP TABLE Vehicle_Type;
DROP TABLE Employee;
DROP TABLE Customer;
DROP TABLE Category;

CREATE TABLE Category(
    Category VARCHAR2(100) NOT NULL,
    Description VARCHAR2(100) NOT NULL,
    Requirements VARCHAR2(100),

    PRIMARY KEY(Category)
);

CREATE TABLE Customer(
  Reference INT NOT NULL,
  Name VARCHAR2(100) NOT NULL,
  Address VARCHAR2(100) NOT NULL,
  Town VARCHAR2(100) NOT NULL,
  Post_code VARCHAR2(8) NOT NULL,
  Telephone INT NOT NULL,
  Contact_first_name VARCHAR2(100) NOT NULL,
  Contact_last_name VARCHAR2(100) NOT NULL,
  Email VARCHAR2(100) NOT NULL,

  PRIMARY KEY(Reference)
);

CREATE TABLE Employee(
  Employee_no INT NOT NULL,
  First_name VARCHAR2(100) NOT NULL,
  Last_name VARCHAR2(100) NOT NULL,
  NI_No VARCHAR2(100) NOT NULL,
  Telephone VARCHAR2(100) NOT NULL,
  Mobile VARCHAR2(100) NOT NULL,
  Hazardous_goods VARCHAR2(100) NOT NULL,

  PRIMARY KEY(Employee_no)
);

CREATE TABLE Vehicle_Type(
    Vehicle_Type_ID VARCHAR2(100) NOT NULL,
    Model VARCHAR2(100) NOT NULL,
    Make VARCHAR2(100) NOT NULL,

    PRIMARY KEY(Vehicle_Type_ID)
);

CREATE TABLE Vehicle(
  Registration VARCHAR2(100) NOT NULL,
  Vehicle_Type_ID VARCHAR2(100) NOT NULL,
  GVW VARCHAR2(100) NOT NULL,
  Vehicle_Year INT NOT NULL,
  Body VARCHAR2(100),

  PRIMARY KEY(Registration),
   FOREIGN KEY(Vehicle_Type_ID) REFERENCES Vehicle_Type(Vehicle_Type_ID)
);

CREATE TABLE Trip(
    Trip_ID INT NOT NULL,
    Departure_date VARCHAR2(100) NOT NULL,
    Return_date VARCHAR2(100) NOT NULL,
    Employee_no INT NOT NULL,
    Vehicle_registration VARCHAR2(100) NOT NULL,

    PRIMARY KEY(Trip_ID),
    FOREIGN KEY(Employee_no) REFERENCES Employee(Employee_no)
);

CREATE TABLE Item(
    Manifest_barcode VARCHAR2(100) NOT NULL,
    Trip_ID INT NOT NULL,
    Item_weight INT NOT NULL,
    Pickup_customer INT NOT NULL,
    Delivery_customer INT NOT NULL,
    Category VARCHAR2(100) NOT NULL,

    PRIMARY KEY(Manifest_barcode),
    FOREIGN KEY(Trip_ID) REFERENCES Trip(Trip_ID),
    FOREIGN KEY(Category) REFERENCES Category(Category)
);

This is how the items are being inserted -

INSERT INTO Customer (Name,Reference,Address,Town,Post_code,Telephone,Contact_first_name,Contact_last_name,Email) VALUES
('Calash Ltd.',1,'88 Rinkomania Lane','Cardigan','SA55 8BA',11671595763,'Cameron','Dunnico','[email protected]');

INSERT INTO Employee (Employee_no,First_name,Last_name,NI_No,Telephone,Mobile,Hazardous_goods) VALUES
(0045619,'Eamon','O''Looney','JJ 56 53 26 B','1656727840','76599770175','N');

INSERT INTO Vehicle_Type (Vehicle_Type_ID,Model,Make) VALUES
(1,'RIEVER','ALBION');

INSERT INTO Vehicle(Registration,Vehicle_Type_ID,GVW,Vehicle_Year,Body) VALUES
('4585 AW',1,20321,1963,'');

INSERT INTO Category (Category, Description, Requirements) VALUES
('A','Normal','');

INSERT INTO Trip(Trip_ID,Departure_date,Return_date,Employee_no,Vehicle_registration) VALUES
(72943,'40910','40914',0028539,'BR58BXE');

INSERT INTO Item(Manifest_barcode,Trip_ID,Item_weight,Pickup_customer,Delivery_customer,Category) VALUES
(541769754,73421,3629,44,145,'A');

Anyone have any suggestions?

Answer 1

Your full insert script has 13 inserts into the item table with trip_id 73495. Your error is being thrown from the first one:

INSERT INTO Item(Manifest_barcode,Trip_ID,Item_weight,Pickup_customer,Delivery_customer,Category)
VALUES (159601450,73495,2156,166,184,'A');

Your script does not have a matching insert for the trip table. You have IDs either side:

INSERT INTO Trip(Trip_ID,Departure_date,Return_date,Employee_no,Vehicle_registration)
VALUES (73494,'40994','40995',0077517,'PY11 OAA');
INSERT INTO Trip(Trip_ID,Departure_date,Return_date,Employee_no,Vehicle_registration)
VALUES (73496,'40994','41000',0083413,'PY58 UHF');

But there is not one for ID 73495.

Searching that script for 73495 only matches those 13 item inserts, and a later item which has a manifest_barcode of 617349505 which contains it. But that is all.

There is no matching trip, which is what the exception is telling you.

Answer 2

Hi please check the following

you have inserted this row in the employee table with employee_no=0045619

INSERT INTO Employee (Employee_no,First_name,Last_name,NI_No,Telephone,Mobile,Hazardous_goods) VALUES
(0045619,'Eamon','O''Looney','JJ 56 53 26 B','1656727840','76599770175','N');

trip is the table which was referencing the employee table

INSERT INTO Trip(Trip_ID,Departure_date,Return_date,Employee_no,Vehicle_registration) VALUES
(72943,'40910','40914',0028539,'BR58BXE');

in this insert statement you had inserted a row with employee_no=0028539, which was not matched with the employee_no(0045619) available in the employee table. please correct the value and try to insert with the same employee number in the employee table

---------editied----------------

check the tripid in item(73421) is available in the trip table?