Sequelize+PostgreSQL配置多列唯一约束避免重复插入问题排查

• 唯一约束字段配置和业务去重要求不匹配
  你要求的去重维度是siteId、email、userId、cadence，对应表字段为site_id、user_email、user_id、cadence，但迁移代码里配置的唯一约束字段是['site_id', 'user_email', 'portfolo_id', 'cadence']，既漏掉了必填的去重字段user_id，又额外加入了非去重维度的portfolo_id。
• PostgreSQL的NULL值规则导致约束失效
  PostgreSQL中NULL不等于任何值（包括另一个NULL），而你的portfolo_id字段允许为空，只要两条重复记录的这个字段值为NULL，哪怕其余字段完全一致，数据库也不会判定为违反唯一约束，自然可以正常插入。
• 迁移未实际生效
  如果你之前已经创建过report表，后续修改迁移文件重新执行时，node-pg-migrate不会自动覆盖已存在的表结构，你配置的新约束不会实际生效。可以直接连接数据库执行\d report命令，查看表上的实际约束列表，确认约束是否存在、关联字段是否正确。

• 修正唯一约束配置
  如果不需要将portfolo_id纳入去重逻辑，直接把唯一约束的字段替换为业务要求的四个字段即可：

  constraints: {
    unique: ['site_id', 'user_email', 'user_id', 'cadence'],
  }
  

  如果确实需要把portfolo_id加入去重维度，要么将该字段设置为notNull: true，要么创建带默认值处理的部分唯一索引，解决NULL值导致的约束失效问题：

  CREATE UNIQUE INDEX uk_report_unique_dimension ON report 
  (site_id, user_email, user_id, cadence, COALESCE(portfolo_id, ''));
  

• 给已存在的表补做结构变更
  不需要删表重建，单独新增一个迁移文件修正约束即可：

  exports.up = (pgm) => {
    // 删除之前建错的唯一约束，约束名可以通过\d report查询实际值
    pgm.dropConstraint('report', 'report_site_id_user_email_portfolo_id_cadence_key');
    // 新建正确的唯一约束
    pgm.addConstraint('report', 'uk_report_unique_schedule', {
      unique: ['site_id', 'user_email', 'user_id', 'cadence']
    })
  }
  
  exports.down = (pgm) => {
    pgm.dropConstraint('report', 'uk_report_unique_schedule');
    pgm.addConstraint('report', 'report_site_id_user_email_portfolo_id_cadence_key', {
      unique: ['site_id', 'user_email', 'portfolo_id', 'cadence']
    })
  }
  

• 代码层做兜底处理
  不要完全依赖数据库约束拦截重复数据：插入前可以先按四个去重字段查询是否已存在对应记录，提前返回业务层报错；同时插入逻辑里捕获PostgreSQL抛出的23505唯一冲突错误码，返回友好提示，避免接口直接返回500错误。

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