Oracle stored procedure Update the Integrity conflict solution when the primary key value is constrained by a foreign key

1. Issue background

Although we do not advocate the modification of primary keys in database operations, we do have this business requirement in real life:

表A有主键KA，表B中声明了一个references A(KA)的外键约束，我们需要修改A中某条目KA的值并且更新B中外键约束。

However, the DBMS checks the integrity after performing the first article update to find a conflict:the foreign key for the purpose of B does not exist .

Note: I encountered this problem in Oracle database environment, Oracle is very painful to set the foreign key is not update级连 , so only manual processing.

2. Illustrative examples

In a simple example, there are three tables in the database:
(1) Student table, attribute number (primary key), name and age:

create table Student(S# integer primary key, sname varchar2(20), age integer);

(2) Curriculum, attributes include course number (primary key), course name and credits:

create table Course(C# integer primary key, cname varchar2(20), credit integer);

(3) The score table, the attribute has the school number, the course number and the score, the school number is the student table secondary School key, the course number is the curriculum in the course extra key:

create table SC (       S# integer foreign key (S#) references Student(S#) on delete cascade       C# integer foreign key (C#) references Course(C#) on delete cascade       score integer);

We need to change the student's number, and if there is a change in the score table, the above integrity conflict will be raised.

3. Solution

There are two ideas that I think of:

• Block (or remove) the SC table foreign KEY constraint, modify the student number, and update the student's number in all SC with consistency (guaranteed by our DBA), and finally restore (or add) the SC table foreign KEY constraint.
• Take out all of the student's score entries in the SC in the zero table/external variable and then delete these records in SC, modify the student number, and insert the SC table when the data in the zero/external variable is guaranteed to be consistent (also our DBA guarantees).

The previous method ( mask modification and re-recovery ) is relatively simple and the following steps are further explained:

1. We need to modify the following SC table foreign key Declaration, add the name of the foreign key constraint, to facilitate our subsequent masking and recovery of foreign KEY constraints:

create  table  SC (s# integer , C # integer , Score Span class= "Hljs-keyword" >integer , constraint  sidfk foreign  key  (s#) references  Student (s#) on  delete  cascade , constraint  cidfk foreign  key  (C #) references  Course (C #) on  delete  cascade );   

Here the two foreign keys are named SIDFK and CIDFK respectively.
2. Shielding and opening foreign KEY constraints:
Using SQL alter table statements to implement masking and opening, set S#_NEW is the newly-learned number, S#_old is the old school number:

alter table SC disable constraint sidfk;update Student set S# = S#_new where S# = S#_old;update SC set S# = S#_new where S# = S#_old;alter table SC enable constraint sidfk;

3. Using Stored Procedures on Oracle
The use of + dynamic invocation is required because Oracle stored procedures cannot be used directly create table or alter table a class of statements that modify the table structure. execute immediate SQL Command
The complete stored procedure is as follows:

 Create or replace procedure changestuid (s#_old  in integer , S#_new  in integer) asbeginexecute immediate ' ALTER TABLE SC disable constraint sidfk ';                    update Student set s# = S#_new where s# = s#_old;         update SC set s# = S#_new where s# = s#_old;Execute Immediate ' alter table SC enable constraint sidfk'; end; 

Oracle stored procedure Update the Integrity conflict solution when the primary key value is constrained by a foreign key