Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, SACHIN.

Asked: October 16, 2000 - 1:15 am UTC

Last updated: March 01, 2010 - 11:22 am UTC

Version: 8

Viewed 10K+ times! This question is

You Asked

Dear tom,
please let me know how can i update a record in the primary key field which behaves as a foreign key to other tables without altering (disabling) the primary/foreign constraint???? please reply sooon

yours faithfully,

SACHIN JAIN

and Tom said...

See
</code> http://asktom.oracle.com/~tkyte/update_cascade/index.html <code>
for one solution.

Alternatively, you can use deferred constraints as well. If you create the foreign keys "deferrable", you can

set constraints deferred;
update parent_table;
update child_table(s);
commit;

and the constraint will be verified at the commit instead of at the statement level, allowing you to "cascade" the update yourself.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sagi, October 24, 2002 - 6:36 am UTC

Hi Tom,

SQL> select table_name, constraint_name, DEFERRABLE, DEFERRED, VALIDATED     
  2  from user_constraints
  3  where table_name in ('EMP', 'DEPT')
  4  ORDER BY 1 ;

TABLE_NAME CONSTRAINT_NAME   DEFERRABLE  DEFERRED  VALIDATED
------------------------------ -----------------------------
DEPT        PK_DEPTNO   NOT DEFERRABLE IMMEDIATE VALIDATED
EMP         PK_EMPNO    NOT DEFERRABLE IMMEDIATE VALIDATED
EMP         FK_DEPT     NOT DEFERRABLE IMMEDIATE VALIDATED
EMP        SYS_C001402  NOT DEFERRABLE IMMEDIATE VALIDATED

I tried:

SQL> set constraints pk_dept, fk_dept deferred ;
set constraints pk_dept, fk_dept deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

Check the Doc. It says:

Specify NOT DEFERRABLE to indicate that this constraint is checked at the end of each DML statement. If you do not specify either word, then NOT DEFERRABLE is the default. 

INITIALLY IMMEDIATE  
 Specify INITIALLY IMMEDIATE to indicate that at the start of every transaction, the default is to check this constraint at the end of every DML statement. If you do not specify INITIALLY, INITIALLY IMMEDIATE is the default. 
 
INITIALLY DEFERRED  
 Specify INITIALLY DEFERRED to indicate that this constraint is DEFERRABLE and that, by default, the constraint is checked only at the end of each transaction. 
 
 
Restrictions: 

You cannot defer a NOT DEFERRABLE constraint with the SET CONSTRAINT(S) statement. 

You cannot specify either DEFERRABLE or NOT DEFERRABLE if you are modifying an existing constraint directly (that is, by specifying the ALTER TABLE ... MODIFY constraint statement). 

You cannot alter a constraint's deferrability status. You must drop the constraint and re-create it. 

If the constraint is either "INITIALLY IMMEDIATE" or "INITIALLY DEFERRED" then what is the need to use the SET CONSTRAINTS clause. Because anyway the check is done at the end of DML or Transaction. Right?

I would appreciate if you could give us an example. 

Thanx in advance.

Regards,
Sagi.
 

Tom Kyte
October 24, 2002 - 7:04 am UTC

an example of what exactly??? you just demonstrated that the documentation is accurate:

...
Restrictions:

You cannot defer a NOT DEFERRABLE constraint with the SET CONSTRAINT(S)
statement.
......

Sagi, October 24, 2002 - 9:37 am UTC

Hi Tom,

If we create constraints on EMP and DEPT (Both INITIALLY DEFERRED) or 
If we create RI constraint on EMP (INITIALLY DEFERRED) then

directly we can update the master (DEPT) and child (EMP). 

So when do we use the SET CONSTRAINTS [<ALL>/<Constraint_Name>] DEFERRED.

I tried the below

SQL> CREATE TABLE DEPT1 AS SELECT * FROM SCOTT.DEPT ;

SQL> CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP ;

SQL> ALTER TABLE DEPT1
  2  ADD CONSTRAINT PK_DEPTNO1 PRIMARY KEY(DEPTNO) INITIALLY DEFERRED ;
Table altered.

SQL> ALTER TABLE EMP1
  2  ADD CONSTRAINT FK_DEPTNO1 FOREIGN KEY (DEPTNO)
  3  REFERENCES DEPT1(DEPTNO) INITIALLY DEFERRED ;
Table altered.

SQL> UPDATE DEPT1
     SET DEPTNO=DEPTNO+1 ;

