Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Arun.

Asked: October 06, 2002 - 11:31 am UTC

Last updated: April 30, 2021 - 10:14 am UTC

Version: 8i

Viewed 100K+ times! This question is

You Asked

Dear Tom,
We know that when we delete a parent record, automatically
child record also will be deleted if we used "on delete cascade".
Is it possible to update automatically a child record when we
update parent record?
(Do we have "On Update Cascade" option? Or any other like..)


and Tom said...

There is not "on update cascade" automagically.

There are ways to do it,

o deferrable constraints. defer the foreign key check until commit, update the parent, update the child and then commit.

o use the technique here:
</code> http://asktom.oracle.com/~tkyte/update_cascade/index.html <code>


Personally -- I've never found a need or use for update cascade. I'm opposed to it. If your design requires it -- change your design now if you can.


Primary keys are supposed to be imutable, never changing, constant. It is an excessively bad practice to have to update them ever. If there is a 0.00001% chance you will have to update a primary key -- then it is not a primary key, its a surrogate key and you need to find the true primary key (even if you have to make it up via a sequence)

Rating

  (44 ratings)

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

Comments

Surrogate keys

Tony, October 07, 2002 - 5:44 am UTC

Tom,

I agree with your advice, but query your use of terms. A primary key that is made up via a sequence surely IS a surrogate key. The natural primary key may well be the one that has a 0.00001% chance of change - e.g. due to a typing error. The only primary keys that could never require an update are surrogate keys, where the key value has no meaning (like a sequence number).

Tom Kyte
October 07, 2002 - 7:38 am UTC



A primary key is something that doesn't change. If your primary key changes, it is not a primary key -- a candidate key if you will but not a primary key.

Your right, I should have called it a "candidate key".


Thanks Mr.Tom

Arun Kumar K T, November 10, 2002 - 3:58 am UTC

Thank you very much Mr.Tom for your timely response.

update primary key

Sean, January 31, 2004 - 2:18 pm UTC

Hi Tom,

I have a trigger to update child record after updating primary key of parent record.
It should not work, but it works(Oracle 902).  I don't know why.


Here is the sample:
create table t1(c1 number primary key);

create table t2(c2 number references t1(c1));

insert into t1
values(1);

insert into t2
values(1);

update t1
set c1 = 2
where c1 = 1;

update t1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C0026830) violated - child record
found


create or replace trigger cascade_update
after update of c1 on t1
for each row
begin
   update t2
   set c2 = :new.c1
   where c2 = :old.c1;
end;
/


Trigger created.

SQL> update t1
  2  set c1 = 2
  3  where c1 = 1;

1 row updated.

SQL> select * from t2;

        C2
----------
         2

Thanks so much for your help.

Sean 







 

Tom Kyte
January 31, 2004 - 2:36 pm UTC

they relaxed the mutating table restrictions in 8i.

Before you go off "gung ho" on this approach -- REALLY -- consider this:

ops$tkyte@ORA9IR2> create table t1(c1 number primary key);
Table created.
 
ops$tkyte@ORA9IR2> create table t2(c1 number primary key, c2 number references t1(c1));
Table created.
 
ops$tkyte@ORA9IR2> insert into t1 values(1);
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values(2);
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values(100, 1);
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values(101, 2);
1 row created.
 
ops$tkyte@ORA9IR2> create or replace trigger cascade_update
  2  after update of c1 on t1
  3  for each row
  4  begin
  5          dbms_output.put_line( 'setting ' || :old.c1 || ' to ' || :new.c1 );
  6     update t2
  7        set c2 = :new.c1
  8      where c2 = :old.c1;
  9  end;
 10  /
Trigger created.
 

ops$tkyte@ORA9IR2> update t1
  2    set c1 = c1+1;
setting 1 to 2
setting 2 to 3
 
2 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
        C1
----------
         2
         3
 
ops$tkyte@ORA9IR2> select * from t2;
 
        C1         C2
---------- ----------
       100          3
       101          3

<b>Hmmm, they are both 3. Well, we did that to ourselves.  The database only did what it was asked to do in this case...

Even worse perhaps:</b>
 
ops$tkyte@ORA9IR2> delete from t2;
2 rows deleted.
 
ops$tkyte@ORA9IR2> delete from t1;
2 rows deleted.
 
ops$tkyte@ORA9IR2> commit;
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values(2);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values(1);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values(100, 1);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values(101, 2);
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t1
  2    set c1 = c1+1;
setting 2 to 3
setting 1 to 2
 
2 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
        C1
----------
         3
         2
 
ops$tkyte@ORA9IR2> select * from t2;
 
        C1         C2
---------- ----------
       100          2
       101          3
 
<b>same exact data -- byte for byte -- same exact data, but.....

different answer.

Caveat Emptor.  Do NOT do this in a trigger.  In fact -- beware of triggers that might depend on the order of rows being updated.  

Use deferrable constraints.

Perform the updates in a stored procedure.

Always do it in a controlled, safe, predicable manner.  

Better yet -- don't do it.  primary keys are supposed to be immutable.</b>


 

Cascade update will be a good feature. Sure!!

Thaha Hussain, May 03, 2004 - 3:44 am UTC

Dear Tom,

Cascade update will be a good feature, if you can incorporate in later relaeases.

Take a case like this.

One fellow put customer code ' A123' and 'A123' for customer master. While searching he will always find two similar customers, as code is important for him.

I think you always personally disagree with a feature, that is not there in Oracle. (gaps in sequence for example!)

Why doesn't Oracle Support 'Cascade Update'

Arash, June 08, 2004 - 3:16 pm UTC

Hi Tom,

I had a discussion today with our developers regarding using Surrogate keys as primary key and one the major reason was to support Cascade Update.

They asked why Oracle doesn't support this? Why does it support Cascade Delete but not update?

I couldn't answer! As always , could you please let us know?

Thanks,
Arash


Tom Kyte
June 08, 2004 - 3:45 pm UTC

