Oracle foreign key cascade Update-latency constraints and trigger implementation

Oracle Foreign keys only have cascade deletion and no Cascade update, but sometimes such functions may be required in the system, so the implementation process is recorded here.

There are two constraints in Oracle: latency constraint and non-latency constraint. The so-called non-Delay Constraint means that when a record is changed, it will immediately check the constraints and check whether the modification cannot be performed because it violates certain constraints, the latency constraint means that the constraints will not be viewed when the modifications are just made. The constraints will be checked only when the modifications are submitted. This feature is used for Oracle cascade updates.

1. First, set the foreign key as a latency constraint. The specific statement is as follows:

 
Alter table test_2 add constraint test_2_test_1_fk1 foreign key (fkid) References test_1 (ID) on Delete cascade deferrable;
 

Add a foreign key to the test_2 table, reference it to test_1, and set it to cascading deletion and latency constraint (deferreable ). If you are using the Oracle SQL developer tool, you can check whether the setting is successful in the constraints column.

2. Set the trigger

Set a trigger on the test_1 table. When the primary key of the table changes, the fields in test_2 are updated in cascade mode.

 
Create or replacetrigger test_update after update on test_1 for each rowbegin if: Old. ID <>: New. ID then update test_2 set fkid =: New. ID where fkid =: Old. ID; end if; end;
 

The cascade update of Foreign keys can be implemented in steps 1 and 2. The basic idea is that when the primary key of the master table is updated, the delay constraint is imposed and the check is not performed immediately. After the trigger is updated, the foreign key of the attached table is updated cascade, and then submitted, this will check the constraints. Because the foreign key has been cascade updated, there will be no problem here.