Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Chirag.

Asked: February 22, 2007 - 10:32 am UTC

Last updated: December 14, 2007 - 3:07 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I have emp table and many child tables of it.
and those child tables have again many child tables.

i want to update employee code in parent table emp. i want all child table update automatically (including child of child also).

e.g. table scripts

create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10)
);
alter table EMP
add constraint PK_EMP primary key (EMPNO);

create table EMP_LOAN
(
EMPNO NUMBER(4) not null,
LOAN_NO NUMBER(2) not null
);
alter table EMP_LOAN
add constraint PK_EMPLOAN primary key (EMPNO, LOAN_NO)
using index ;
alter table EMP_LOAN
add constraint EMP_LOAN_FK1 foreign key (EMPNO)
references EMP (EMPNO);

create table EMP_LOAN_TRA
(
EMPNO NUMBER(4) not null,
LOAN_NO NUMBER(2) not null,
TRA_NO NUMBER(2) not null,
AMOUNT NUMBER not null
);
alter table EMP_LOAN_TRA
add constraint PK_EMP_LOAN_TRA primary key (EMPNO, LOAN_NO, TRA_NO)
using index ;
alter table EMP_LOAN_TRA
add constraint EMP_LOAN_TRA_FK1 foreign key (EMPNO, LOAN_NO)
references EMP_LOAN (EMPNO, LOAN_NO);

what is easy way to empno column ??? e.g. from E01 to E1001

From
Chirag

and Tom said...

well, as we all know - primary keys are to be immutable - unchanging, constant.

So, it would see your "model is a mess" if you feel the need to update a primary key (and have it cascade).

If you want this to be "automatic", you won't update the primary key. There are methods via triggers, but I'm very very very much against that - not going to go there anymore.

You can use deferrable constraints and a stored procedure.



ops$tkyte%ORA10GR2> create table p ( x int primary key );

Table created.

ops$tkyte%ORA10GR2> create table c1 ( x constraint c1_fk_p references p deferrable, y int, primary key(x,y) );

Table created.

ops$tkyte%ORA10GR2> create table c2 ( x int, y int, z int,
  2                                    constraint c2_fk_c1
  3                    foreign key(x,y)
  4                                    references c1 deferrable,
  5                                    primary key(x,y,z));

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure cascade_p_c1_c2( p_old in int, p_new in int )
  2  as
  3  begin
  4          execute immediate 'set constraint c1_fk_p deferred';
  5          execute immediate 'set constraint c2_fk_c1 deferred';
  6
  7          update p set x = p_new where x = p_old;
  8          update c1 set x = p_new where x = p_old;
  9          update c2 set x = p_new where x = p_old;
 10
 11          execute immediate 'set constraint c1_fk_p immediate';
 12          execute immediate 'set constraint c2_fk_c1 immediate';
 13  end;
 14  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into p select rownum from all_users;

32 rows created.

ops$tkyte%ORA10GR2> insert into c1 select rownum, rownum from all_users;

32 rows created.

ops$tkyte%ORA10GR2> insert into c2 select rownum, rownum, rownum from all_users;

