PostgreSQL中如何向同结构表复制约束（不重建表）

没问题，这种场景我经常碰到，咱们完全不用重建表，直接通过ALTER TABLE语句给table_b添加上对应的外键约束就行。下面分两种方式来操作：

一、自动生成添加约束的SQL脚本（推荐，避免手动出错） #

PostgreSQL的系统表可以帮我们快速导出table_a的外键定义，直接改造成table_b的添加语句，省心又准确。

执行下面的查询，它会自动生成给table_b添加外键的SQL语句：

SELECT 
    'ALTER TABLE table_b ADD CONSTRAINT ' || conname || ' FOREIGN KEY (' || 
    array_to_string(array_agg(attname), ', ') || ') REFERENCES table_a(' || 
    array_to_string(array_agg(attname), ', ') || ');' AS add_fk_stmt
FROM 
    pg_constraint
JOIN 
    pg_attribute ON attrelid = conrelid AND attnum = ANY(conkey)
WHERE 
    conrelid = 'table_a'::regclass 
    AND contype = 'f'
    AND confrelid = 'table_a'::regclass; -- 筛选指向table_a主键的外键
GROUP BY 
    conname;

执行后会得到一条或多条ALTER TABLE语句，直接复制这些语句执行，就能给table_b加上和table_a完全一致的外键约束了。

二、手动添加外键约束（适合约束数量少的情况） #

如果你清楚具体的外键字段，直接写ALTER TABLE语句就行。比如假设table_a有一个外键fk_table_b_user_id指向自身主键user_id，那给table_b添加的语句就是：

ALTER TABLE table_b ADD CONSTRAINT fk_table_b_user_id FOREIGN KEY (user_id) REFERENCES table_a(user_id);

如果有多个外键，重复执行类似的语句即可。

关键注意事项 #

• 因为你已经确认两表数据一致，默认情况下PostgreSQL会自动校验数据，只要数据没问题就能顺利添加约束。
• 如果表数据量特别大，默认的校验会耗时很久，这时候可以加上NOT VALID选项跳过校验（毕竟你已经确认数据一致），之后再按需验证：

  -- 跳过校验快速添加约束
  ALTER TABLE table_b ADD CONSTRAINT fk_table_b_user_id FOREIGN KEY (user_id) REFERENCES table_a(user_id) NOT VALID;
  -- 可选：事后验证约束（确保数据真的符合要求）
  ALTER TABLE table_b VALIDATE CONSTRAINT fk_table_b_user_id;
  

这样操作完，table_b就拥有和table_a完全一致的外键约束啦，完全不用折腾重建表~

内容的提问来源于stack exchange，提问作者radicalbiscuit