PolarDB:ALTER TABLE ... ENABLE/DISABLE CONSTRAINT

How it works

Every constraint has two independent properties:

• New data enforcement — whether INSERT and UPDATE operations are checked against the constraint.

• Existing data validation — whether the database scans existing rows to verify they comply.

The commands in this document control these two properties independently. Understanding this separation is key to choosing the right operation.

ENABLE CONSTRAINT ... NOT VALID is equivalent to Oracle's ENABLE NOVALIDATE.

Supported constraint types: CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE.

Prerequisites

Before you begin, ensure that:

• Your cluster runs PolarDB for PostgreSQL (Compatible with Oracle) with revision version 2.0.11.9.11.0 or later.

• The polar_constraint extension is installed. See Install the extension.

To check your revision version, run:

SHOW polardb_version;

If needed, upgrade the minor engine version.

Dependency rules

Before enabling or disabling constraints, be aware of these ordering requirements:

• Disabling a referenced constraint: To disable a PRIMARY KEY or UNIQUE constraint, first disable all FOREIGN KEY constraints that reference it.

• Enabling a foreign key: To enable a FOREIGN KEY, the referenced PRIMARY KEY or UNIQUE constraint must already be enabled.

Compatibility notes

This is a PolarDB-specific feature. Native PostgreSQL does not support disabling PRIMARY KEY and UNIQUE constraints.

The syntax is based on PostgreSQL and differs from Oracle syntax. PolarDB uses ENABLE CONSTRAINT ... NOT VALID where Oracle uses ENABLE NOVALIDATE.

Install the extension

1. Check whether polar_constraint is already installed:

   SELECT extname, extversion FROM pg_extension WHERE extname = 'polar_constraint';

   If a row is returned, the extension is installed — skip to Syntax.

2. (Optional) For clusters with a revision version earlier than 2.0.11.9.25.0: in the PolarDB console, go to Configuration and Management > Parameter Configuration and add polar_constraint to the shared_preload_libraries cluster parameter.

   If other extensions are already listed, separate them with commas — for example: pg_stat_statements,polar_constraint. This action restarts your cluster and causes a brief service interruption. Perform it during off-peak hours or within a maintenance window.

3. Install the extension in your database:

   CREATE EXTENSION IF NOT EXISTS polar_constraint;

Syntax

-- Add an enabled constraint and validate all existing data (full table scan)
ALTER TABLE table_name ADD CONSTRAINT constraint_name;

-- Add an enabled constraint without validating existing data (fast)
ALTER TABLE table_name ADD CONSTRAINT constraint_name NOT VALID;

-- Add a disabled constraint and validate all existing data
ALTER TABLE table_name ADD CONSTRAINT constraint_name DISABLE;

-- Enable a constraint and validate all existing data (full table scan)
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

-- Enable a constraint without validating existing data (fast)
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name NOT VALID;

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Choose the right operation

Each command acquires a different lock level, which determines its impact on concurrent reads and writes.

Use case: Add a constraint to a large table

Running ADD CONSTRAINT directly on a large table triggers a full table scan while holding an ACCESS EXCLUSIVE lock. This blocks all reads and writes for the entire duration, which can severely impact a production service.

The two-phase method avoids this by separating the two concerns:

1. Phase 1 — Add the constraint without validating existing data. This modifies only metadata and holds the ACCESS EXCLUSIVE lock for a very short time (milliseconds), with almost no impact on concurrent operations.

2. Phase 2 — Validate existing data with a lighter lock. The ENABLE CONSTRAINT command uses a SHARE UPDATE EXCLUSIVE lock, which allows concurrent reads throughout the scan. Schedule this during off-peak hours.

Example: Add a CHECK constraint to a products table with hundreds of millions of rows, ensuring price > 0.

Phase 1: Add the constraint without validating existing data

-- Create a test table with data
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

-- Insert a non-compliant row
INSERT INTO products (name, price) VALUES ('Bag', -1);

-- Insert a compliant row
INSERT INTO products (name, price) VALUES ('Book', 10);

-- Add the constraint without scanning existing data — fast and non-disruptive
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

After this step, new writes are enforced immediately:

-- Blocked: new non-compliant insert
INSERT INTO products (name, price) VALUES ('Pen', -5);
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"
-- DETAIL: Failing row contains (3, Pen, -5).

