Oracle constraints management script

The ORACLE tutorial is: Oracle constraints management script.

As an Oracle database administrator, you may encounter such a database management requirement to stop or open the constraints and triggers for all tables under the current user (mode. This is one of the work that needs to be done when the database is merged and some code changes to the code table of the database system.

Let's look at this kind of actual database work business requirements, which is very practical in many applications at present. Banking data in a region is currently concentrated at the municipal level. With the continuous improvement of computer network technology and the requirements for service levels, the provincial and even national data sets are proposed. In addition to modifying the application, the most important task for the database administrator is to centralize the scattered databases in one or more databases. In this case, we need to sort out the previous code tables from different regions as a unified code table and the final centralized database merging.

For the Oracle database administrator, such data maintenance work should be performed before the code in the code table is updated or the data is merged, the first task is to stop all foreign keys or triggers under a user in the system. After processing the data, open these closed foreign keys and triggers. To meet these requirements, this article provides the following two SQL scripts: (1) The management script for a mode in the system or the user's foreign key or trigger; (2) the script is automatically searched for when a foreign key error occurs. The two scripts are described in detail below.

I. Constraints management script

This script can be used to manage the opening and closing of all foreign keys and triggers under the current Login User. Primary keys and unique constraints are not processed here, this script can process the primary key and unique constraints with slight modification. However, we recommend that you do not maintain the data after you stop the primary key or unique constraints.

The script runs as follows (SQL/PLUS ):


The as_alter parameter can only be "ENABLE" or "DISABLE", otherwise the program prompts an error. When the parameter is "ENABLE", it means to open all foreign keys and triggers in the current mode. On the contrary, "DISABLE" means to close all foreign keys and triggers in the current mode.

Attached Stored Procedure script:


Determine whether the input parameter is DISABLE or ENABLE. If yes, continue processing. Otherwise, exit the process and a prompt is displayed.
IF (UPPER (AS_ALTER) = 'disable' or upper (AS_ALTER) = 'enable') THEN
OPEN C_CON;

[NextPage]

Processing ENABLE or DISABLE of the current user's foreign key


Ii. Automatic script search for constraint errors

Generally, when the database administrator maintains the data, before importing new data, he must first disable all foreign keys and triggers. After the data is imported successfully, open the foreign key and trigger that are closed before import. The error "no parent keyword found" for the ORA-02298 is often encountered. This error is caused by a record in the database table that does not meet the foreign key constraints. Here, a script (P_CON_ERR) is provided to automatically find the cause of this type of error, that is, to find the field values that do not meet the foreign key constraints.

The stored procedure can be run independently and also called in the Stored Procedure P_ALTERCONS described earlier. In the Stored Procedure P_ALTERCONS, you can see that when the foreign key is enabled, if an error with an error number of ORA-02298 occurs, the stored procedure is called to automatically find the cause that caused the foreign key to fail.

The following is an example of running the stored procedure separately in the SQL/PLUS Environment:

The PL/SQL process is successfully completed.

Fk_sb_hjjl_relation1_sb_pzxh indicates the foreign key name with the error.

Attached Stored Procedure script:

Previous Page