because update cascade means you picked a set of columns that is not imutable -- eg: you picked a primary key that isn't a primary key.

Hence, you need to find something imutable.

If you find yourself constantly in update cascade mode, you've done something *wrong*.

if you need to do it every now and again, once in a very blue moon -- look at DEFERRABLE constraints.

set constraints all deferred;
update parent set primary_key = new_value where primary_key= old_value;
update child set fkey = new_value where fkey = old_value;
set constraints all immediate;




As always the best answer! Thanks

A reader, June 08, 2004 - 3:48 pm UTC


bad window of opportunity for constraint violating values

A reader, February 14, 2005 - 7:33 pm UTC

Agreed that it is good design NOT to ever use update cascade, but... wouldn't the work-around you provided create a window of opportunity that some other session would enter values that would violate the R constraint (and your subsequent re-set of it to immediate would fail)?

Also, update cascade is sometimes needed for foreign keys whose parent keys that are not primary, but unique (whose values are [albeit still rarely] mutable).

Thanks.

Tom Kyte
February 14, 2005 - 8:01 pm UTC

set constraint is session specific, as are the consistency "violations"

you are allowed -- IN YOUR SESSION -- to temporarily have the data go out of "consistency".

when you commit -- YOUR changes must be 100% consistent.

and this is not affected by multi-user situations -- each session can only see it's changes.

(and by definition, the parent "keys" are not primary -- only unique, implementation was wrong ;)

A different perspective on Cascading Updates

Marcus, October 05, 2006 - 3:15 pm UTC

In reading through the past review something jumped out about you response to the trigger, highlighting the inherent problem with updating a key and it cascading causing bad data.

The problem I had is in your use of the update query. An update to a key should not be without a where condition. After all this is not a update salary kind of thing.

Update t1 set c1 = x where c1 = y;

This would prevent the problem you pointed out and would be applicable to the actual use of cascading updates among tables.

I would also point out that over time the natural key may change from programmatic or policy changes. This would cause a new record and connecting the dots, then cleaning up the old. Or, the same effect as cascading update. Surrogate keys by nature require the database to have additional constraints, indexes and joins that are unnessary if you use the natural key (that data required to uniquely identify the non-key data). In small applications this is irrelevant, but in large enterprise applications this can be painful.

Tom Kyte
October 05, 2006 - 4:25 pm UTC

not following you here at all.

why should an update to a key not have a where condition????

"we are merging two companies, the ID's for company 1 need to have 10000000 added to them to support this merge"

In general, there is NOT ANY SUCH REQUIREMENT.

And what if my where clause (if you say I must have one) is "where 1=1"

and if a natural key changes due to whatever, it was not a key, it might be something "unique" but it wasn't the key.


so, while I agree it rarely sometimes must happen, I am here strongly arguing that "this had better not be part of your DESIGN, that would be a REALLY REALLY bad thing to do"



Update Cascade

karthick pattabiraman, June 27, 2008 - 1:37 am UTC

Your update cascade is very usefull.

I have one question. Is this restriction has been over come or its still there.

3. Updates to primary keys that do not generate 'new' primary keys are not currently supported. For example, take the standard DEPT table. The update statement "update dept set deptno = deptno+10" will not work whereas the update "update dept set deptno = deptno+1" will. The first update will change 10->20, 20->30 and so on. Problem is that 10->20 is not generating a 'new' primary key. On the other hand, deptno=deptno+1 does not have this problem since 10->11, 20->21 and so on.

NOTE: an update that affects a single row will never suffer from this problem.

If its still there is it possible to overcome it.

Thanks,

Karthick.
Tom Kyte
June 27, 2008 - 9:14 am UTC

this should be a one time thing - if you find you are doing update cascade as a natural part of your application - you have failed, you have done it wrong, you need to fix your model to use a key that is immutable.

I pray this never becomes part of the database.


but I have not modified this code since version 7.3, no. Any restrictions that were there - will still be there.

Declarative Cascaded update are indeed needed

Bernaridho, July 09, 2008 - 4:32 pm UTC

I disagree about 'Primary keys are supposed to be immutable'. There is no such scientific evidence to it.
Indeed, the primary keys are ok to be changed. If an id (the primary key column) of the table products changes, then the related description in the child tables should change too. Think about the bar-code or serial# as the id of the product

This even makes more sense on temporal data. On temporal data, for example, we combine the id of the product with the start date and stop date. The three columns become primary key. We should be able to change this, and the changes should be cascaded to the child tables.

I don't know why you personally never found a use for it. I find it very useful, and the lack of declarative referential integrity for cascaded-update in Oracle troubles me.

Bernaridho
www.bernaridho.net
Tom Kyte
July 09, 2008 - 5:09 pm UTC

How can you have "scientific evidence" about the basic premise.

If the id of a product changes - it is *not* the primary key. Therein lies your mistake. It is not the primary key.

If a bar-code or serial# changes - guess what, it isn't the primary key - it just isn't.


And your "temporal" example doesn't fly. If the three column become the primary key - well, no - they don't.

The primary key is the ID. You have flag deleted the old data, but the primary key is still ID.

A very bad answer from you

Bernaridho, July 27, 2008 - 11:12 am UTC

Hi Tom,

Followup July 9, 2008 - 5pm US/Eastern:
> How can you have "scientific evidence" about the basic
> premise.

Say that I don't have scientific evidence, then what is your basis for saying 'Primary keys are supposed to be immutable'? Is your saying based on scientific evidence?

If it's not (and you don't say that your claim is based on scientific evidence). So you imply that we just believe you for your claim without scientific evidence?

> If the id of a product changes - it is *not* the primary
> key. Therein lies your mistake. It is not the primary key.

It's fallacy. What is your basis on claiming that just because the id product can change it's not a key?

> If a bar-code or serial# changes - guess what, it isn't
> the primary key - it just isn't.

Again, a baseless accusation.

> And your "temporal" example doesn't fly. If the three
> column become the primary key - well, no - they don't.

