PostgreSQL 创建外键时不校验历史数据

示例表

CREATE TABLE address
(
    id   SERIAL PRIMARY KEY,
    data TEXT,
    c_id INTEGER
);

CREATE TABLE customer
(
    id   SERIAL PRIMARY KEY,
    name TEXT
);

创建外键

如果历史数据有不匹配的情况，会报错，无法创建外键

ALTER TABLE address
    ADD CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES customer (id);

不校验历史数据

添加NOT VALID数据表示在创建外键时不校验历史数据。

ALTER TABLE address
    ADD CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES customer (id) 
    NOT VALID;

手动校验外键约束

ALTER TABLE address VALIDATE CONSTRAINT fk_c_id;

数据不匹配的情况下会输出

postgres=# ALTER TABLE address VALIDATE CONSTRAINT fk_c_id;
ERROR:  insert or update on table "address" violates foreign key constraint "fk_c_id"
DETAIL:  Key (c_id)=(2) is not present in table "customer".

删除外键

ALTER TABLE address
    DROP CONSTRAINT fk_c_id;

临时关闭外键检验

alter table address
    disable trigger all;

开启检验

alter table address
    enable trigger all;

官方文档

https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT