add data in foreign key column of table using update command-CodePudding

i have two tables professor and department. professor table has dept_id as foreign key and department table has prof_id as forgin key. how i have created professor table without adding dept_id column then i created department table with prof_id as foreign key. now i added data in both table(professor table do not have column dept_id). now i altered table professor and added dept_id as foreign key and now i want to add data in this column what sql query should i use?

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','[email protected]','9992214587','blockchain'); 
insert into PROFESSOR values('prof2','prof.ravi','[email protected]','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;

now i want to add data into column Dept_id of table professor i tried

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

command but it is showing

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

CodePudding user response：

Of course it failed; you're referencing a wrong table (professor, instead of department).

This is what you did:

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

Table altered.

SQL> update PROFESSOR set Dept_id='11' where Prof_id='prof1';
update PROFESSOR set Dept_id='11' where Prof_id='prof1'
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C009258) violated - parent key not
found

So, drop that invalid constraint and create new one:

SQL> alter table professor drop constraint sys_c009258;

Table altered.

SQL> alter table PROFESSOR add CONSTRAINT FK_PROF_DEPT FOREIGN KEY (DEPT_ID)
  2  references DEPARTMENT(DEPT_ID)on delete cascade;

Table altered.

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

1 row updated.

SQL>

Now update works.

CodePudding user response：

Your ALTER TABLE DDL is faulty. Use:

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

See example at db<>fiddle.