ALTER TABLE ... ENABLE/DISABLE CONSTRAINT

功能简介

ENABLE/DISABLE CONSTRAINT功能通过修改约束的活动状态来优化特定场景下的数据库性能。该功能支持CHECK、FOREIGN KEY、PRIMARY KEY和UNIQUE四种类型的约束。

• DISABLE CONSTRAINT：将指定约束置为禁用状态，对于所有约束类型，数据库将不再对新的数据写入和更新操作（INSERT、UPDATE）进行校验。

• ENABLE CONSTRAINT：将指定约束置为启用状态。此操作包含两种校验模式：

  • ENABLE CONSTRAINT（默认模式）：启用约束时，不仅会对后续的新数据进行校验，还会扫描表中的全部存量数据，确保它们都符合约束规则。对于大表而言，此过程可能非常耗时并消耗大量I/O资源。

  • ENABLE CONSTRAINT ... NOT VALID：启用约束时，仅对后续的新数据进行校验，跳过对存量数据的校验。此模式的核心优势是速度快，因为它假定表中的现有数据已经满足约束。

前提条件

您的集群版本需为Oracle语法兼容 1.0，且修订版本为2.0.11.9.11.0及以上。

使用限制

• 插件依赖：此功能依赖polar_constraint插件，需按指导完成安装配置。

• 约束依赖限制：

  1. 被引用约束的禁用限制：

     如果一个主键（PRIMARY KEY）或唯一约束（UNIQUE）被其他表的外键约束（FOREIGN KEY）所引用，那么在禁用或删除引用它的外键约束之前，无法直接禁用此主键或唯一约束。系统会阻止此操作以维护数据完整性。

  2. 外键约束的启用限制：

     在启用一个外键约束（FOREIGN KEY）之前，系统会检查它所引用的、位于另一张表上的主键（PRIMARY KEY）或唯一约束（UNIQUE）的状态。只有当被引用的主键或唯一约束处于启用（ENABLED）状态时，该外键约束才能被成功启用。如果被引用的约束处于禁用状态，启用外键约束的操作将会失败并报错。

• 兼容性：

  • 此功能为PolarDB PostgreSQL版（兼容Oracle）扩展功能，原生PostgreSQL不支持禁用主键（PRIMARY KEY）和唯一约束（UNIQUE）。

  • 语法风格基于PostgreSQL，与Oracle的ENABLE VALIDATE及ENABLE NOVALIDATE等语法不同。

安装插件

首次使用该功能前，需要为集群安装polar_constraint插件。

1. 请检查您的PolarDB PostgreSQL版（兼容Oracle）集群是否已预装polar_constraint插件。如果返回了polar_constraint插件及其版本信息，则表示您的集群已成功安装插件。

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

2. （可选）若您的集群尚未安装，则需要按照以下步骤安装插件，对于不同内核版本，安装插件方式不同：

   1. （可选）对于修订版本在2.0.11.9.25.0以下的集群：您需先前往PolarDB控制台的配置与管理 > 参数配置页面，修改集群参数shared_preload_libraries，添加polar_constraint。

      说明

      • 若参数中已有其他插件，请使用逗号分隔。例如：pg_stat_statements,polar_constraint。

      • 修改当前参数会自动重启集群，这将导致业务服务中断。请在业务低峰期或维护窗口内进行操作。

   2. 为您指定的业务数据库中安装插件。

      -- 安装扩展
      CREATE EXTENSION IF NOT EXISTS polar_constraint;

语法参考

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

操作建议

不同的约束操作会施加不同级别的锁，对并发事务的影响也不同。理解这些差异是安全使用此功能的关键。

为大表安全添加约束

直接为大表添加约束（ADD CONSTRAINT）会立即触发全表数据校验，并持有ACCESS EXCLUSIVE锁，长时间阻塞所有读写操作，对生产环境业务造成严重影响。推荐采用以下两阶段添加法，将影响降至最低。

示例场景

为一张包含数亿行数据的products表添加一个CHECK约束，确保price字段大于0。

流程概述

