PolarDB:ALTER TABLE ... ENABLE/DISABLE CONSTRAINT

Prerequisites

• The polar_constraint plug-in is added to the value of the shared_preload_libraries parameter.

• The kernel version is V1.1.11 or later. For more information about how to upgrade the kernel version, see Version Management.

• The polar_constraint plug-in is installed in the cluster for which the kernel version is manually upgraded to V1.1.11. You can execute the following statement to install the plug-in:

  CREATE EXTENSION IF NOT EXISTS polar_constraint;

Syntax

• ALTER TABLE table_name ADD CONSTRAINT constraint_name DISABLE;

  Adds a constraint to the table_name table. The constraint_name constraint is in the disabled state. This indicates that this constraint does not apply to the data in the table and the data that is to be inserted to the table.

  The syntax supports the following four types of constraints:

  • Primary key constraint

  • Unique constraint

  • Foreign key constraint

  • CHECK constraint

• ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

  Enables a constraint. By default, the system checks whether data in the table meets the constraint. If the data does not meet the constraint, the system reports an error. The error includes the table data that does not meet the constraint.

  Note

  Assume that the constraint is a foreign key constraint and associated with a primary key constraint or a unique constraint of another table. Before you enable the foreign key constraint, you must check whether the primary key constraint or the unique constraint associated with the foreign key constraint is enabled. If the primary key constraint or the unique constraint is enabled, you can enable the foreign key constraint. Otherwise, the system reports an error.

• ALTER TABLE table_name ENABLE CONSTRAINT constraint_name NOT VALID;

  Enables a constraint but does not check whether data in a table meets the constraint.

• ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

  Disables a constraint. The disabled constraint no longer works.

  Note

  A primary key constraint or a unique constraint may be referenced by other objects. For example, a primary key constraint is referenced by a foreign key constraint of another table. In this case, if the foreign key constraint is not disabled or deleted, the primary key constraint cannot be disabled.

Examples

Execute the following statement to create a table:

CREATE TABLE aa(a1 INT);
CREATE TABLE

Execute the following statement to create a constraint in which the values of the a1 column must be greater than 10. By default, the constraint is disabled.

ALTER TABLE aa ADD CONSTRAINT aa_a1_check CHECK(a1>10) DISABLE;
ALTER TABLE

Execute the following statement to insert a value that does not meet the constraint. The value can be inserted because the constraint is disabled.

INSERT INTO aa VALUES (10);
INSERT 0 1

Execute the following statement to enable the constraint and check whether the existing values meet the constraint. The system reports an error because a value in the table does not meet the constraint.

ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;
ERROR:  check constraint "aa_a1_check" is violated by some row

Execute the following statement to enable the constraint but not to check whether the existing values meet the constraint.

ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID;
ALTER TABLE

Execute the following statement to insert the values that do not meet the constraint. As a result, the system reports an error.

INSERT INTO aa VALUES (10);
ERROR:  new row for relation "aa" violates check constraint "aa_a1_check"
DETAIL:  Failing row contains (10).