Oracle update statement cascade

The table would contain the information a procedure would need to update all tables that are dependent upon a main or master table. Therefore, the table would have to contain the master table name, the dependent table s and in case we can't duplicate the exact column name across all of the dependent tables, the column to update.

The table DDL script in figure 1 meets these requirements. Figure 1: Example DDL to create a cascade update source table. The table by itself would be of little use. Since the data in the table is dynamic i.

Figure 2 shows the commented code for just such a procedure set. Figure 2: The package containing the procedures for cascade update. The Cascade Update Procedures. The procedure uses a standard cursor fetch to retrieve the dependent table names and dependent table column names from the source table shown in figure 1. If desired, the table from figure 1 could be altered to accept an ordering value for each master-dependent set to allow the cascade update to be done in a specific order if required.

The Final Piece, the Trigger. Once the source table and procedures are built, we need to design a trigger to implement against our master tables that automatically fires on update to the target master column.

Figure 3 shows an example of this trigger. The subquery must return exactly one row and one column. The subquery must return exactly one row that includes all the columns listed.

The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on. Any valid SQL expression. Why does it support Cascade Delete but not update? I couldn't answer! As always , could you please let us know?

Thanks, Arash. June 08, - pm UTC. Hence, you need to find something imutable. As always the best answer! Agreed that it is good design NOT to ever use update cascade, but 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.

February 14, - 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. 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. October 05, - pm UTC. Your update cascade is very usefull. I have one question. Is this restriction has been over come or its still there. Updates to primary keys that do not generate 'new' primary keys are not currently supported. For example, take the standard DEPT table. 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. June 27, - am UTC. I pray this never becomes part of the database. Any restrictions that were there - will still be there. 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. July 09, - pm UTC.

How can you have "scientific evidence" about the basic premise. 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.

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?

It's fallacy. What is your basis on claiming that just because the id product can change it's not a key? Again, a baseless accusation. Why they don't? You're playing God here! Why is that? Prove it to us with examples. Otherwise that is just silly, baseless opinion not worthy of thinking. July 29, - am UTC. 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 right - we provide it, it could be done, no need to use dates, numbers, varchar2 30 - right?

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..

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?!? 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. 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 as well as the Relationl Model V2 never say that primary keys are being immutable.

I wonder. And why user of products like SQL Server, Access, Interbase, and others do not ask the vendors to abandon cascaded update? Is it user? I don't think we need other terms here.

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? What do you want to say? 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? Yes, I think about you're wandering here. 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.

And who says integrity is not important? 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. Why should I? Says who? Very bad analogy.

Just the opposite. Read my two paragraphs explaining integrity constraints you just missed to think and address. August 04, - pm UTC. 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. Patient : Doctor, Doctor, my arm hurts when I do this. Doctor : Well don't do it then!

Hi all oracle manias, it's really good discussion. 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.. It's confusing". And your production head will resign asap 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. 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. Well friends.. August 07, - pm UTC. 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. 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. It was an ERP project.