SQL> UPDATE EMP1
     SET DEPTNO=DEPTNO+1 ;

and it allows me to directly update without using SET CONSTRAINTS clause. Could you give an example where we use SET CONSTRAINT clause. Please.

Regards,
Sagi 

Tom Kyte
October 24, 2002 - 2:26 pm UTC

umm, when the constraints are deferrable initially immediate would be a case.

when you wanted to verify that your update is commitable (eg: in your example, you could use set constraints to test the constraints to see if the commit will succeed or fail)



A reader, October 25, 2002 - 7:40 am UTC

Hi Tom,

Sorry, but could not catch you. Could you please demonstrate.

Thanx in advance.

Regards,
Sagi

Tom Kyte
October 26, 2002 - 11:37 am UTC

try the commands yourself. Just *TRY* them.

Or search for

"set constraints"

(with quotes) for other threads where this has been discussed. the examples of using this are out there.

What about in a stored function?

Doron Rippel, November 13, 2002 - 4:17 pm UTC

Why does it not work in a stored function?
When I try to compile the following stored function:
-------------------------------------------------------
CREATE OR REPLACE FUNCTION ttt
return number
IS
BEGIN
SET CONSTRAINTS DEFERRED;
return 0;
END ttt;
-------------------------------------------------------
I get:

PLS-00103: Encountered the symbol "CONSTRAINTS" when expecting one of the following: transaction

What's wrong? How can I defer constraints in a stored function?

Thanks

Doron

Tom Kyte
November 13, 2002 - 6:10 pm UTC

xecute immediate 'SET CONSTRAINTS ALL DEFERRED'

deferral

mo, November 14, 2002 - 8:43 am UTC

TOm:

1. do you recommend to make all primary key/foreign key
constraints deferred when I create them?

2. If I did not do that, and decided to defer it after creation can I do something like "Alter table emp set constraint xxx deferred".

Thank you,


Tom Kyte
November 14, 2002 - 7:23 pm UTC

1) i recommend you understand the implications (almost none on a fkey, totally different type of index with a pkey) and use your judgement

2) no, you need to recreate the constraint.

Now it works!

Doron Rippel, November 14, 2002 - 2:43 pm UTC

Thanks.
I struggled with this problem for several days and could not find proper documentation or examples for deferring constraints within a stored function.

deferral

mo, November 15, 2002 - 7:27 am UTC

Tom:

well as you know judgements keep changing as system requirements keep developing. the initial database design is never final.

anyway it seems to that you always should defer a foreign key constraint when you need to update parent table.

Thanks,

Tom Kyte
November 15, 2002 - 7:22 pm UTC

and if you need to update the parent table -- you have chosen the wrong key for the parent table ;)

Very informative; Please guide

Yogeeraj, January 11, 2003 - 6:35 am UTC

Hello,

I did the following exercise (before implementation in our Forms applications):
=========================================================================
yd@yddb.MU> alter table drt555 drop constraint fk_drt555_1;

Table altered.

Elapsed: 00:00:00.11
yd@yddb.MU> ALTER TABLE DRT555 ADD CONSTRAINT FK_DRT555_procnum_lcncod
FOREIGN KEY (PROCNUM, LCNCOD)
REFERENCES DRT540 (PROCNUM, LCNCOD) deferrable disable;

Table altered.

Elapsed: 00:00:00.07
yd@yddb.MU> alter table drt555 modify constraint fk_drt555_procnum_lcncod enable novalidate;

Table altered.

Elapsed: 00:00:00.09
yd@yddb.MU> alter table drt555 enable constraint fk_drt555_procnum_lcncod;

Table altered.

Elapsed: 00:00:00.04
yd@yddb.MU>


yd@yddb.MU> update drt555 set procnum,='888'
2* where procnum='777' and lcncod='PHX'
update drt555 set procnum='888'
*
ERROR at line 1:
ORA-02291: integrity constraint (YD.FK_DRT555_PROCNUM_LCNCOD) violated - parent key not found


Elapsed: 00:00:00.02
yd@yddb.MU>


===================================================
that won't work until we defer the constraints:
set constraints all deferred;
It makes it so that any constraint that is deferrable (to be checked at COMMIT, not at the statement level) is in fact deferred.
===================================================
yd@yddb.MU> set constraints all deferred;

Constraint set.

Elapsed: 00:00:00.01
yd@yddb.MU> update drt555 set procnum='888'
2* where procnum='777'

