title: Add Constraints Safely in Migrationsimpact: HIGHimpactDescription: Prevents migration failures and enables idempotent schema changestags: constraints, migrations, schema, alter-table在迁移中安全地添加约束PostgreSQL 不支持ADD CONSTRAINT IF NOT EXISTS。使用该语法的迁移会失败。错误做法导致语法错误-- 错误not 附近存在语法错误SQLSTATE 42601altertablepublic.profilesaddconstraintifnotexistsprofiles_birthchart_id_uniqueunique(birthchart_id);正确做法幂等的约束创建-- 使用 DO 块在添加前检查do$$beginifnotexists(select1frompg_constraintwhereconnameprofiles_birthchart_id_uniqueandconrelidpublic.profiles::regclass)thenaltertablepublic.profilesaddconstraintprofiles_birthchart_id_uniqueunique(birthchart_id);endif;end$$;适用于所有约束类型-- 检查约束do$$beginifnotexists(select1frompg_constraintwhereconnamecheck_age_positive)thenaltertableusersaddconstraintcheck_age_positivecheck(age0);endif;end$$;-- 外键do$$beginifnotexists(select1frompg_constraintwhereconnameprofiles_birthchart_id_fkey)thenaltertableprofilesaddconstraintprofiles_birthchart_id_fkeyforeignkey(birthchart_id)referencesbirthcharts(id);endif;end$$;检查约束是否存在-- 查询约束是否存在selectconname,contype,pg_get_constraintdef(oid)frompg_constraintwhereconrelidpublic.profiles::regclass;-- contype 取值-- p 主键PRIMARY KEY-- f 外键FOREIGN KEY-- u 唯一UNIQUE-- c 检查CHECK参考约束