32 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec cascade_p_c1_c2( 5, 100 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select * from p where x = 100;

         X
----------
       100

Rating

  (11 ratings)

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

Comments

Avoiding triggers

Michel Cadot, February 28, 2007 - 4:39 am UTC


It is indeed a much better way than doing things under cover with triggers without speaking about concurrency/serialization.

Regards
Michel

procedure over triggers

Amit, February 28, 2007 - 5:39 am UTC

Hi Tom,

Many Many thanks for so Quick and Logical Solutions.

I just have a query about above posting-
In this case Why we prefer "procedure/deferrable constraints" OVER Triggers.

As we know triggers are automatic whereas in other solution we have to manually call the proc everytime
there is update ( or again we have to write some sort
of trigger on Parent table to monitor updates.)







Tom Kyte
February 28, 2007 - 3:28 pm UTC

because triggers are evil.

because side effects are bad.

because explicit linear code is more maintainable then "happens as a by product of something int he background"

because your goal is to never actually CALL this procedure in real life

because the triggers would add great overhead, that is unnecessary

because the triggers (plural, it takes a minimum of three) would be really hard to code for each table.

because triggers are evil.

magic should be avoided. Experience tells me this.

General Rule

Amit, March 01, 2007 - 8:53 am UTC

Many Thanks Tom for practical advice above.

Are these reasons valid only in Parent/child relationship update OR Triggers are always evil as in General and we should always avoid using triggers and should use proc like above in all cases?
Tom Kyte
March 02, 2007 - 11:35 am UTC

triggers are always evil in general.
I encourage people to avoid triggers whenever possible.


The automagic side effects that just happen in the background make understanding and maintain stuff almost impossible.


Here is a frequent conversation I have with people:

them: oracle is broken, look - I insert into t values(5); when I select, it is not 5, it is 10.

me: got trigger?

them: oh, never mind.

Question the data model

Mike, March 01, 2007 - 1:52 pm UTC

If you have this kind of 'cascade update' requirement, then you should consider changing the data model so that your primary key is something (perhaps an artifical key) that would never have to change.
Tom Kyte
March 02, 2007 - 12:53 pm UTC

my feeling exactly

update a primary key and have it cascade

Chirag Patel, March 02, 2007 - 7:39 am UTC

Hi Tom,

Thanks a lot.

This is Chirag Patel.

You said,

well, as we all know - primary keys are to be immutable - unchanging, constant.

So, it would see your "model is a mess" if you feel the need to update a primary key (and have it cascade).

First thing is this we need to use for implementer not for end user.


I explain u live scenario.
We implemented payroll. 2 week they did data entry.
After entering data of 500 employees he found he has input wrong code for 14 employees.

Because of many child tables we can not tell to user delete this all information of 14 employees and do re-entry.

Tell me what should I do??
¿Model is not mess¿
Yes still I feel to update primary key and have is cascade.

From
Chirag Patel (CEO),
+254-721671460
Nairobi, Kenya
YASH INFOTECH

Tom Kyte
March 04, 2007 - 5:45 pm UTC

so, your data model does not reflect the reality of your data. Your data model is not correct if you have to do update cascades.

Model is not in line with your stated requirements. Model is wrong if you have to update primary key.

We've showed you how to do it however....

To chirag

Sundar M, March 02, 2007 - 4:08 pm UTC

>> he found he has input wrong code for 14 employees
You shouldn't allow the column which is enterable or modifiable as a primary key. It's always wise to hide the primary key from the end user. You can assign a sequence to populate the same.

Integrity constraint and trigger

Jérôme Radix, March 05, 2007 - 4:05 am UTC

Dear Tom,

is there a better way to put an integrity constraint to say, without a trigger, that a relation (r1) must be included into another one (r2) ? This trigger would rollback if a row is inserted into r1 and is not present in r2.

Thank you for your response to this (newbie) question...

Best regards,
Jérôme.
Tom Kyte
March 05, 2007 - 12:55 pm UTC

it is typically impossible to enforce cross - object integrity in a trigger without using the LOCK TABLE command.


Can you better describe what you mean by:


that a relation (r1) must be included into another one (r2) ?


do you just mean a foreign key?

create table p( x int primary key );
create table c( x references p );

Re: Integrity constraint and trigger

Jérôme Radix, March 23, 2007 - 6:49 am UTC

A little relational schema :

t1 ---0,n---<r1>---0,n--- t2

t1 ---0,n---<r2>---0,n--- t3



The integrity constraints : "Elements of t1 in r2 must be present in elements of t1 in r1".

For example, you have the following tables :

t1 (t1id, t1col)
t2 (t2id, t2col)
t3 (t3id, t3col)

r1 (t1id, t2id)
r2 (t1id, t3id)


I want to be sure all t1id in r2 are present in r1.

It's an integrity constraint. A trigger on r2 would just rollback if new.t1id not in (select t1id from r1)

It's not a trigger which do things magically in the background, it only checks the integrity constraint.

I understand your dislike of triggers (because of the performance issues and magic behavior they can introduce). But for an integrity constraint, would you use a trigger or code a transactional API ?

Thank you for your response,

Best regards,
Jérôme.
Tom Kyte
March 23, 2007 - 9:56 am UTC

either an attribute of T1 is to be in R2 or not (either t1 is a child of R2 or it isn't)

and if t1 must belong to r2 and t1 must belong to r1, you just have a foreign key from t1 to r2 and r1. that is all.




Hiding Primary Key

Chirag Patel, March 24, 2007 - 7:56 am UTC

Hello,

Thanks to all

I have primary key on emp_no. I can not hide cos it is appears on payslip and all reports.

ok in my all previous applications my all tables has integer primary.

Following are the examples of database model
============================================

Database Model 1)
==================

-- Create table EMPLOYEE
create table M1_EMPLOYEE
(
EMP_ID NUMBER not null,
EMP_NO VARCHAR2(20 CHAR) not null,
EMP_NAME VARCHAR2(100 CHAR) not null
);
alter table M1_EMPLOYEE
add constraint M1_EMPLOYEE_PK primary key (EMP_ID) using index ;

alter table M1_EMPLOYEE
add constraint M1_EMPLOYEE_UK1 unique (EMP_NO) using index ;


-- Create table EMP_PAYROLL_MTH
create table M1_EMP_PAYROLL_MTH
(
EMP_ID NUMBER not null,
PAYROLL_MONTH_ID NUMBER not null,
PAYROLL_MONTH VARCHAR2(6 CHAR)
);
alter table M1_EMP_PAYROLL_MTH
add constraint M1_EMP_PAYROLL_MTH_PK primary key (EMP_ID, PAYROLL_MONTH_ID) using index ;
alter table M1_EMP_PAYROLL_MTH
add constraint M1_EMP_PAYROLL_MTH_UK1 unique (EMP_ID, PAYROLL_MONTH) using index ;
alter table M1_EMP_PAYROLL_MTH
add constraint M1_EMP_PAYROLL_MTH_FK1 foreign key (EMP_ID)
references M1_EMPLOYEE (EMP_ID);

-- Create table EMP_PAYROLL_MTH_ITEM
create table M1_EMP_PAYROLL_MTH_ITEM
(
EMP_ID NUMBER not null,
PAYROLL_MONTH_ID NUMBER not null,
ITEM_ID NUMBER not null,
ITEM_CODE VARCHAR2(20 CHAR) not null,
ITEM_VALUE NUMBER not null
);
alter table M1_EMP_PAYROLL_MTH_ITEM
add constraint M1_EMP_PAYROLL_MTH_ITEM_PK primary key (EMP_ID, PAYROLL_MONTH_ID, ITEM_ID) using index ;
alter table M1_EMP_PAYROLL_MTH_ITEM
add constraint M1_EMP_PAYROLL_MTH_ITEM_UK1 unique (EMP_ID, PAYROLL_MONTH_ID, ITEM_CODE) using index ;
alter table M1_EMP_PAYROLL_MTH_ITEM
add constraint M1_EMP_PAYROLL_MTH_ITEM_FK1 foreign key (EMP_ID, PAYROLL_MONTH_ID)
references M1_EMP_PAYROLL_MTH (EMP_ID, PAYROLL_MONTH_ID);

Database Model 2)
==================

