What to do if Oracle foreign key fails

In Oracle, you can use the "alter table" statement together with "enable constraint" to solve the problem of foreign key failure. The syntax is "alter table tableName enable constraint foreign key name;". This statement can make the failed foreign key key recovery.

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

What to do if the oracle foreign key fails

Recover the foreign key:

alter table tableName enable constraint 外键名称;

Extension:

Invalidate the foreign key:

alter table tableName disable constraint 外键名称;

Delete foreign keys:

alter table tableName drop constraint 外键名称;

ALTER TABLE ... ENABLE/DISABLE CONSTRAINT command is used to enable or disable constraints.

Prerequisites

Submit a work order and contact technical support to add the polar_constraint plug-in in the shared_preload_libraries parameter.

The kernel version is V1.1.11 and above. To upgrade the kernel version, please see version management.

Clusters that are manually upgraded to the V1.1.11 kernel version need to install the polar_constraint plug-in. The command is as follows:

CREATE EXTENSION IF NOT EXISTS polar_constraint;

Syntax

ALTER TABLE table_name ADD CONSTRAINT constraint_name DISABLE;

Add a constraint to the table table_name, and the constraint_name is disabled status, that is, it does not work on the data in the current table or the newly inserted data.

Currently this syntax supports the following four types of constraints:

• Primary key constraints

• Unique constraints

• Foreign key constraints

• CHECK constraints

The example is as follows:

Create a constraint, It is required that the value of column a1 must be greater than 10, and the constraint is initialized to a disabled state.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What to do if Oracle foreign key fails. For more information, please follow other related articles on the PHP Chinese website!