Why they don't? You're playing God here!

> The primary key is the ID. You have flag deleted the old
> data, but the primary key is still ID.

Why is that? WHY?? Prove it to us with examples. Otherwise that is just silly, baseless opinion not worthy of thinking.
Tom Kyte
July 29, 2008 - 10:44 am UTC

primary keys being immutable is one of the tenants here - one of the basic premises, one of the rules of the game. That is all.

The concept is "do not choose a key that must be updated, that is bad, we do not do that when designing a relational schema, that is one of the rules"

Just like normalization
And using the right datatype (heck, you could just use varchar2(4000) right - we provide it, it could be done, no need to use dates, numbers, varchar2(30) - right? wrong, the rule is "use the right type")
just like constraints
just like hundreds of other things.


There is no example for a basic premise, a rule, a foundation concept.

Think about that.

There are thousands of things you can in fact DO in every technology. But then you read the best practice, you learn from experience, you are mentored and shown.... It is called "good design"

If your design includes having to update a primary key from one value to another, you want to rethink that.

Just like you want to rethink slow by slow processing (you can do it, lots of people do - is it right?)

Just like you want to rethink constraints enforced NOT by the database..




Joe Bloggs, July 31, 2008 - 9:49 am UTC

I'd give up Tom if I were you. This guy clearly doesn't understand the very first things about relational database design.

Why is it that we need a driving licence before we can drive a car, a pilot's licence before we can fly a plane, but they let any old Muppet at the helm of a database ?!?

Careless

Bernaridho, August 04, 2008 - 7:51 am UTC

Joe,

Surely you give up because perhaps it's you who know nothing about normalization and database design. Is normalization applicable? Explain your answer.

You're so cocky to quickly claim the other person knows nothing about database design.

Not on topic

Bernaridho, August 04, 2008 - 8:16 am UTC

> Primary keys being immutable is one of the tenants here - > one of the basic premises, one of the rules of the game.
> That is all.

Rule of the game without solid theoretical foundation right?

The authors of leading database textbooks: C J Date and Hugh Darwen never say that primary keys are being immutable. Edgar F. Codd on formulating the Relational Model (the original one back in 1970 as well as the Relationl Model V2) never say that primary keys are being immutable.

If cascaded update is so wrong, why SQL Standard Committee never say so? I wonder. And why user of products like SQL Server, Access, Interbase, and others do not ask the vendors to abandon cascaded update?

> The concept is "do not choose a key that must be updated,
> that is bad, we do not do that when designing a relational
> schema, that is one of the rules"

What is schema? Is it user? or else? I don't think we need other terms here.

> Just like normalization

Normalization is not applicable in Oracle. Why you mention normalization as if it's applicable in Oracle, or applicable at all in ALL products? ALL vendors are too lazy implementing normalization process.

Inapplicability of normalization can be verified easily. When you create table in Oracle, does Oracle tell you on what normal forms the table is? NO, Not since the beginning of Oracle DBMS, not now, and perhaps will never be.

> And using the right datatype (heck, you could just use
> varchar2(4000) right - we provide it, it could be done, no
> need to use dates, numbers, varchar2(30) - right? wrong,
> the rule is "use the right type")

What this has to do with our discussion? That's out of topic

> just like constraints
> just like hundreds of other things.

What do you want to say?

> There is no example for a basic premise, a rule, a
> foundation concept.

Initially you said that "primary keys being immutable is one of the tenants here - one of the basic premises". Now you say "There is no example for a basic premise". Have you ever been consistent?

> Think about that.

Yes, I think about you're wandering here.

> There are thousands of things you can in fact DO in every > technology. But then you read the best practice, you learn
> from experience, you are mentored and shown.... It is
> called "good design"

The good design and the good practice is to implement DECLARATIVE INTEGRITY constraints and INTEGRITY rules, not an imperative one. By not implementing declarative INTEGRITY constraints cascade update for foreign key, the users are enforced to use IMPERATIVE INTEGRITY CONSTRAINTS AND INTEGRITY RULES: by using triggers.

It's pathetic to pay million dollars for products that is claimed to be number one DBMS yet fall short on implementing declarative integrity constraints that are written down on ISO about SQL, and implemented in so many products with lower prices. In other words, many lower-price products are SUPERIOR than Oracle in terms of handling integrity in declarative way.

And who says integrity is not important? Declarative integrity constraints and declarative integrity rule is about INTEGRITY. Data integrity comes first, not speed, not fancy reporting tool, etc. Again, it's pathetic that Oracle with so high price fall short on this; on the matter that is of first and foremost importance in data management.

> If your design includes having to update a primary key
> from one value to another, you want to rethink that.

Why should I? Says who? You?

> Just like you want to rethink slow by slow processing (you
> can do it, lots of people do - is it right?)

Very bad analogy.

> Just like you want to rethink constraints enforced NOT by
> the database..

Just the opposite. Read my two paragraphs explaining integrity constraints you just missed to think and address.
Tom Kyte
August 04, 2008 - 2:13 pm UTC

ok, you win.

go with your updateable primary keys. have fun with that. really.



I don't know why you say it was a bad analogy, that needs some explanation. Just making the point that just because you can do something, doesn't mean you should.


instead of triggers, you could, well, do it the way many people would say it makes the most sense to do it. If you don't update a primary key, you have no code to write, and you have complete data integrity - 100%.

Joe Bloggs, August 07, 2008 - 10:05 am UTC

Patient : Doctor, Doctor, my arm hurts when I do this.
Doctor : Well don't do it then !

billy halim, August 07, 2008 - 2:46 pm UTC

Hi all oracle manias, it's really good discussion.
No.. no.. no one loses or wins, Tom, it's the rule and one rule has good and bad impacts, doesn't it?

First, I would like to look at from end user side. I think there are different psychological aspects between delete cascade and update cascade. Well, I think if I have ever used delete cascade, I know that I made a mistake and I do not want to make mistake again. But if I can use update cascade, I do not need to worry about wrong data setup and it will develop careless users. I can not imagine if i have to face this kind of database and users. Well, you know we use Oracle for large scale enterprise database.

