Renaming of foreign keys for Oracle system tables _oracle

The Oracle tutorial you are looking at is the renaming of the foreign key in the Oracle system table.

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.

[NextPage]

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:


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.



Running the script, the system generates the Delete foreign key script as follows:
Iii. generate a foreign key script to recreate the deletion

Under Sql/plus, run the following script to generate the foreign key scripts that recreate the deletion:


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

ALTER TABLE dj_nsrxx ADD CONSTRAINT foreign Key name FOREIGN key (RYDM) REFERENCES Dm_gy_swry (RYDM);

Replace the foreign key name above with a foreign key name named after the canonical naming convention described above, which is


Four, foreign key renaming

After generating the two scripts above, first run the second step to remove the system automatically generated foreign key script, the system named nonstandard foreign key Delete, and then run the third step generated in the creation of the foreign key script, recreate these deleted foreign keys, also achieved on the nonstandard foreign key renaming.

V. System INSPECTION

Once the operation is complete, repeat step 1 and regenerate the Foreign key report form of an application system for a comparative check. If correct, the name is successful, otherwise the reason is found.

Previous page