create table M2_EMPLOYEE
(
EMP_NO VARCHAR2(20 CHAR) not null,
EMP_NAME VARCHAR2(100 CHAR) not null
);
alter table M2_EMPLOYEE
add constraint M2_EMPLOYEE_PK primary key (EMP_NO) using index ;

-- Create table EMP_PAYROLL_MTH
create table M2_EMP_PAYROLL_MTH
(
EMP_NO VARCHAR2(20 CHAR) not null,
PAYROLL_MONTH VARCHAR2(6 CHAR)
);
alter table M2_EMP_PAYROLL_MTH
add constraint M2_EMP_PAYROLL_MTH_PK primary key (EMP_NO, PAYROLL_MONTH) using index ;
alter table M2_EMP_PAYROLL_MTH
add constraint M2_EMP_PAYROLL_MTH_FK1 foreign key (EMP_NO) references M2_EMPLOYEE (EMP_NO);

-- Create table EMP_PAYROLL_MTH_ITEM
create table M2_EMP_PAYROLL_MTH_ITEM
(
EMP_NO VARCHAR2(20 CHAR) not null,
PAYROLL_MONTH VARCHAR2(6 CHAR),
ITEM_CODE VARCHAR2(20 CHAR) not null,
ITEM_VALUE NUMBER not null
);
alter table M2_EMP_PAYROLL_MTH_ITEM
add constraint M2_EMP_PAYROLL_MTH_ITEM_PK primary key (EMP_NO, PAYROLL_MONTH, ITEM_CODE) using index ;
alter table M2_EMP_PAYROLL_MTH_ITEM
add constraint M2_EMP_PAYROLL_MTH_ITEM_FK1 foreign key (EMP_NO, PAYROLL_MONTH)
references M2_EMP_PAYROLL_MTH (EMP_NO, PAYROLL_MONTH);

