Oracle中为PROFESSOR表Dept_id外键列赋值遇ORA-02291错误的解决

现有PROFESSOR和DEPARTMENT两张表，初始创建PROFESSOR表时未包含Dept_id列；DEPARTMENT表将Prof_id设为外键，引用PROFESSOR表的Prof_id字段。插入数据后，修改PROFESSOR表新增Dept_id列并设置为外键，但错误地引用了PROFESSOR表自身的Prof_id字段。执行以下UPDATE语句时触发错误：

update PROFESSOR set Dept_id='11' where Prof_id='prof1';

错误信息：

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

附建表及插入数据语句：

create table PROFESSOR( Prof_id varchar2(5) primary key check(length(Prof_id)=5),
                        Prof_name varchar2(40), 
                        Email varchar2(40) check(Email like '%@%') unique,
                        Mobile varchar2(40) check(length(Mobile)=10) unique,    
                        Speciality varchar2(40));


create table DEPARTMENT(Dept_id varchar2(40) primary key,
            Dname varchar2(40),
            Prof_id varchar2(5) check(length(Prof_id)=5) references PROFESSOR(Prof_id) on delete cascade);

insert into PROFESSOR values('prof1','prof.raj','raj@gmail.com','9992214587','blockchain'); 
insert into PROFESSOR values('prof2','prof.ravi','ravi@gmail.com','9292514787','database'); 

insert into DEPARTMENT values('11','mca','prof1');
insert into DEPARTMENT values('12','btech','prof2');

alter table PROFESSOR add Dept_id varchar2(40) references PROFESSOR(Prof_id)on delete cascade;

新增的Dept_id外键被错误设置为引用PROFESSOR表的Prof_id字段，但Prof_id的值是prof1、prof2这类字符串，而你要赋值的11并不存在于PROFESSOR的Prof_id集合中，因此触发父键未找到的完整性约束错误。正确的逻辑应该是让PROFESSOR的Dept_id引用DEPARTMENT表的Dept_id主键（值为11、12）。

1. 删除错误的外键约束 #

先通过错误信息获取约束名（即SYS_C00101381949），执行删除语句：

alter table PROFESSOR drop constraint SYS_C00101381949;

如果不确定约束名，可通过以下查询获取：

select constraint_name from user_constraints 
where table_name = 'PROFESSOR' and constraint_type = 'R' and column_name = 'DEPT_ID';

2. 添加正确的外键约束 #

将PROFESSOR的Dept_id设置为引用DEPARTMENT表的Dept_id主键：

alter table PROFESSOR add constraint fk_prof_dept 
foreign key (Dept_id) references DEPARTMENT(Dept_id) on delete cascade;

3. 执行UPDATE赋值 #

现在可以正常执行更新语句：

update PROFESSOR set Dept_id='11' where Prof_id='prof1';
update PROFESSOR set Dept_id='12' where Prof_id='prof2';

临时替代方案（不推荐，仅应急使用） #

如果需要先完成赋值再修正约束，可临时禁用约束后操作：

-- 禁用错误约束
alter table PROFESSOR disable constraint SYS_C00101381949;
-- 执行更新
update PROFESSOR set Dept_id='11' where Prof_id='prof1';
update PROFESSOR set Dept_id='12' where Prof_id='prof2';
-- 删除错误约束并添加正确的
alter table PROFESSOR drop constraint SYS_C00101381949;
alter table PROFESSOR add constraint fk_prof_dept foreign key (Dept_id) references DEPARTMENT(Dept_id) on delete cascade;

内容的提问来源于stack exchange，提问作者Prasad jadhav