drop table emp;
drop table dept;
CREATE TABLE dept
   (deptno   NUMBER NOT NULL PRIMARY KEY ,
    deptname VARCHAR2(30) NOT NULL );

CREATE TABLE emp
   (empno   NUMBER NOT NULL PRIMARY KEY,
    empname  VARCHAR2(20) NOT NULL,
    deptno   NUMBER,
       CONSTRAINT emp_fk1 foreign key (deptno) references dept(deptno) DEFERRABLE);

insert into dept values (10, 'Sales');
insert into emp values (1, 'Joe', 10);
commit;

-- see if we can do it with just deferrable constraints
begin
 update dept set deptno = 100 where deptno = 10;
 update emp set deptno = 100 where deptno = 10;
end;
/

-- no, we need to set the constraints to deferred in this session
-- in pl/sql block because most applications would typically use it this way
begin
Execute immediate 'SET CONSTRAINTS ALL DEFERRED';
update dept set deptno = 100 where deptno = 10;
update emp set deptno = 100 where deptno = 10;
 -- set them back on again now that we are done
 -- else they are reset to default (immediate)
 -- on commit/rollback
Execute immediate 'SET CONSTRAINTS ALL IMMEDIATE';
end;
/
select * from dept;
select * from emp;