xxxxxxxxxxxxxxxxxxxxxxxx

about model 1
=============
* the emp_no i can change easily reason is it is uniq key and not referenced in any other table.
* Performance will be better cos of joining tables on integers datatype primary key.
* But for development, maintain and to deploy into data where house is difficult, lenghty.

about model 2
=============
for development, maintain and to deploy into data where house is toooooooooooooooooo easy .

please advise for the follwings.

1) which model is better and why ?
2) is some one provide me better then above ?


Reg
Chirag Patel
Nairobi, Kenya
254-721671460
Tom Kyte
March 26, 2007 - 7:20 am UTC

1) how long is a piece of string? and why?

not knowing your requirements - no one can answer this.

Why not enforce "Immutable" Primary Keys?

Phil, December 14, 2007 - 12:15 pm UTC

Hi Tom,

I keep seeing that you recommend "Immutable" Primary Key constraints - so why not build that into the Database (with backward compatibility of course for "evil" applications)?

I've raised an SR to that effect:

----------------------------------------------------
SR Number 6677903.992 Abstract

Requesting the ability to make Primary Keys "Immutable" with native syntax

### Reason current product functionality is insufficient. ### Most Oracle documentation and Tom Kyte recommends that Primary Key values be "Immutable" - or not ever changing. They recommend this as part of good database design. Oracle Database does not provide this functionality out of the box, however - so why not add it? It will allow one to enforce good database design standards from a built-in Oracle feature.

### Detailed description of the Enhancement Request.
### I am requesting to add the ability to make Primary Key Constraints "Immutable" - therefore not allowing someone to modify them with an UPDATE statement. This of course would be optional - but could be specified with new "IMMUTABLE" or "MUTABLE" keywords like so:
ALTER TABLE test_table ADD CONSTRAINT test_table_immutable_pk PRIMARY KEY (pk_col) IMMUTABLE;

"MUTABLE" would be the default option for backward compatibility. If a Primary Key Constraint was created with the "IMMUTABLE" keyword - an Oracle error should be returned if one attempts to UPDATE the column(s) declared within the constraint like so:

User enters:
"UPDATE test_table SET pk_col = pk_col + 1;"

New Error returned:
"ORA-xxxxx: Table: TEST_TABLE has an IMMUTABLE Primary Key Constraint: "TEST_TABLE_IMMUTABLE_PK" declared upon column "PK_COL". This column cannot be updated."

### How the product can be changed to achieve the desired result.
### Add new syntax to the creation of Primary Keys - specifically "IMMUTABLE" and "MUTABLE" - to allow enforcement of good database design principles from the Oracle Kernel. This allows one to not require Triggers or column level UPDAT privileges for that purpose.

### Reasons to consider the Enhancement Request.
### Oracle prides itself on introducing features in the database that allow one to build-in good design practices into an Oracle Database application. Introduction of Primary Key and Foreign Key Constraints did that initially. This just enhances the Primary Key to enforce its immutability. Good Data Modelers everywhere would like this feature.

----------------------------------------------------

What do you think? Is this an ok idea?

Tom Kyte
December 14, 2007 - 3:07 pm UTC

I'm ambivalent on it.

Ok to good idea? I think so.

Worth the effort (is there a good return on investment)? I'm not so sure... there are other things we should/could do before this.

Updation

JAGDISH, February 06, 2010 - 7:48 am UTC

Very good.......

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library