Oracle constraint management scripts

The ORACLE tutorial you are looking at is :Oracle constraint management scripts.

As an Oracle database administrator, you will encounter such a database administration requirement to stop or open constraints and triggers for all tables under the current user (schema). This is one of the things that needs to be done when merging databases and modifying some code in the code table of the database system.

Let's look at one of the actual database work business requirements that are very practical in many applications today. At present, municipal data centralization is adopted for the data of a regional bank. With the continuous improvement of computer network technology and the requirement of service level, provincial and even national data centralization is proposed. In addition to the need to modify the application, the most important task for the database administrator is to centralize the distributed database into one or several centralized databases. At this point, we need to sort out the code table for a unified 1 code table and the database of the last centralized integration.

For the database administrator of Oracle, before updating the code in the code table or merging the data, the first thing to do is to stop all the foreign keys or triggers under a user in the system. After processing the data, open the closed foreign keys and triggers. To meet such requirements, this paper presents the following two SQL scripts :(1) the management script of a mode or user's foreign key or trigger in the system; (2) automatic script search for foreign key errors. These two scripts are described in detail below.

1. Constrain the management script

The script can be used to manage the current login user under all foreign keys and trigger to open and close, there is no primary key and only 1 constraints, modify the script a little bit can handle primary and only 1 constraints, but preferably not here in a casual stop after the primary or only 1 constraints, data maintenance.

The script is run as follows (SQL/PLUS) :


Where, the parameter as_alter can only be "ENABLE" or "DISABLE", otherwise the program will prompt an error. When the parameter is "ENABLE", it means that all foreign keys and triggers in the current mode will be turned on, whereas "DISABLE" means that all foreign keys and triggers in the current mode will be turned off.

Attached stored procedure script:


Determine whether the input parameter is DISABLE or ENABLE. If so, continue processing. Otherwise, exit the process and give a prompt
IF (UPPER(AS_ALTER) = 'DISABLE' OR UPPER(AS_ALTER) = 'ENABLE') THEN
OPEN C_CON;

[NextPage]

The current user handles ENABLE or DISABLE for foreign keys


2. Constraint error automatic lookup script

In general, the database administrator in the data maintenance, such as the import of new data, first to close all the foreign keys and triggers, data successfully imported, and then open the foreign keys and triggers closed before the import. The "parent keyword not found" error with the error number ORA-02298 is often encountered. The reason for this error is that there is a record in the database table that does not satisfy the foreign key constraint. Here, an additional script (P_CON_ERR) is given to automatically find the cause of this type of error, that is, to find the field values that do not satisfy the foreign key constraint.

This stored procedure can be run separately, and is also called in the previously described stored procedure P_ALTERCONS. In the stored procedure P_ALTERCONS, you can see that when the foreign key is opened, if there is an error of ORA-02298, the stored procedure is called, and the reason why the foreign key cannot be started is automatically found.

Here's an example of running the stored procedure separately, in an SQL/PLUS environment:

The PL/SQL process was successfully completed.

Where, FK_SB_HJJL_RELATION_RELATION_PZXH is the foreign key name with the fault.

Attached stored procedure script:

On 1 page