CODEWITHSUNDEEP
sqloracle-database
Vaiebhav Patil
Vaiebhav Patil

Reputation: 15

I'm repeatedly getting this "integrity constraint violated- parent key not found" error

The problem goes like: I have 2 tables Employee and Department. Both tables have department_id and manager_id in them. I have a primary key employee_id in table employee and primary key department_id in the table department. I have to make department_id and manager_id of table employee foreign keys to department_table. Now, I've spent 15+ hours (i know hard to believe) on trying to do so but failed. After making the foreign keys, I'm able to insert values into department table and then when I try to enter values into employee table I get the "invalid constraint violated" error. I know this might be a very basic thing but I'm really new to this and this foreign key stuff still confuses me. Can someone tell me what is wrong with my code, why am I only able to insert values into department table and not into employee table.

Here are the contents of my spool file:

CREATE TABLE EMPLOYEE(EMPLOYEE_ID NUMBER(6) CONSTRAINT EMP_NNPK PRIMARY KEY NOT NULL, FIRST_NAME VARCHAR2(20) CONSTRAINT FIRSTNAME_NN NOT NULL, LAST_NAME VARCHAR2(20) CONSTRAINT LASTNAME_NN NOT NULL, EMAIL VARCHAR2(25) CONSTRAINT EMAIL_UKNN UNIQUE NOT NULL, PHONE_NUMBER VARCHAR2(20) DEFAULT '0000000000', HIRE_DATE DATE CONSTRAINT HIREDATE_NN NOT NULL, JOB_ID VARCHAR2(10) CONSTRAINT JOBID_NN NOT NULL, SALARY NUMBER(8,2) CONSTRAINT SALARY_CHECK CHECK(SALARY >5000), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4) CONSTRAINT DEPTID_NN NOT NULL);

Table created.

SQL> CREATE TABLE DEPARTMENT(DEPARTMENT_ID NUMBER(4) CONSTRAINT DEPT_NN_PK PRIMARY KEY NOT NULL, DEPARTMENT_NAME VARCHAR2(30) CONSTRAINT DEPTNAME_NN NOT NULL, MANAGER_ID NUMBER(6) CONSTRAINT MANAGERID_UK UNIQUE, LOCATION_ID NUMBER(4));

Table created.

SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT DEP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID);

Table altered.

SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT MAN_FK FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID);

Table altered.

SQL> INSERT INTO DEPARTMENT VALUES('&DEPARTMENT_ID','&DEPARTMENT_NAME','&MANAGER_ID','&LOCATION_ID');
Enter value for department_id: 10
Enter value for department_name: ADMINISTRATION
Enter value for manager_id: 200
Enter value for location_id: 1700
old   1: INSERT INTO DEPARTMENT VALUES('&DEPARTMENT_ID','&DEPARTMENT_NAME','&MANAGER_ID','&LOCATION_ID')
new   1: INSERT INTO DEPARTMENT VALUES('10','ADMINISTRATION','200','1700')

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES('&EMPLOYEE_ID','&FIRST_NAME','&LAST_NAME','&EMAIL','&PHONE_NUMBER','&HIRE_DATE','&JOB_ID','&SALARY','&COMMISSION_PCT','&MANAGER_ID','&DEPARTMENT_ID');
Enter value for employee_id: 174369
Enter value for first_name: OLIVIA
Enter value for last_name: MATOS
Enter value for email: [email protected]
Enter value for phone_number: 8989166387
Enter value for hire_date: 03-SEP-2021
Enter value for job_id: 20ABC101
Enter value for salary: 100000.00
Enter value for commission_pct: 0.10
Enter value for manager_id: 200
Enter value for department_id: 10
old   1: INSERT INTO EMPLOYEE VALUES('&EMPLOYEE_ID','&FIRST_NAME','&LAST_NAME','&EMAIL','&PHONE_NUMBER','&HIRE_DATE','&JOB_ID','&SALARY','&COMMISSION_PCT','&MANAGER_ID','&DEPARTMENT_ID')
new   1: INSERT INTO EMPLOYEE VALUES('174369','OLIVIA','MATOS','[email protected]','8989166387','03-SEP-2021','20ABC101','100000.00','0.10','200','10')
INSERT INTO EMPLOYEE VALUES('174369','OLIVIA','MATOS','[email protected]','8989166387','03-SEP-2021','20ABC101','100000.00','0.10','200','10')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.DEP_FK) violated - parent key not found

Upvotes: 1

Views: 8076

Answers (2)

Jameson_uk
Jameson_uk

Reputation: 487

Your foreign key is referencing the wrong table FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID); should be FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID); It is trying to validate department ID against the ID in the employee table.

When you add a foreign key constraint all you doing is telling the database to ensure any value being inserted into a column exists in the related table (the other side of the foreign key).

so ALTER TABLE ABC ADD CONSTRAINT TEST_FK FOREIGN KEY(MY_FK_COL) REFERENCES XYZ(MY_PK_COL); is telling the database that each time someone tries to insert or update the MY_FK_COL column in the ABC table that the database should check that the value already exists in the MY_PK_COL column of the XYZ table. If the value does not exist you get your ORA-02291: integrity constraint (TEST_FK) violated - parent key not found error telling you the value does not already exist in the parent / related table / the other side of the foreign key

The main reason for this is to ensure valid data. Take your employee -> department example, you create a foreign key to ensure that employees can only be added to departments that actually exist.

Also you really want to use the right data-types as you have defined your IDs as number but are then inserting them as text. It probably won't make a difference but it does mean that there needs to be some conversion going on in the database and it can cause some oddities. Just remove the single quotes around your numeric fields.

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

Try to COMMIT after inserting the manager, before inserting the employee reporting to him .

Upvotes: -1

Related Questions