Liquibase迁移重建county表时PostgreSQL主键约束重复报错排查

在Liquibase迁移流程中，需要重建county表，执行的SQL语句如下：

DROP TABLE IF EXISTS county;

CREATE TABLE IF NOT EXISTS county (
    id varchar NOT NULL,  
    "name" varchar NOT NULL,
    state_id varchar NOT NULL, 
    state_abbr varchar NOT NULL,
    state_name varchar NOT NULL,
    country_code varchar NOT NULL,  
    country_name varchar NOT NULL, 
    CONSTRAINT pk_county PRIMARY KEY (id)
);

删除表操作正常，但创建表时触发错误：ERROR: relation "pk_county" already exists。

根据PostgreSQL文档，DROP TABLE会移除目标表的所有索引、规则、触发器及约束，即使添加CASCADE参数也没解决问题。查询information_schema后发现，pk_county约束存在于另一个table_schema中。虽然理论上约束名无需全局唯一，但将约束名改为pk_county2后，创建表操作可正常执行。

一、约束未随表删除的原因 #

PostgreSQL中，主键约束本质是绑定了一个唯一索引，索引名称和约束名完全一致。虽然约束名在单个schema内是唯一的，但如果你的数据库search_path（搜索路径）包含多个schema，创建约束时若未指定schema，PostgreSQL会按搜索路径顺序查找同名对象——这里的问题是，另一个schema里已经存在名为pk_county的索引（可能是其他表遗留的主键索引，或是之前操作留下的无效对象），导致创建新约束时冲突。

而你删除的只是当前schema下的county表，只会清理当前schema内关联的约束和索引，不会影响其他schema中的同名对象。

二、正确删除并重建表的方法 #

1. 明确指定schema #

在SQL中直接指定表和约束所属的schema，彻底避免搜索路径带来的歧义：

DROP TABLE IF EXISTS your_schema.county;

CREATE TABLE IF NOT EXISTS your_schema.county (
    id varchar NOT NULL,  
    "name" varchar NOT NULL,
    state_id varchar NOT NULL, 
    state_abbr varchar NOT NULL,
    state_name varchar NOT NULL,
    country_code varchar NOT NULL,  
    country_name varchar NOT NULL, 
    CONSTRAINT pk_county PRIMARY KEY (id)
);

（注：把your_schema替换成你实际使用的schema名称）

2. 清理其他schema的同名对象 #

如果确认另一个schema里的pk_county是无用的遗留对象，直接删除即可：

DROP INDEX IF EXISTS other_schema.pk_county;

操作前务必确认该对象没有被其他表依赖，防止破坏现有业务数据。

3. 使用全局唯一的约束名 #

给约束名加上当前schema前缀，确保在整个数据库内唯一：

CREATE TABLE IF NOT EXISTS county (
    id varchar NOT NULL,  
    "name" varchar NOT NULL,
    state_id varchar NOT NULL, 
    state_abbr varchar NOT NULL,
    state_name varchar NOT NULL,
    country_code varchar NOT NULL,  
    country_name varchar NOT NULL, 
    CONSTRAINT your_schema_pk_county PRIMARY KEY (id)
);

4. 临时调整搜索路径 #

在迁移脚本开头设置会话级的搜索路径，让PostgreSQL优先使用目标schema：

SET search_path TO your_schema, public;
DROP TABLE IF EXISTS county;
CREATE TABLE IF NOT EXISTS county (
    id varchar NOT NULL,  
    "name" varchar NOT NULL,
    state_id varchar NOT NULL, 
    state_abbr varchar NOT NULL,
    state_name varchar NOT NULL,
    country_code varchar NOT NULL,  
    country_name varchar NOT NULL, 
    CONSTRAINT pk_county PRIMARY KEY (id)
);

这种方式能确保当前会话内的所有操作都默认指向目标schema，避免跨schema的对象冲突。

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