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

Solution to integrity conflict 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, after the first update is executed, the DBMS checks the integrity and finds a conflict:The foreign key of entry B does not exist..

Note: I have encountered this problem in the Oracle database environment. Oracle cannot set the foreign key to update-level connections, 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:

We need to modify the following SC table foreign key declaration and add the foreign key constraint name to facilitate the subsequent shielding and restoration of foreign key constraints:

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 the SQL alter table statement to block and enable it. 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
Since the statement of modifying the table structure using create table or alter table cannot be used in Oracle stored procedures, it must be dynamically called using execute immediate + SQL Command.
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;