sql - 未找到父键 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 行
解决方案
您必须按照层次结构的顺序在表中插入记录EMP
,例如首先是经理,然后是员工
您第一次插入员工100
抱怨说EMPNO
111 的经理不存在......
insert into EMP (EMPNO, ENAME, EJOB, MGR_ID, ...
values (100, 'Ravi', 'MGR', 111, ...
推荐阅读
- excel - 使用 VBA 将第三个 Y 轴添加到 Excel 图表
- node.js - 如何在 Node.JS Google Cloud 函数中获取访问令牌?
- javascript - 将 unix 时间戳转换为特定时区
- typescript - 打字稿用字符串值重命名类型键
- swiper - 刷卡器....如何阻止仅向左滑动的第一张幻灯片?
- laravel - Laravel:在 Laravel 中执行 PostgreSQL currval() 函数
- python - 您如何判断从原始数据帧和当前数据帧中删除了哪些行?
- c++ - 原始消息上的 MessageDifferencer 是否处理映射?
- mongodb - 如何在 MongoDB 中将来自其他集合的文档的整个值更新为来自另一个具有相同 ID 键的文档?
- svelte - 如何在 Svelte 应用程序的共享功能中访问商店?