Renaming of Foreign keys in Oracle

The ORACLE tutorial is: Renaming the foreign key of the Oracle system table.

The Foreign keys in Oracle tables are the means to ensure the integrity of the system's reference, and the integrity of the Reference refers to the constraints that are satisfied by the columns distributed in the two tables with the master-slave nature. The foreign key involves two tables, one of which is the parent table and the other is the child table.

The parent table is the basis for reference constraints. It checks the valid data of this table to determine whether the constraints are true. It is the condition for reference constraints and affects constraints, without any constraints.

A child table is an object that references constraints. When a child table changes, if a new data input exists, the child table compares the valid data in the parent table, to determine whether these changes meet the constraints. If they do not, the changes will be rejected.

In practical application systems, developers generally need to define a large number of foreign keys to ensure system integrity. However, if the names of foreign keys are not standardized, such as names automatically generated by the system, it will cause a lot of trouble in future system operation and maintenance. For example, if a foreign key error occurs when a system loads a large amount of data or performs some data conversion operations, the system prompts a foreign key error, it is impossible to directly locate the Foreign keys between the two tables. It takes a lot of time to find the Parent and Child tables of the foreign keys that cause the error, then we can further determine that the record violates the foreign key constraints. Generally, we use this naming rule to name the foreign key FK_Child_table name_Parent_table name. Since the maximum length of the foreign key name is limited to 30 characters, the child_table_name and Parent_table name are not necessarily the same as the original table, you can take some shorthand, but the name must be able to reflect the constraints of the two tables. Here, Child_table name refers to the sub-table, that is, the constraint table. Parent_table name refers to the parent table, that is, the table to be constrained.

Next we will discuss in detail how to change the nonstandard foreign key name in the application system to a standardized foreign key name. Before the discussion, you should note that it takes a long time to complete the following operations, so you must plan to complete it when the system is idle. At the same time, the foreign key is renamed. The method used here is to first Delete and then recreate, involving the operation of deleting Application System Objects. Therefore, before the operation, you should back up the application system for security reasons.

[NextPage]

1. Generate the current foreign key report form of the system

First, generate a report on all foreign keys in the current system mode. The SQL script is as follows:


This script generates all foreign keys in the mode where they are located, including foreign key names, parent table names, child table names, and referenced column names. Run the script in SQL/PLUS. Before running the script, you can export the SPOOL to a local file. At the same time, it should be noted that if there are many and complex Foreign keys in the application system, the script will run for a long time.

Ii. Generate a script to delete the foreign key automatically named by the System

In SQL/PLUS, run the following script to generate and delete all the foreign keys automatically named by the system (that is, the foreign key name is prefixed with SYS), which is the same as generating the foreign key report, upload the generated script spool to a file.



Run the script and the system generates the following script to delete the foreign key:
3. Generate a re-create and delete foreign key script

In SQL/PLUS, run the following script to generate a re-create and delete foreign key script:


Run this script. The system generates the following foreign key creation script:

Alter table dj_nsxx add constraint foreign key name foreign key (RYDM) REFERENCES DM_GY_SWRY (RYDM );

Replace the name of the foreign key in the naming rules described above


Iv. Foreign key renaming

After the above two scripts are generated, first run the script to delete the foreign key automatically generated by the system in step 2, and delete the nonstandard foreign key in the system, then, run the script generated in step 3 to create the Foreign keys and recreate these deleted Foreign keys, which will rename the nonstandard Foreign keys.

V. System check

After the operation is complete, re-Execute Step 1 and generate a foreign key report form of the application system for comparison and check. If it is correct, the name is renamed successfully. Otherwise, you can find the cause.

Previous Page