Oracle stored Procedure Update Integrity conflict resolution when the primary key value is constrained by a foreign key

1. Issue background

Although we do not advocate changing the primary key 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 for integrity after running the first article update to find a conflict:the foreign key for the purpose of B does not exist .

Note: I have encountered this problem in Oracle database environment. Oracle very sore can't set the foreign key to update级连 . So there is only manual processing.

2. Illustrative examples

Use a simple sample description. The following three tables are in the database:
(1) Student table. Attribute has a 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 a student's school number, assuming that there is a change in the score table of the student's score entry, then the above-mentioned integrity conflict.

3. How to Solve

There are two ideas that I think of:

• Block (or remove) the SC table foreign KEY constraint, change the student number, and update the student number in all SC with consistency (guaranteed by our DBA), and finally restore (or join) the SC table foreign KEY constraint.

• Take out all of the student's score entries in SC in the zero table/external variable and then delete these records in SC, change the student number, and insert the SC table when the data in the table/external variable is guaranteed to be consistent (same as our DBA guarantees).

The previous method ( mask change and restore ) is simpler. The following steps are further explained:

1. We need to change the following SC table foreign key Declaration, add the name of the foreign key constraint, so that we might block and restore 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:
Use 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
Because Oracle stored procedures cannot be used directly create table or alter table a class of statements that alter the table structure. Requires execute immediate + SQL Command dynamic invocation.
The complete storage steps are 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 Integrity conflict resolution when the primary key value is constrained by a foreign key