-- Blocked: non-compliant update
UPDATE products SET price = -2 WHERE id = 1;
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"
-- DETAIL: Failing row contains (1, Bag, -2).

Phase 2: Find and fix non-compliant existing data, then enable the constraint

During off-peak hours, identify and fix any rows that violate the constraint:

-- Find non-compliant rows
SELECT * FROM products WHERE NOT (price > 0);

-- Fix non-compliant rows
UPDATE products SET price = 10 WHERE id = 1;

Then enable the constraint with full validation:

ALTER TABLE products ENABLE CONSTRAINT chk_price_positive;

Use case: Speed up bulk data imports

Enabled constraints validate every row during a COPY operation, which can be a significant performance bottleneck for large imports. Disable constraints before the import and re-enable them afterward.

Example: Import a large CSV file into target_table.

Step 1: Disable constraints before the import

-- Disable UNIQUE, FOREIGN KEY, and CHECK constraints
-- Note: Disabling a PRIMARY KEY is generally not recommended or possible.
ALTER TABLE target_table DISABLE CONSTRAINT uq_target;
ALTER TABLE target_table DISABLE CONSTRAINT fk_target;
ALTER TABLE target_table DISABLE CONSTRAINT chk_target;

Step 2: Run the import

COPY target_table FROM '/path/to/data.csv' WITH CSV;

Step 3: Re-enable constraints

Choose based on your confidence in the imported data's quality.

Option A: Enable without validation (faster)

Use this when you are confident the data is clean, or when you plan to validate it later.

-- ENABLE ... NOT VALID skips validation of the imported data
-- and enforces the constraint only on future writes.
ALTER TABLE target_table ENABLE CONSTRAINT uq_target NOT VALID;
ALTER TABLE target_table ENABLE CONSTRAINT fk_target NOT VALID;
ALTER TABLE target_table ENABLE CONSTRAINT chk_target NOT VALID;

Option B: Enable with validation (safer)

Use this to verify all data, including the imported data. This performs a full table scan and blocks writes — run it during off-peak hours.

-- Enable and validate all data
ALTER TABLE target_table ENABLE CONSTRAINT uq_target;
-- If validation fails: ERROR: could not enable unique constraint "uq_target"

ALTER TABLE target_table ENABLE CONSTRAINT fk_target;
-- If validation fails: ERROR: insert or update on table "target_table" violates foreign key constraint "fk_target"

ALTER TABLE target_table ENABLE CONSTRAINT chk_target;
-- If validation fails: ERROR: check constraint "chk_target" is violated by some row

FAQ

`ENABLE CONSTRAINT` fails with `ERROR: check constraint "..." is violated by some row`. What should I do?

Existing data violates the constraint. Find and fix the offending rows before retrying.

-- Find non-compliant rows (adjust the condition to match your constraint)
SELECT * FROM your_table WHERE NOT (price > 0);

-- Fix the rows
UPDATE your_table SET price = <new_value> WHERE <condition>;

-- Or delete them
DELETE FROM your_table WHERE NOT (price > 0);

-- Retry
ALTER TABLE your_table ENABLE CONSTRAINT your_constraint_name;

`ENABLE CONSTRAINT` fails with `ERROR: could not enable unique constraint "..."`. What should I do?

Duplicate values exist in the columns the constraint enforces. Find and remove the duplicates before retrying.

-- Find duplicate values (replace column_list with your actual column names)
SELECT column_list, COUNT(*)
FROM your_table
GROUP BY column_list
HAVING COUNT(*) > 1;

-- Remove duplicates, keeping the row with the lowest id
DELETE FROM your_table
WHERE id NOT IN (
    SELECT MIN(id) FROM your_table GROUP BY column_list
);

-- Retry
ALTER TABLE your_table ENABLE CONSTRAINT your_unique_name;

`ENABLE CONSTRAINT` fails with `ERROR: insert or update on table "..." violates foreign key constraint "..."`. What should I do?

Rows in the child table reference values that do not exist in the parent table. Find the orphaned rows and fix them before retrying.

-- Find orphaned rows in the child table
-- Replace child_table, parent_table, child_col, and parent_col with your actual names
SELECT c.*
FROM child_table c
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p WHERE p.parent_col = c.child_col
);

-- Fix orphaned rows: update to a valid parent value or delete them
DELETE FROM child_table
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p WHERE p.parent_col = child_table.child_col
);

-- Retry
ALTER TABLE child_table ENABLE CONSTRAINT your_fk_name;