Subject Re: [firebird-support] doing cascade update manually?
Author David Garamond
Martijn Tonies wrote:
>> create table t1 (id int not null primary key, i int, unique(i));
>> create table t2 (id int not null primary key, i int references t1(i));
>> insert into t1 values (1,1);
>> insert into t1 values (2,2);
>> insert into t2 values (1,1);
>>t2(i) is not declared 'cascade update'. Can I update t1(i) like this
>> update t1 set i=3 where id=1;
>>as well as updating t2(i)? In other words, I want to do cascade
>>manually/case-by-case in this situation (sometimes I want to forbid
>>changes to a referenced unique column, and sometimes I want to allow
>>that change and propagating the changes to all referencing columns as
> well).
> As you might have noticed, constraints are not deferred
> until "commit" time. So if, while updating, you are violating
> any constraints, the update will fail. So there's no way to
> do this.

Thanks, Martijn.

Would declaring all foreign keys as CASCADE UPDATE be a "good practice"?
Some of my foreign keys are referencing unique columns, not PK, and
these columns sometime change values (though infrequently). All of my PK
usually don't change their value, but many times it's more convenient to
refer to a UC instead of a PK.

Sometimes I want to check whether the change in unique column value is
"sensible", and if it is, I want to allow that (plus cascading).