| Temporary disable constaint on UPDATE [message #38179] |
Thu, 28 March 2002 05:40  |
Francois
Messages: 8
Registered: March 2002
|
Junior Member |
|
|
Is it possible to temporary disable constraint integrity until a COMMIT ?
I'D like to do something like
UPDATE TABLE_PARENT
SET PARENT_ID = p_NewParent
WHERE PARENT_ID = p_OldParent;
then UPDATE all children tables...
then COMMIT;
I don't have an UPDATE_CASCADE integriry...
TIA
|
|
|
|
| Re: Temporary disable constaint on UPDATE [message #38181 is a reply to message #38179] |
Thu, 28 March 2002 07:17  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
http://govt.oracle.com/~tkyte/update_cascade/
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
