Solution to integrity conflicts when updating primary key values restricted by foreign keys in Oracle stored procedures

Solution to integrity conflicts when updating primary key values restricted by foreign keys in Oracle stored procedures
1. Background

Although we do not advocate modifying the primary key in database operations, there is indeed such a business demand in real life:

Table A has A primary key KA, and table B declares A foreign key constraint for references A (KA). We need to modify the value of KA for an entry in Table A and update the foreign key constraint of Table B.

However, DBMS is executing the firstupdateThen check the integrity to find a conflict:The foreign key of entry B does not exist..

Note: I encountered this problem in the Oracle database environment. Oracle cannot set the foreign keyUpdate-level connection, So only manual processing is required.

2. Examples

A simple example shows that the database has the following three tables:
(1) Student table with student ID (Primary Key), name, and age attributes:

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

(2) The course schedule has the course number (Primary Key), course name, and credits:

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

(3) The student ID table has the following attributes: Student ID, course number, and score. Student ID is the foreign key of the student table's secondary school number, and course number is the foreign key of the course number in the course list:

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 modify the student's student ID. If there is a score entry in the score table, the above integrity conflict will occur.

3. Solution

I think of two ideas:

• Block (or delete) the SC foreign key constraint, modify the Student table Student ID, and update the Student's Student ID in all SC instances to ensure consistency (which is guaranteed by the DBA, finally, the SC table foreign key constraint is restored (or added.
• Take out the score entries of all students in SC and put them in the zero-hour table/external variables. Then, delete these records in SC, modify the Student table Student ID, and ensure consistency (also guaranteed by DBA) when the data in the zero-time table or external variables is modified, then all the data is inserted into the SC table.

The previous method (Block modification and restore) Is relatively simple. The following describes the steps:

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

The two Foreign keys are named sidfk and cidfk respectively.
2. Mask and enable foreign key constraints:
Use SQLalter tableThe statement is blocked and enabled. Set S # _ new to the new student ID, and S # _ old to the old student ID:

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 in Oracle
Because Oracle stored procedures cannot be used directlycreate tableOralter tableStatements used to modify the table structure.execute immediate+SQL CommandDynamic call.
The complete stored procedure is as follows:

create or replace procedure ChangeStuId(S#_old in integer, S#_new in integer)       asbegin           execute 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;