未找到父鍵ORA-06512:在"SYS.DBMS_SQL"

無法弄清楚問題，并會感謝您的幫助。定義表格和更改時沒有問題。由于某種原因，它無法找出另一個表/同一個表中的父鍵參考。此類問題的可能解決方案是什么？

問題是由 EMP 表約束引起的。

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'));

錯誤代碼：ORA-02291：違反完整性約束 (SQL_OZPTHTLYAAVUSNISLXUTJKQNF.SUPERVISOR) - 未找到父鍵 ORA-06512：在“SYS.DBMS_SQL”，第 1721 行

uj5u.com熱心網友回復：

您必須EMP按照層次結構的順序在表中插入記錄，例如首先是經理，而不是員工

你第一次插入員工100抱怨說EMPNO111的經理不存在......

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

標籤：sql 甲骨文

上一篇：如何在OPENROWSET中添加動態URL

下一篇：SQL-使用連接表欄位的GROUPBY和SUM