The name change of the Oracle system table foreign keys

The ORACLE tutorial you are looking at is the name change of the Oracle system table foreign key.

The foreign keys of the tables in Oracle are the means to ensure the system's referential integrity, which refers to the master-slave constraint relationships that are satisfied by the columns distributed in two tables. The foreign key involves two tables, one of which is called the parent table and one of which is called the child table.

The parent table (parent table) is the basis of the reference constraint, that is, whether the constraint is valid or not can be judged by checking the valid data situation of this table. It is the condition of the reference constraint, affecting the constraint, and not subject to any influence of the constraint.

The subitem table (child table) is the object of reference constraint. When it changes, if there is new data input, it can judge whether the changes meet the constraint condition by comparing the valid data status in the parent item table. If not, it will reject the changes to be made.

In practical applications, developers usually define a large number of foreign keys in order to ensure the integrity of the system. However, if the naming of foreign keys is not standard, such as using the name automatically generated by the system, it will cause great trouble in the later system operation and maintenance. Such as after the system is running, loading large amounts of data or for 1 some data conversion operation, such as the foreign key mistakes, according to the system prompt foreign key error, impossible to locate directly to the foreign key error occurs between the two tables, and need to waste a lot of time to find errors caused by the foreign key of the parent table and item list, and then further to determine is the record in violation of the foreign key constraints. In general, we use this naming convention 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, child_table_name and Parent_table name are not exactly the same as the original table 1. Some abbreviations can be adopted, but the name 1 must reflect the constraint of the two tables. The names here, Child_table name refers to the child table, which is the constraint table, and Parent_table name refers to the parent table, which is the constrained table.

Let's discuss in more detail how to change non-standard foreign key names in the application system to standard foreign key names. Before we get into the discussion, it is important to note that the following actions will take a long time to complete, so plan to do them when the system is free. At the same time, the foreign key here is renamed, the method adopted is to delete and then rebuild, involving the deletion of the application system object operation, so before the operation, for the sake of security, the application system should be backed up.

[NextPage]

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

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


This script generates all the foreign key cases in the schema, including foreign key names, parent table names, child table names, and referenced column names. Run the script under SQL/PLUS, and before running the script, output SPOOL to a local file. Also note that this script can take longer to run if the foreign keys in the application are more numerous and complex.

2. Generate a foreign key script to delete the system's automatic name

Under SQL/PLUS, running the following script to generate all the foreign keys that delete the automatic system name (that is, the foreign key name is prefixed with SYS) and generate the foreign key report 1 will generate the script spool into a file.



Run the script and the system generates the delete foreign key script as shown below:
3. Generate the foreign key script to recreate the delete

Under SQL/PLUS, run the following script to generate the re-create the deleted foreign key script:


Run the script and the system generates the foreign key creation script as shown below:

ALTER TABLE DJ_ADD ADD CONSTRAINT foreign key name FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);

Replace the foreign key name above with the foreign key name named in the canonical naming rule described above


4. Foreign keys are renamed

After the above two scripts are generated, first run the script in step 2 to delete the foreign key automatically generated by the system, and delete the foreign key with irregular name in the system. Then run the script generated in step 3 to create the foreign key, and recreate the deleted foreign key.

5. System check

After the operation is completed, step 1 is re-executed to generate a foreign key report card of the application system for comparison and inspection. If correct, the name change is successful, otherwise find the reason.

On 1 page