PostgreSQL 12.5中ALTER TABLE移表后返回丢失键约束问题咨询

首先纠正一个关键语法错误：你写的ALTER TABLE old_shcema.my_table SET new_schema是错误写法，PostgreSQL中迁移表到指定schema的正确命令为：

ALTER TABLE old_schema.my_table SET SCHEMA new_schema;

你提到第一次迁移除触发器外一切正常，推测你实际执行时可能写对了命令，但移回原schema时主键、外键丢失的问题，可从以下方向排查：

可能的原因 #

• 约束对象冲突：当你把表移回old_schema时，如果该schema中已存在同名的主键、外键约束（比如第一次移表后残留的无效约束对象），PostgreSQL会因对象名冲突自动丢弃新的约束，导致表移回后无主键/外键。
• 外键引用路径异常：如果你的表外键引用了其他schema的表，第一次移表后外键约束的引用路径会更新为新schema下的表。当移回原schema时，若引用表的位置未同步调整，可能导致外键约束失效被自动清理。
• PG12.5的版本小问题：PG12系列存在少量与schema迁移相关的依赖对象同步bug，比如部分索引、约束的schema属性未随表迁移正确更新，但主键/外键完全丢失的情况并不普遍。建议检查数据库操作日志，确认是否有约束丢弃的报错信息。

解决与验证步骤 #

1. 严格使用正确的迁移命令，确保schema名、表名拼写无误（比如你写的old_shcema是笔误，应为old_schema）。
2. 移表前清理目标schema的残留对象：用\d old_schema查看是否有同名约束、索引，若存在则手动删除。
3. 移表后立即验证表结构：执行\d old_schema.my_table查看主键、外键是否存在，触发器是否正确关联。
4. 若问题重复出现，可先删除原schema中残留的触发器，再执行移表命令。

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