Renaming of table foreign keys in Oracle systems

The foreign key of the table in Oracle is a means to guarantee the referential integrity of the system, while referential integrity refers to the constraint relationship with the principal and subordinate properties that are satisfied by the columns distributed in the two tables. The foreign key involves two tables, one of which is called the parent table, and the other is called a child item table.

The parent table, which is the basis of a reference constraint, is to determine whether a constraint is valid by examining the data in the table, which is the condition of the reference constraint, and affects the constraint without any influence from the constraint.

The subkey table (child table) is the object of the reference constraint, and when it changes, if there is a new data entry, by comparing the valid data status in the parent table to determine whether these changes meet the constraint, and if not, reject the change that is to occur.

In the practical application system, developers to ensure the integrity of the system, the general need to define a large number of foreign keys. However, if the naming of foreign keys is not standardized, such as the use of the system automatically generated name, then in the future maintenance of the system will cause a lot of trouble. such as when the system is running, loading large amounts of data or doing some data conversion operations, etc. when a foreign key error occurs, depending on the foreign key error prompted by the system, it is not possible to navigate directly to the foreign key between the two tables, and there is a lot of time to find the parent and child table of the foreign key that caused the error to occur. You can then further determine that the record violates the foreign KEY constraint. Typically, we use this naming convention to name the foreign key fk_child_table name_parent_table name. Because the maximum length of a foreign key name is limited to 30 characters, the Child_table_name and parent_table name may not necessarily be identical to the original table, but the name must reflect the constraint's two tables. Named here, child_table name refers to the child table, which is the constraint table, parent_table name refers to the parent table, which is the constrained table.

The following is a detailed discussion of how to modify the nonstandard foreign key name in the application system to the foreign key names of the specification. Before discussing it, it is important to remind the reader that it takes a long time to complete the following operations, so be sure to plan to do so when the system is idle. At the same time, the foreign key renamed here, the method is to delete and then rebuild, involving the deletion of application system objects, so before the operation, for security reasons, should back up the application system.

First, the production system of the current foreign key report form

First, generate all foreign key situation reports in the current mode of the system, SQL script as follows:

******************************************************************************
脚本1：列出当前模式下所有外键的报告表，可以将其spool到某个文件中
******************************************************************************/
SELECT RPAD(child.TABLE_NAME,25,' ') Child_Tablename,
RPAD(cp.COLUMN_NAME,17,' ') Referring_Column,
RPAD(parent.TABLE_NAME,25,' ') Parent_Tablename,
RPAD(pc.COLUMN_NAME,15,' ') Referred_Column,
RPAD(child.CONSTRAINT_NAME,25,' ') Constraint_Name
FROM USER_CONSTRAINTS child,
USER_CONSTRAINTS parent,
USER_CONS_COLUMNS cp,
USER_CONS_COLUMNS pc
WHERE child.CONSTRAINT_TYPE = 'R' AND
child.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND
child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME AND
parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME AND
cp.POSITION = pc.POSITION
ORDER BY child.OWNER,
child.TABLE_NAME,
child.CONSTRAINT_NAME,
cp.POSITION;

This script generates all foreign key conditions in the mode, including the foreign key name, the parent table name, the subkey table name, and the referenced column name. Run the script under Sql/plus, and you can spool the output to a local file before running the script. Also note that if the foreign key in the application system is more and more complex, the script will run for a long time.

Second, generate delete system automatically named foreign key script

Under Sql/plus, run the following script to generate all foreign keys that delete the system's automatic naming (that is, the foreign key name is prefixed with SYS), and to generate a foreign key report, spool the build script to a file.

******************************************************************************
脚本2：删除系统自动生成的外键约束条件
******************************************************************************/
SELECT 'ALTER TABLE ' || TABLE_NAME ||' '||
'DROP CONSTRAINT ' || CONSTRAINT_NAME || ' ;'
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'SYS%' AND
CONSTRAINT_TYPE = 'R';

Running the script, the system generates the Delete foreign key script as follows:

ALTER TABLE DJ_NSRXX DROP CONSTRAINT SYS_C000231;