parent key not found ORA-06512: at "SYS.DBMS

Can't figure out the problem, and will appreciate the help. There was no problem while defining the tables and alterations. For some reason it can't figure out the parent key reference in another table/ in the same table. What could be the possible solution for such problems?

Problem is caused by the EMP table constraint.


create table DPT (
    DNO             varchar2(3) constraint pri_key primary key,
    DNAME           varchar2(10) constraint unq unique,
    
    constraint sta_letr check(DNO like 'D%')
);

create table PROJECTS (
    DNO             varchar2(3) constraint dno_fork references DPT(DNO) constraint dno_nullState not null, 
    PRJ_NO          varchar2(5) constraint rgexCheck check(PRJ_NO like 'P%') constraint prj_nullState not null, 
    PRJ_NAME        varchar2(10),
    PRJ_CREDITS     number(2) constraint credRange check(PRJ_CREDITS between 1 and 10),
    START_DATE      date,
    END_DATE        date,
    
    constraint prKey primary key(DNO, PRJ_NO),
    constraint dateChecker check(END_DATE > START_DATE)  
);

create table EMP (
    EMPNO           number(4),
    ENAME           varchar2(10),
    EJOB            varchar2(9) default 'CLRK' constraint jobCheck check(EJOB in('CLRK', 'A.MGR', 'MGR', 'GM', 'CEO')),
    MGR_ID          number(4),
    BIRTH_DATE      date,
    SAL             number(7,2) default 20001 constraint salCheck check(SAL > 20000),
    COMM            number(7,2) default 1000,
    DEPTNO          varchar2(3) constraint deptFk references DPT(DNO),
    PRJ_ID          varchar2(9) default 'P1', 
    DATE_OF_JOIN    date,
    
    constraint supervisor  foreign key(MGR_ID) references EMP(EMPNO),
    constraint pri_ky primary key(EMPNO)
);


--Insertion, Modifications and Alterations

alter table EMP modify PRJ_ID varchar2(5);
alter table EMP drop constraint deptFk;
alter table EMP add constraint deptRef foreign key(DEPTNO, PRJ_ID) references PROJECTS(DNO, PRJ_NO);
alter table DPT add LOCATIONS varchar2(9);
alter table DPT modify LOCATIONS default 'BNG';
alter table DPT add constraint oth_val check(LOCATIONS in ('BNG', 'MNG', 'MUB', 'HYD', 'CHN'));

alter table DPT modify DNAME varchar2(15);

insert into DPT (DNO, DNAME, LOCATIONS) values ('D1', 'Marketing', 'CHN');
insert into DPT (DNO, DNAME, LOCATIONS) values ('D2', 'Research', 'MNG');
insert into DPT (DNO, DNAME, LOCATIONS) values ('D3', 'Administrator', 'BNG');
insert into DPT (DNO, DNAME, LOCATIONS) values ('D4', '', 'BNG');
insert into DPT (DNO, DNAME, LOCATIONS) values ('D5', 'IT', 'BNG');
insert into DPT (DNO, DNAME, LOCATIONS) values ('D6', 'Corporate', 'HYD');


select * from DPT;

insert into PROJECTS (DNO, PRJ_NO, PRJ_NAME, PRJ_CREDITS) values ('D1', 'P1', '', 2);
insert into PROJECTS (DNO, PRJ_NO, PRJ_NAME, PRJ_CREDITS) values ('D2', 'P1', '', 2);
insert into PROJECTS (DNO, PRJ_NO, PRJ_NAME, PRJ_CREDITS) values ('D3', 'P2', '', 7);
insert into PROJECTS (DNO, PRJ_NO, PRJ_NAME, PRJ_CREDITS) values ('D1', 'P3', '', 5);
insert into PROJECTS (DNO, PRJ_NO, PRJ_NAME, PRJ_CREDITS) values ('D4', 'P2', '', 7);

select * from PROJECTS;


--Statement where the problem is occurring.

insert into EMP (EMPNO, ENAME, EJOB, MGR_ID, BIRTH_DATE, SAL, DEPTNO, PRJ_ID, DATE_OF_JOIN) values (100, 'Ravi', 'MGR', 111, to_date('10-10-1985', 'dd-mm-yyyy'), 32000, 'D1', 'P1', to_date('2-10-2001', 'dd-mm-yyyy'));

Error Code: ORA-02291: integrity constraint (SQL_OZPTHTLYAAVUSNISLXUTJKQNF.SUPERVISOR) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

CodePudding user response：

You must insert the records in the EMP table in the order of the hierarchy, e.g. first the manager, than the employee

Your first insert of the employee 100 complains that the manager with EMPNO 111 does not exists...

insert into EMP (EMPNO, ENAME, EJOB, MGR_ID, ...
values (100, 'Ravi', 'MGR', 111, ...