2 rows updated.

Elapsed: 00:00:00.02
yd@yddb.MU> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (YD.FK_DRT555_PROCNUM_LCNCOD) violated - parent key not found


Elapsed: 00:00:00.03
yd@yddb.MU>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and then tried:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

yd@yddb.MU> alter table drt555 modify constraint fk_drt555_procnum_lcncod initially deferred;

Table altered.

Elapsed: 00:00:00.03
yd@yddb.MU> update drt555 set procnum='888'
2 where procnum='777'
3 ;

2 rows updated.

Elapsed: 00:00:00.02
yd@yddb.MU> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (YD.FK_DRT555_PROCNUM_LCNCOD) violated - parent key not found


Elapsed: 00:00:00.01
yd@yddb.MU>

============================================================

Is it true that if we create a constraint as "initially deferred", we do not need to use:
"set constraints all deferred;"
before doing an update?

What is the overhead?

Is this the only "advantage" of defining "initially deferred" constraints?

thank you in advance for you explanation and precious time.

Best Regards
Yogeeraj

PS. In which month will your most-awaited new book be available? thanks

Tom Kyte
January 11, 2003 - 8:25 am UTC

it is true that if you set the constraint to initially deferred -- that as the name implies -- it will be deferred initially and you need not set all constraints or that constraint to deferred. That is correct (and apparently obvious?)

Overhead of what? You benchmark to find overhead -- use </code> http://asktom.oracle.com/~tkyte/runstats.html <code>
and simulate it.

"advantage" is a two way street. This is neither an advantage or disadvantage. It is a setting. Do you want it initially deferred? Most people do not -- it fails the commit and I'll betcha most applications would not deal with that nicely -- they are not expecting a commit to fail. Most people would defer the constraint when needed -- especially since a primary key should never in a billion years be updated.

springtime is the closest you'll get me to commit to.

DML_table_expression_clause

A reader, November 17, 2003 - 1:44 pm UTC

In 9iR2 SQL Reference for Update statement:

"The ONLY clause applies only to views. Specify ONLY syntax if the view in the UPDATE clause is a view that belongs to a hierarchy and you do not want to update rows from any of its subviews."

I don't quite understand this. Could you provide an example?


Thanks.



Any disadvantages to DEFERRABLE INITIALLY IMMEDIATE for foreign keys?

Mike, July 25, 2008 - 9:15 am UTC

In an earlier post there was this exchange:
Q: do you recommend to make all primary key/foreign key constraints deferred when I create them?
A: I recommend you understand the implications (almost none on a fkey, totally different type of index with a pkey) and use your judgement)

When you said 'almost none on a fkey', can you clarify your choice of the word 'almost'? Are there any potential disadvantages?
Tom Kyte
July 29, 2008 - 10:15 am UTC

almost none, meaning "all things considered the same - if you leave them initially immediate - they will be the same, only if you defer them will they be different"


INITIALLY IMMEDIATE

Mike, July 29, 2008 - 2:12 pm UTC

Thanks very much.

zilvinas, February 26, 2010 - 3:03 am UTC

Reading all this I came up to conclusion: "by default you should make all primary key/foreign key constraints deferrable initially immediate".
But this is not oracle default. I have a feeling that there's somethig more...
Is there some point to set keys "not deferable" like oracle does by default?
Tom Kyte
March 01, 2010 - 11:22 am UTC

no, you should not.

if you make a primary key deferrable for example, it will us a NON-UNIQUE index for the constraint enforcement. This can and will affect query plans and might not be what you wanted.

Most of the time, most all of the time, almost all of the time - you want non-deferrable constraints. It is only when you identify an ongoing need for deferrable that you would go the other way.

materialized views are the exception, an updatable materialized view should use deferrable constraints - as per documentation.

update primary key story

daniel, November 08, 2013 - 4:41 pm UTC

hi, i have a composite primary key (that combination can never exist on the table again) that comes as foreign keys...the thing is someone can mess the combination of the composite key up by selecting (not typing) values that correspond to the table but not to that exact record (i.e. im in floor #2 ,my room is 205 and I mistook it for room 215, since both rooms are on floor #2 i could choose either, but that doesnt mean its my room)...should i leave the composite key still and update cascade, or create a unique conrtaint on that composite key and add a surrogate key as PK...thanks?

Invalid link

Tomas Hammar, August 01, 2014 - 5:54 am UTC