1. 添加启用状态的约束，但不验证存量数据：快速添加约束定义，不校验存量数据，锁时间短。

2. 后台验证存量数据：在业务低峰期扫描并验证存量数据，确保表中字段满足约束。此过程允许并发读取，对业务影响最小。

操作步骤

1. 快速添加一个处于启用状态的约束定义。使用ADD CONSTRAINT ... NOT VALID语法，此操作仅修改元数据，不校验存量数据，操作时间短。

   -- 创建测试表和数据
   CREATE TABLE products (
       id SERIAL PRIMARY KEY,
       name TEXT,
       price NUMERIC
   );
   
   -- 插入一条不合规的数据
   INSERT INTO products (name, price) VALUES ('Bag', -1); 
   
   -- 插入一条合规的数据
   INSERT INTO products (name, price) VALUES ('Book', 10); 
   
   -- 添加开启状态的约束，不校验存量数据
   ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

2. （可选）验证约束对新写入和更新的数据是否生效。

   -- 新插入的非法数据会被阻止
   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).
   
   -- 更新的非法数据会被阻止
   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).

3. 验证历史数据是否满足约束。使用SELECT查询不合规的存量数据，并使用UPDATE修复存量数据。

   -- 查找不合规的历史数据
   SELECT * FROM products WHERE NOT (price > 0);
   
   -- 修复不合规的历史数据
   UPDATE products SET price = 10 WHERE id = 1;

优化大批量数据导入性能

在进行大批量数据导入（如COPY/INSERT）时，启用的约束（尤其是主键、唯一键和外键）会对每一行数据进行检查，严重拖慢导入速度。通过在导入前后禁用和启用约束，可以大幅提升性能。

示例场景

将大量数据从CSV文件导入到target_table。

操作步骤

1. 在数据导入前，临时禁用目标表的所有约束。

   -- 假设target_table已有唯一键、外键和检查约束
   ALTER TABLE target_table DISABLE CONSTRAINT uq_target;
   ALTER TABLE target_table DISABLE CONSTRAINT fk_target;
   ALTER TABLE target_table DISABLE CONSTRAINT chk_target;

   说明

   通常不建议也无法直接禁用主键，因为主键索引对数据组织至关重要。主要禁用的是唯一约束（UNIQUE）、外键（FOREIGN KEY）和检查（CHECK）约束。

2. 执行数据导入。由于约束被禁用，数据库不会进行检查，导入速度会显著加快。

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

3. 在数据导入后，重新启用约束并验证所有数据。

   1. 不验证导入数据。

      说明

      ENABLE CONSTRAINT ... NOT VALID存量数据的不一致性被忽略，只有新数据和修改的数据会被校验。建议在启用前最好先手动检查数据。

      -- 启用唯一约束
      ALTER TABLE target_table ENABLE CONSTRAINT uq_target NOT VALID;
      
      -- 启用外键约束
      ALTER TABLE target_table ENABLE CONSTRAINT fk_target NOT VALID;
      
      -- 启用CKECK约束
      ALTER TABLE target_table ENABLE CONSTRAINT chk_target NOT VALID;

   2. 验证导入数据。

      说明

      ENABLE CONSTRAINT会全表扫描消耗资源并阻塞写入。建议在业务低峰期执行，且启用前最好先手动检查数据。

      -- 启用唯一约束，这将触发对全表数据的校验
      ALTER TABLE target_table ENABLE CONSTRAINT uq_target;
      -- 如果校验失败，会报错：ERROR: could not enable unique constraint "uq_target"
      
      -- 启用外键约束，这将触发对全表数据的校验
      ALTER TABLE target_table ENABLE CONSTRAINT fk_target;
      -- 如果校验失败，会报错：ERROR: insert or update on table "target_table" violates foreign key constraint "fk_target"
      
      -- 启用CKECK约束，这将触发对全表数据的校验
      ALTER TABLE target_table ENABLE CONSTRAINT chk_target;
      -- 如果校验失败，会报错：ERROR: check constraint "chk_target" is violated by some row

常见问题