INSERT ALL error ORA-02291 integrity constraint but PK is referenced

I’m creating a basic sales and inventory DB for a class.

I’ve successfully created the tables necessary with where I want my PK and FK’s to be.

The issue I’m running into now is when I try to perform a multiple insert into my employees table.

Here’s what has been created and inserted so far:

CREATE TABLE location (    
  zipcode int CONSTRAINT zipcode_pk PRIMARY KEY,    
  city varchar2 (50)      
)
;

CREATE TABLE employees (    
   employeeid int CONSTRAINT employeeid_pk PRIMARY KEY,    
   firstname varchar2 (50),    
   lastname varchar2 (50),    
   street varchar2 (50),    
   state varchar2 (2),    
   zipcode int REFERENCES location (zipcode),    
   datehired date,    
   phonenum int,    
   salaryhr number       
)
;

CREATE TABLE customer (    
   customerponum int CONSTRAINT customerponum_pk PRIMARY KEY,    
   firstname varchar2 (50),    
   lastname varchar2 (50),    
   street varchar2 (50),    
   zipcode int REFERENCES location (zipcode)    
)
;

CREATE TABLE products (    
   productid int CONSTRAINT productid_pk PRIMARY KEY,    
   productname varchar2 (50),    
   price number (*,2),    
   costpercent int   
)
;

CREATE TABLE inventory (  
   productid int REFERENCES products (productid),  
   unitonhand int,  
  CONSTRAINT productid_pkt PRIMARY KEY (productid)  
)
;

CREATE TABLE sales (     
   ordernum int CONSTRAINT ordernum_pk PRIMARY KEY,     
   customerponum int REFERENCES customer (customerponum),     
   productid int REFERENCES products (productid),     
   employeeid int REFERENCES employees (employeeid),     
   saledate date,     
   unitssold int     
)
;

INSERT ALL  
  INTO  location (zipcode, city) VALUES (77095, 'Houston')  
  INTO  location (zipcode, city) VALUES (77451, 'Dallas')  
  INTO  location (zipcode, city) VALUES (77114, 'Austin')  
  INTO  location (zipcode, city) VALUES (77369, 'Lubbock')  
  INTO  location (zipcode, city) VALUES (75451, 'El Paso')  
SELECT * FROM dual
;

INSERT ALL  
  INTO customer (customerponum, firstname, lastname, street, zipcode) VALUES (101, 'Josh', 'Smith', '100 Baker St',77095)  
  INTO customer (customerponum, firstname, lastname, street, zipcode) VALUES (102, 'John', 'Doe', '12 Yankee Ave',77451)  
  INTO customer (customerponum, firstname, lastname, street, zipcode) VALUES (103, 'Brandon', 'Markle', '1 Longhorn Blvd',77114)  
  INTO customer (customerponum, firstname, lastname, street, zipcode) VALUES (104, 'Mary', 'Eglin', '223 Aggie St',77369)  
  INTO customer (customerponum, firstname, lastname, street, zipcode) VALUES (105, 'Sue', 'Fields', '91 Patriot',75451)  
SELECT * FROM dual
;
'''

---Oracle liveSQL this is the statement that I receive the error
---

INSERT ALL  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (1, 'Jason', 'Wayne', '103 Brown St', 'TX', 77453, '14-may-13', 2814441304, 13)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (2, 'Jacob', 'Dutch', '14 Yawn Rd', 'TX', 77096, '12-july-11', 8325472222, 10)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (3, 'Susan', 'Anthony', '1 Patronas Ln', 'TX', 77231, '08-jan-17', 2819993547, 9)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (4, 'David', 'Lane', '888 Madrid Blvd', 'TX', 78113, '27-dec-18', 8321119876, 8)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (5, 'Anthony', 'Barnard', '21 Adiom Cl', 'TX', 79448, '13-nov-17', 2814558008, 10)  
SELECT * FROM dual
;

When I run the statement INSERT ALL INTO employees I get this error:

“ORA-02291: integrity constraint (SQL_NCBYEZZVAYRPDIJSWAZMSKRHK.SYS_C0016383126) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721“

Answer

The zip code values in your insert into the employees table don’t match the ones you created in the location table, so the error is valid and expected.

Either change the employees zip codes to match the locations you already have, or more likely – since you already use those for customers – add new locations for the zip codes you are trying to use:

INSERT ALL  
  INTO  location (zipcode, city) VALUES (77453, 'Lane City')  
  INTO  location (zipcode, city) VALUES (77096, 'Houston')  
  INTO  location (zipcode, city) VALUES (77231, 'Houston')  
  INTO  location (zipcode, city) VALUES (78113, 'Falls City')  
  INTO  location (zipcode, city) VALUES (79448, 'Richland')  
SELECT * FROM dual
;

5 rows inserted.

INSERT ALL  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (1, 'Jason', 'Wayne', '103 Brown St', 'TX', 77453, '14-may-13', 2814441304, 13)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (2, 'Jacob', 'Dutch', '14 Yawn Rd', 'TX', 77096, '12-july-11', 8325472222, 10)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (3, 'Susan', 'Anthony', '1 Patronas Ln', 'TX', 77231, '08-jan-17', 2819993547, 9)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (4, 'David', 'Lane', '888 Madrid Blvd', 'TX', 78113, '27-dec-18', 8321119876, 8)  
  INTO employees (employeeid, firstname, lastname, street, state, zipcode, datehired, phonenum, salaryhr) VALUES (5, 'Anthony', 'Barnard', '21 Adiom Cl', 'TX', 79448, '13-nov-17', 2814558008, 10)  
SELECT * FROM dual
;

5 rows inserted.

db<>fiddle