Second, If one database supports update and delete cascade, Frequency of their usage will be different. I guess update cascade will be used more often. If I am dealing with large database, how long does it take to update hundreds of tables, rows, recreating indexes and so on? Process has to lock the updated records and wait until commit is executed.
Well, careless users.. mistaken product id.. first times.. ok.. it's natural.. second times.. ok ..third times ok.. fourth times .. the sales forces said "We can not sell your product. It's confusing". And your production head will resign asap...headache man!!

Third, Tom, I never try your suggested package for update cascade. It must be interesting but I have another idea. Actually oracle supports update cascade implicitly. Really. I think other products have the same thing. Just disable all your constraints and you are free to update "the primary keys". I have ever taken this action on production database because of mistaken product id. It's really ridiculous but it is the project was. Of course, i had retested for several times and crashed some cloned production databases. :) That's what We have to face in the project field.

Well friends.. this is just intermezzo ...


Tom Kyte
August 07, 2008 - 3:17 pm UTC

wrong data does not result from careless users.

wrong data exists because of incorrectly implemented schemas, without proper integrity constraints.

If product id is something that the application permits an update to - the product id is NOT your primary key, it is a unique constraint for that (and not null), and you have a surrogate.

billy halim, August 07, 2008 - 10:45 pm UTC

Tom, you are right. If we know the value of a column can be changed, it is strongly recommended not to have it as primary key. I agree with you. :)

What I tried to tell you is my experience in my previous projects. It was an ERP project. Everything has been setup and nothing was wrong with the database design until three days after going live the customers said that they wanted to change the product code. Well, as on third point I told you it was truly ridiculous. But you have to follow the customer or they don't pay you...:)

I am thinking about mutable primary key is a good concept. But I also considers if it is applied in practice, on big database how much do the effort to update all tables? One weeks later the customer said "I want to change the employee id", two weeks later he said again "I want to change the customer id". Well, the database gets messy and you probably get heart attack. Only ridiculous practitioner wants to entertain this ridiculous customer.

I am not talking about database theory. I am talking about putting theory into practice. Don't you think the world is flat? Well, I do not need to put it into practice now.
Tom Kyte
August 08, 2008 - 12:58 pm UTC

You had an engineering change request, a new feature.

New features not only permit, but many times dictate "a change"

You could have fixed this, you chose not to.

