It would be nice to be able to merge a record referenced by Foreign Keys with another and have the server performing a cascade update instead of having to update all the tables referencing this record and then delete the referenced record.
Table Patient (Patient_ID integer not null primary key);
Table Visit (Visit_ID integer not null primary key,
Patient_ID integer not null,
constraint FK_Visit_Patient foreign key (Patient_ID) references Patient(Patient_ID) on update cascade on delete no action);
if Patient_ID 2 is effectively the same patient as Patient_ID 1 and I want to merge those two records, I have to do:
Update Visit set Patient_ID = 1 where Patient_ID = 1; --And the same on the dozen of dependant tables
Delete from Patient where Patient_ID=2;
If this could be done with one action (something like "merge patient set patient_id=:NewValue where Patient_Id=:OldValue) it would save having to create a Stored Procedure to execute each updates on then foreign keys...