Oracle update statement not updating
If your primary key changes, it is not a primary key -- a candidate key if you will but not a primary key. Thanks, Arash June 08, 2004 - 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. If you find yourself constantly in update cascade mode, you've done something *wrong*.
Your right, I should have called it a "candidate key". 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. Dear Tom, Cascade update will be a good feature, if you can incorporate in later relaeases. One fellow put customer code ' A123' and 'A123' for customer master. if you need to do it every now and again, once in a very blue moon -- look at DEFERRABLE constraints.
An update to a key should not be without a where condition.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" Your update cascade is very usefull. 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. The update statement "update dept set deptno = deptno 10" will not work whereas the update "update dept set deptno = deptno 1" will.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.
The Ask TOM team is taking a break over the holiday season.