If the customer id (for example) is an externally facing bit of data (eg: it isn't a sequence populated field for example), and you think there is even a chance that someone would want to change it (no, you need not bow down to the whims of a single customer - your ERP product must have had other customers right - or was this the first customer, if the first - then make the change, they are basically beta testing for you) - you would not make it the key. It is a "pretty attribute" that end users look at, not the key.

Still trying to get my head around this one

Bill S., August 08, 2008 - 11:59 am UTC

I've read the reviews and your replies, Tom, and I'm quite frankly stumped as to what the fuss is about. Primary keys are exactly what they say - "primary" keys, uniquely identifiable to one item.
I think we should chuck this whole database thing and go back to keeping everything in spreadsheets.




OK, before the flame war starts that last comment was totally tongue-in-cheek. Where I work, we use sequences often for applications where there is no obvious primary key. Whenever possible, natural keys are used. And no, we don't change the keys. I like data integrity to be maintained. An example: in my job we have a lot of different organizations we deal with. Each one has an OrgID. OrgID is the primary key, and my response to the statement "Organization so-and-so just changed their OrgID to X" is always no they didn't unless they've suddenly become a new org.
OrgID identifies them across time. An org can close its doors, and two years later re-open them again as the same orgid. No problem. But if they suddenly decide they don't like their name or their identifier, they have to formally close the old org and open the new one.

Thanks

billy halim, August 08, 2008 - 1:48 pm UTC

Tom, I impress and am very happy with your answer. It reflects your truly experiences about what best practice really is.

For New England's Bill, from my last comment if you read it once again not all my paragraphs appoint to Tom. I'm not tongue in cheek but I'm with tongue in check to someone whom just hit and run.

Billy Halim - did you READ what I WROTE?

Bill S., August 08, 2008 - 2:28 pm UTC

Sorry, Billy, but if you read my comment you'd see I wasn't referring to you. I have read ALL of the comments - my response was why is everyone so adamant about updating primary keys? Everyone - NOT just you.

I don't hit and run, either. I'm always here even if I'm not commenting. But thanks anyway.

Stupid Billy Halim

A reader, August 10, 2008 - 9:57 am UTC

In some sense, I admire Tom Kyte. I can still admire someone despite great disagreement and some flame wars. If Tom really meant what he said, I admire Tom even more.

Now, for this stupid Billy who has no idea about database design. Have you ever understood that cascaded update can be applied to non-primary key? If you never did it, and you don't really understand unique key, never read books on database design (especially by C. J. Date), you better shut your mouth.

Billy Halim, August 10, 2008 - 10:58 pm UTC

>> Now, for this stupid Billy who has no idea about database design. Have you ever understood that
cascaded update can be applied to non-primary key?

Yes, I am stupid but.. hmm... we're talking about cascaded update on PRIMARY KEY.

If you have any idea or examples, you can share with us like New England's Bill's. I appreciate other ideas and opinions.

Bernaridho is right - and so is Tom!

Tony Andrews, August 11, 2008 - 9:29 am UTC

As so often in discussions about databases, there is a dramatic clash between people talking about relational database theory, and people talking about vendor-specific DBMS best practice.

Bernaridho is correct: nothing in relational database design precludes primary keys being updateable.

Tom is also quite correct to say it isn't a good idea in practice (though saying "if it is updateable, it isn't a primary key" isn't technically correct).
Tom Kyte
August 12, 2008 - 8:27 am UTC

to me, if it is a primary key, it isn't updateable - it isn't allowable to update it.

correct, you technically can, technically you can do tons of stuff - not that you should, but you can.

Two schools of practice - not much theory

Jon Waterhouse, August 11, 2008 - 1:22 pm UTC

I have to admit I'm a person who almost automatically puts a sequence in as a primary key -- there are just too many cases where using the "natural" key would be too messy and impractical.

For example, I work with a database that provides income support to families. We use a sequence number for people.

Maybe name, date of birth, gender and mother's name would be enough to provide a natural key, but then you are talking six columns (assuming two for each name) to identify a person -- so six columns on each payment row, etc. Not a good idea.

Why the mother's name? Well, the children's names change. A lot of the time people have to start receiving income support when the child is born, at which time the child's name is "BABY BOY". Without the mother's name, you have the distinct possibility of having two "BABY BOY"s with the same birth date, so not a unique key, even if you lived in a small enough place where the "real" name and birth date should provide a unique identifier. Not to mention data entry error etc.

And then maybe the mother decides to change her name? Ooch.

And in many other cases there's just no advantage to using a natural key:

This is a payment for request (some #) made on date (some date)to person (some person #). The three key columns are sort of meaningless, so why not just a single meaningless number instead?
Tom Kyte
August 12, 2008 - 8:45 am UTC

... Maybe name, date of birth, gender and mother's name ...

and gender changes.... names change too.

Joe Bloggs, August 12, 2008 - 9:30 am UTC

I agree with the horrendous 6-column PK.

It makes otherwise-simple-to-write table joins into a messy maintenance nightmare !

SELECT 'X'
FROM ABC, XYZ
WHERE ABC.PK = XYZ.PK;

becomes,

SELECT 'X'
FROM ABC, XYZ
WHERE (ABC.PK_COL1 = XYZ.PK_COL1
AND ABC.PK_COL2 = XYZ.PK_COL2
AND ABC.PK_COL3 = XYZ.PK_COL3
AND ABC.PK_COL4 = XYZ.PK_COL4
AND ABC.PK_COL5 = XYZ.PK_COL5
AND ABC.PK_COL6 = XYZ.PK_COL6);

...and that's before applying any filtering clauses !


There are other additional positive side-effects from using a surrogate PK outside the scope of a cascade update.

It can make for easier coding for the developer under certain circumstances. The sequence no. can for example, be used as a faster alternative to potentially more complicated date maths to achieve the same effect.

Joe Bloggs, August 12, 2008 - 10:14 am UTC

While on the subject of natural key joins, why does the 10g NATURAL JOIN clause join over columns of the same name, Tom?

Example,

CREATE TABLE ABC (NAME    VARCHAR2(30), 
                  ADDR    VARCHAR2(30),
                  SEX     CHAR(1),
                  STATUS  CHAR(1))    <-- could mean marital status here

CREATE TABLE XYZ (NAME    VARCHAR2(30), 
                  ADDR    VARCHAR2(30));


SELECT *
FROM   ABC NATURAL JOIN XYZ;

is the same as

SELECT *
FROM   ABC JOIN XYZ USING (NAME, ADDR);

But if someone comes along and adds a new column to XYZ thus:
ALTER TABLE XYZ ADD (STATUS VARCHAR2(20));

possibly with the intent of STATUS meaning 'Insured Status', then every single piece of code written using the natural join syntax above will be voided, since a natural join now yields this,

SELECT *
FROM   ABC JOIN XYZ USING (NAME, ADDR, STATUS);

Arggghhhh!!!

Moral of the Story: Never use NATURAL JOIN !!!



I guess you're gonna say Tom, it's all in the ANSI standard and Oracle Corp. is just following that right?!?

Personally, I had hoped (when I first saw it in the docs.) that a natural join would have used the data dictionary-defined integrity constraints, so one could just go,

SELECT *
FROM   ABC NATURAL JOIN XYZ;

and that loverly 6-column data dictionary-defined primary key integrity constraint would be joined with its correspondingly loverly 6-column data dictionary-defined foreign key integrity constraint naturally with no further coding effort, but alas, twas not to be....
Tom Kyte
August 13, 2008 - 4:33 am UTC

because the ANSI standard said to do that.

Never use a natural join without actual "on" conditions....

Column Names

SeánMacGC, August 12, 2008 - 12:23 pm UTC

Arggghhhh!!!

Moral of the Story: Never use NATURAL JOIN !!!


And never use something as generic as 'Status' as a column name perhaps (though marital_status would be fine)!

A reader, October 01, 2009 - 3:01 am UTC

Could you please reupload the cascade update page http://asktom.oracle.com/~tkyte/update_cascade/index.html or at least make the sql scripts available?

ON UPDATE- reality vs theory vs context

Evan, October 28, 2009 - 11:56 pm UTC

I disagree completely about taking out ON UPDATE and your reasons for doing so. I have worked in a few companies where we use SSN's as a primary key- they are unique and they used as foreign keys in other tables containing tens of thousands of people and are a natural choice short of creating a user ID, which isn't possible due to the extent of the records and the interaction between other systems, paper forms, etc. We would have at least a few SSN change request each week and if we didn't have the ability to change those entries quickly and easily it would be a huge waste of time. Or take for example an employee table where a cyclical foreign key, supervisor_ssn to itself needs to be cascaded on update when say the super is replaced or leaves, etc. I think that in practice you should try to limit cascade on updates but it is sometimes a necessary evil that saves time and energy. But it shouldn't be a feature that is denied to the designer. The earlier person had a point that you didn't address which was that there is a difference between imperative and declarative integrity and Oracle should at least provide support for it. I mean, its not good programming practice to test an integer if it is true or false but most languages support that kind of casting as an analogy. ...so there... I guess.
Tom Kyte
October 29, 2009 - 8:32 am UTC

... I have worked in a few companies where we use SSN's as a primary key ...

that is, was and will be a major design flaw - won't it.

First and foremost in the year 2009 - guess what, using SSN as a key like that would be *not smart* from a privacy compliance perspective would it. SSN and other identifying data like that must be obscured from testers and developers, encrypted in many cases on disk, etc. Using something sensitive as a primary/foreign key is *not smart*

Secondly, everyone knows SSN can change. This is NOT NEW. This has been true forever.


If you believe they are a natural choice for a userid, we need to revisit that thought.


... We would have at least a few SSN change
request each week and if we didn't have the ability to change those entries
quickly and easily it would be a huge waste of time. ...

and you just confirmed why SSN as the choice of a primary key would be *stupid*.



... Or take for example an
employee table where a cyclical foreign key, supervisor_ssn to itself needs to
be cascaded on update when say the super is replaced or leaves, etc. ...


eh? how does that work. You would update a primary key of a record that no longer exists? I don't get it. If the supervisor leaves (assuming this is an employee database) - well, their record would not leave, (retention periods of 7-20 years mandatory for that kind of stuff). So, you would NOT be updating their primary key would you (answer: no, that was a rhetorical question completely). And if you didn't have the retention issue - would you *really* update the primary key (answer: of course not, how could you - you cannot take the fired supervisors record and assign it some other key - that other key presumably BELONGS TO A SUPERVISOR THAT ALREADY EXISTS). So, bad example - you would never do this (for the simple reason that *it would not ever make sense to even thing about


so there. right back at you. I will not apologize for the bad design decision to use sensitive information that must be protected (hence you want it in as FEW places as possible, not scattered all over the place) and that changes frequently (as you pointed out) as a primary key. You missed the design boat as they say.





When will the cascade update be finished?

Russ, November 02, 2009 - 6:23 pm UTC

Like so many proponents of DB theory, Bernaridho would like to change the world for the better. I can only imagine his or her boss watching over their work asking, "when will this Primary Key cascade update be finished", and the response of, "the system cant do what i want it to do" and "you need to rethink your entire information strategy."

If you're planning on being an academic and changing the system from within you're welcome to it. Real change arrives from innovation - think laterally and change your design of primary keys!

Yes, but...

A reader, December 20, 2010 - 12:13 pm UTC

I sympathize with Evan. The thing about sensitive data is an invalid criticism - SSN is just an example.

First, with large external data sources, it can be a nightmare having generated a surrogate key to have to keep matching new data sets to it. I have done this and had cause to regret it, even though it was "best practice." There are probably better ways than I have thought of to handle the merge, but I haven't had time to track them down, and this is legitimate when you're understaffed.

Second, you don't always have the choice of redesigning the schema. Even if it's your schema and you're in complete control of it with no developers affected, there's not always the time. This should be obvious.

Oracle offers many features which can support bad practices, but which are standard and which people rely on. This would be a prime case for adding another one; it would save a lot of people a lot of time and headache.
Tom Kyte
December 20, 2010 - 1:08 pm UTC

the criticism about SSN as a primary key is a valid criticism.

Did you know that there are tons of DUPLICATE SSN's.
Did you know that exposing the SSN as a primary key is a security issue.
Did you know that SSN's are not immutable - they are not candidates for primary keys.


I don't like the term 'best practice' myself - there is no such thing as a universal 'best practice'. If there was, it would be hard coded into the software

"... which can support bad practices ..." and conversely can support good practices and mediocre practices and everything in between.


But we need it, we really do

David Rosen, January 19, 2011 - 11:12 am UTC

It took me a long time to read the whole discussion that has been going for 8 years now. Funny how oracle has not come with the feature since the first question was asked.

I am coming from SQL server and I googled into your discussion asking the same question. I don’t care much about theory, but from my experience this feature of update cascade is a must. YES, I don’t see a way to manage a big database without this feature.

Whether it is a primary key or not we must understand that sometime even a “holy” primary key is simply wrong and needs correction. In one of my systems I manage a publishing house and they have their way to set a book’s ID number. Soon after the key record is created, piles of data are referencing that record. And then, somebody finds out that the book ID is wrong because actually it belongs to a different property or whatever. But who cares. As long as we the ability to change the primary key and let the database change all records in all tables that has that foreign key with update cascade. This way we don’t even need to know which tables are participating in the process.
I do believe that eventually Oracle will support this feature.

some example to have on update cascade

Darius, May 06, 2011 - 4:42 am UTC

CREATE TABLE users_tbl(
id NUMBER(16) NOT NULL,
user_type VARCHAR2(10) NOT NULL,
<...>
);
ALTER TABLE users_tbl ADD CONSTRAINT pk_users PRIMARY KEY (id) USING INDEX;
ALTER TABLE users_tbl ADD CONSTRAINT uq_users_type UNIQUE (user_type, id) USING INDEX;
ALTER TABLE users_tbl ADD CONSTRAINT ch_user_type CHECK (user_type IN ('Employee', 'Manager', 'Client', 'NonMember'));


CREATE TABLE accounts_tbl(
id NUMBER(16) NOT NULL,
user_id NUMBER(16) NOT NULL,
user_type VARCHAR2(10) NOT NULL,
<...>
);
ALTER TABLE accounts_tbl ADD CONSTRAINT pk_accounts PRIMARY KEY (id) USING INDEX;
ALTER TABLE accounts_tbl ADD CONSTRAINT ch_accounts_type CHECK (user_type IN ('Client', 'NonMember'));
ALTER TABLE accounts_tbl ADD CONSTRAINT fk_accounts_holder FOREIGN KEY (user_type, user_id) REFERENCES users_tbl(user_type, id) ON DELETE CASCADE;

Organization has several types of users and only Client and NonMember has accounts. To ensure the accuracy of the data, FOREIGN KEY + check CONSTRAINT of "id" and "user_type" created on table accounts_tbl.
but after some time client of type NonMember become member and we have to change user type to "Client" in table users_tbl. So we need to disable ar defer Fk to have ability to chenge user type. It would be nice to have "on update cascade"

Update cascade

Anish Sahare, February 09, 2012 - 5:21 am UTC

As we us "on delete cascade"
same way can we use "on delete update "

Can we use on Update Cascade

Anish Sahare, February 09, 2012 - 6:44 am UTC

As we use "on delete cascade"
same way can we use "on updade cascade"


Tom Kyte
February 09, 2012 - 9:49 am UTC

no.

A reader, March 20, 2012 - 2:10 pm UTC

Tom, to preface, your responses and the information you've provided over the years on this site has been incredibly helpful for me. I've learned more than I'd ever need to know about Oracle internals merely by reading through the numerous questions here. I find your responses to typically be accurate, direct, and on the ball. However, I occasionally find that some of your answers are not as forthright, especially regarding features and functionality that Oracle lacks, and this is one such case.

You object to ON UPDATE CASCADE for foreign keys on the principle that PRIMARY keys should not be updated. Just because one possible use of a foreign key is to reference a PRIMARY key does not mean that it is the only possible use of a foreign key. You do not address the fact that in Oracle, foreign keys can also reference UNIQUE constraints. The function of ON UPDATE CASCADE would equally apply whether the referenced column is PRIMARY or merely UNIQUE. I fail to see how your objection would be valid in the context of updating columns specified in one or more UNIQUE constraints.

If foreign keys should only reference a PRIMARY key, and any other use of it is bad design, the initial objection then does not hold weight. As Oracle allows foreign keys to reference a UNIQUE constraint, then there's no theoretical reason to not have ON UPDATE CASCADE as well. The functionality provided by ON UPDATE CASCADE is not about database design; it's about maintenance and maintaining data integrity over time. That updates should not be issued to a PRIMARY key column is in and of itself irrelevant to the conversation.

Yes, it can be done programmatically, and I see that you've done so with a package. As convenient as that is, the package is only a stop-gap measure, not to mention that the implementation is also incomplete. ON UPDATE CASCADE needs to work for UNIQUE constraints as well, and the package specifically states that this does not work. It only goes to illustrate that user-space code cannot replace actual functionality. And it should not be necessary to have to write external code to supplement a RDBMS supposedly with RI support.

To have to resort to programmatically maintain one aspect of RI defeats the very purpose of having RI in a RDBMS. RI is as critical an aspect of data management as strong typing and integrity constraints. To have to maintain one aspect of RI elsewhere means that the feature is incomplete and inadequate, and it might as well be entirely maintained elsewhere.

Even if assuming Oracle's feature set philosophy leans towards a particular type of database design, that Oracle lacks an auto-incrementing numeric column data type or the ability to tie a sequence to a column, a necessity for any design pattern that relies on using surrogate keys as opposed to updating natural keys, says that the system is design-pattern agnostic, if actually unfriendly towards the prescribed pattern. In making your design assertion, it seems you are trying to fit a square peg that's slightly too big into a round hole that's slightly too small.

Regardless, to justify the lack of a feature by some potential database design problem is dishonest. It does not address what functionality or lack thereof the feature provides, instead diverting the response to reference an abstraction that the remainder of the system does not adhere to anyway.
Tom Kyte
March 20, 2012 - 3:36 pm UTC

a primary key is immutable. If your primary key is not immutable, it is not a primary key.

I don't understand why this is hard to understand?


you say "You object to ON UPDATE CASCADE for foreign keys on the principle that PRIMARY keys should not be updated. " but then put forth many *principles* yourself. Sort of circular.

I have my principles, you have yours, they do not agree. My principle that a primary key is immutable is one of the founding thoughts behind the relational database - it is what it is.

I've had posted for years (before asktom even) how to do this (update cascade). I still can have my principles and say "bad idea, DO NOT DO IT".

And it is rather trivial to do the update cascade since 8i with deferrable constraints. I've talked of it, I've demonstrated it.

IT DOES NOT MAKE IT A GOOD IDEA.


To have to resort to programmatically maintain one aspect of RI defeats the very purpose of having RI in a RDBMS.

No it isn't, the database *ENFORCES* integrity, the program has ALWAYS had to programatically MAINTAIN IT. If I put a check constraint on something - the database doesn't automagically fix your data - you programatically put in correct data or get an error and fix it. It has always been that way.

This is *NOT* programatically maintaining one aspect of RI, you've ALWAYS had to do this - always. You've ALWAYS had to create the parent before the child (well, deferrable can change that). You never had the parent automagically created when the child was created did you ? Same thing as you are asking for here.


And it should not be necessary to have to write external code to supplement a RDBMS supposedly with RI support.

you have missed the point. the database ENFORCES RI, it does not automagically make your data conform to it.

I hate (despise, which it didn't exist, would love to get rid of) on delete cascade. I fell even more so about update cascade. automagic side effects (like triggers provide) are the most evil things I see in code. Hate them, always will. Didn't used to when I was very young - 15-20 years ago. Experience has shown time and time and time and time again - they are beyond evil. they seem really cool - but they are beyond evil.


If it came up in a discussion with development that they were considering update cascade, I would still be arguing against it.

and asking for on delete cascade to be removed.


and we'll have to agree to disagree on the automagic numbering of columns - anothre thing I despise. Sequences - in my opinion (and yes, I've programmed and used informix, sqlserver, many others) rock and roll. currval, nextval - best way to approach it.

Nikhil, May 26, 2012 - 6:22 pm UTC

I am a database novice. In order to update a field in primary key, we can disable the foreign key constraint, then update the fields in the PK column and FK column. Then enable FK.
Tom Kyte
May 27, 2012 - 12:35 pm UTC

that would not be a good approach. see the original answer - deferrable constraints would be more appropriate.

update cascade

A reader, August 06, 2012 - 10:13 pm UTC


There should be "on update cascade" "automagically"

Hemau, September 06, 2012 - 9:37 am UTC

Just because you didn't find a use for it, it doesn't mean is't a bad feature. In the real world requirements change and you might find yourself after 5 years with a PK that has to change its value because the old DB design cannot be changed.

Now you are going to tell me that this was "bad" design?
Also in you cockyness you opose a feature that would save me (and many others) from a ton of work?

Good job!
Tom Kyte
September 10, 2012 - 7:53 pm UTC

it sounds like you are modifying MANY, MOST or ALL of the primary keys (bad design, design changing, mass update)

in which case I would say "update would be the wrong statement to use to do this bulk, batch, upgrade - use create table as select - DDL - bypass the OLTP redo and undo"

update and delete - great for a few rows. update and delete - stink for more than a few rows.



give me a real world example - and I'll give you my answer. Bake the example fully.

give me a half baked hypothetical and I'll just be the way I am.


Documentation on On Cascade

Snehasish Das, November 08, 2012 - 3:30 am UTC

Hi Tom,

Oracle Documentation says
http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#i4665

"•Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted."

So it says when the referenced data is updated the Dependent data is also updated.

Is this Wrong ? .

If its correct then Should we go for this feature (oracle in built) or for your method of Deferrable Index/Costraint.

Regards,
Snehasish Das
Tom Kyte
November 08, 2012 - 8:23 am UTC

read on just a little bit further

http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref2973

we do not implement "on update cascade" in referential integrity.


the paragraph you pointed to just describes constraints in general - from an ANSI description, they were not talking about what is implemented.

Apologies for above post

Snehasish Das, November 08, 2012 - 3:55 am UTC

Hi Tom,
Apologies, I got the answer.

•FOREIGN KEY constraints for the rules associated with referential integrity. Oracle supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including:

◦Update and delete No Action

◦Delete CASCADE

◦Delete SET NULL


One question though. How do you remember so many things of Oracle which is so vast and each day comes up with new features. Please share that tip.

Regards,
Snehasish.
Tom Kyte
November 08, 2012 - 8:28 am UTC

One question though. How do you remember so many things of Oracle which is so
vast and each day comes up with new features. Please share that tip.


I specialize. I sort of concentrate on the database.

Also, I work with a very broad set of customers - encountering a large set of issues - solving a wide variety of problems. Meaning - I've seen a lot of situations, experienced them, multiple times - it makes it "stick"

cascade update again!

Vinny, February 07, 2013 - 3:44 pm UTC

Tom I agree with your position on making primary keys immutable. Life on planet Oracle would be a lot easier following that rule. I would also like to state for the record how useful and valuable I find your site.

However I am not sure why you seemed so 'defensive' about Oracle's lack of cascade update feature, given that Oracle is okay with a non-primary unique key in one table being referenced by another table column. A cascade update would be an awesome feature in that case if we ever wanted to change the value in that unique column! What would be your argument against using a cascade update feature in that case?

Sure there is a work around, but that would mean hours or even days of effort! You could also argue, like making your primary key immutable, to not refer a unique key in another table if is not a primary key (not doing something just because you can), however that to me is disingenuous!

Given your experience you will agree, it is almost never the case for any of us to be able to design a database from scratch. That and the fact that a non-primary unique key can be referenced by a foreign key would argue how valuable a cascade update feature would be!

That said, I have happily lived without that feature for years, but a special circumstance made me ask that question and read this chain of thoughts. Appreciate yours, again!

Regards...
Tom Kyte
February 11, 2013 - 8:57 am UTC

if you believe you will update the key, make the constraint deferrable (something that wasn't available when I first wrote the update cascade stuff). Then you can defer the constraint, do your updates to the primary/unique key and cascade away - and then set constraints all immediate to verify all is good and commit.

I'm not a fan of deferrable constraints - use them only when necessary.
http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

THere is no link anywhere to download the ON UPDATE CASCADE package

Terry, August 23, 2014 - 10:05 am UTC

Please provide the link to download the ON UPDATE CASCCADE package.
Thank you !!
Terry

Possible use for On Cascade Update?

James McGilvary, April 30, 2021 - 3:58 am UTC

Hi Tom and all, I'm currently a CIS student and one of the courses does cover the use of SQL and primarily using Oracle. So, please forgive me if I mention something incorrectly here as I'm still learning SQL. Though I did read the response of the initial question and I did think about earlier lessons that involved the use of a vendor table and a unique 3 character code that is used as both the primary key and the identifier of these vendors that other tables reference. Wouldn't a 'on update cascade' be useful here if you decide to change a vendor for a necessary item for a plausible reason? An example I'll draw from my course is a lunch menu for the business and different vendors provide different items, and you're tasked to update the database because they found a better-priced vendor that sells the same thing. So all you'd have to do is update the vendor table which would require changing the vendor name and unique identifying code that's referenced to other tables.
Now this isn't a limited thing to lunches obviously, this can be utilized for any vendor/supplier table if the company has this kind of database. An 'on update cascade' would be useful in that case would it not?
Chris Saxon
April 30, 2021 - 10:14 am UTC

ON UPDATE CASCADE is part of the SQL standard. But it is a risky operation.

A row in a parent table could be referenced in tens of tables and thousands, perhaps even millions of rows. So updating one row could lead up changing huge numbers of other rows in the database. This could easily this could make the application unusable while the process runs.

So even if Oracle Database does implement this feature one day, it would be unwise to use it!

An example I'll draw from my course is a lunch menu for the business and different vendors provide different items, and you're tasked to update the database because they found a better-priced vendor that sells the same thing. So all you'd have to do is update the vendor table which would require changing the vendor name and unique identifying code that's referenced to other tables.

In this case the business has found a new vendor for their menu, right?

If so, the correct thing to do here is:

- insert a new row in the vendors table
- update the menu to use this new vendor

Updating an existing row in the vendors table to another company is changing the meaning of that row. In general this is a bad idea. Sure doing this with ON UPDATE CASCADE saves you from having to manually update the menu.

But do you really want to do this for all the child tables of vendors? e.g. their addresses? If you did this, the new vendor points to the old vendors address!

This highlights another risk of ON UPDATE CASCADE. It may make your life easier for the one table you want to change, but it's easy to overlook the other tables where this is completely the wrong thing to do.