NestJS/Sequelize：users_roles表唯一约束重复键错误求助

错误信息 #

2023-12-15 12:31:31.913 UTC [95] ERROR: duplicate key value violates unique constraint "users_roles_company_user_id_key"

业务逻辑 #

存在多家公司，企业用户归属对应公司，公司（及其用户）可创建自定义角色并分配给不同企业用户，且同一企业用户可拥有多个角色。

数据库模型代码（TypeScript + Sequelize） #

users_roles表 #

@Table({ tableName: 'users_roles' })
export class UserRole extends Model<UserRole> {
  @PrimaryKey
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  id: string;

  @ForeignKey(() => CompanyUser)
  @Column({ type: DataType.UUID, allowNull: false, field: 'company_user_id', unique: false })
  companyUserId: string;

  @ForeignKey(() => Company)
  @Column({ type: DataType.UUID, allowNull: false, field: 'company_id', unique: false })
  companyId: string;

  @ForeignKey(() => Role)
  @Column({ type: DataType.UUID, allowNull: false, field: 'role_id', unique: false })
  roleId: string;
}

companies表 #

@Table({ tableName: 'companies' })
export class Company extends Model<Company, CompanyCreationAttributes> {
  @PrimaryKey
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  id: string;

  @BelongsTo(() => User)
  user: User;

  @HasMany(() => CompanyUser)
  companyUsers: Array<CompanyUser>;

  @BelongsToMany(() => Role, () => UserRole)
  roles: Array<Role>;
}

roles表 #

@Table({ tableName: 'roles' })
export class Role extends Model<Role> {
  @PrimaryKey
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  id: string;

  @BelongsToMany(() => CompanyUser, () => UserRole)
  companyUsers: Array<CompanyUser>;

  @BelongsToMany(() => Company, () => UserRole)
  companies: Array<Company>;
}

company_users表 #

@Table({ tableName: 'company_users' })
export class CompanyUser extends Model<
  CompanyUser,
  CompanyUserCreationAttributes
> {
  @PrimaryKey
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  id: string;

  @ForeignKey(() => User)
  @Column({ type: DataType.UUID, allowNull: false, field: 'user_id' })
  userId: string;

  @ForeignKey(() => Company)
  @Column({ type: DataType.UUID, allowNull: false, field: 'company_id' })
  companyId: string;

  @BelongsTo(() => Company)
  company: Company;

  @BelongsToMany(() => Role, () => UserRole)
  roles: Array<Role>;
}

问题排查需求 #

当前users_roles表允许存在相同company_id的记录，但无法添加相同company_user_id的记录，报错提示违反唯一约束，怀疑是外键或约束配置错误，需要排查原因。

补充：users_roles表约束查询结果 #

查询SQL #

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = 'users_roles'::regclass::oid AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;

查询结果 #

问题原因与解决方法 #

原因 #

从约束查询结果可以明确看到：users_roles表存在**UNIQUE (company_user_id)**的唯一约束，这直接限制了同一个企业用户只能被分配一个角色，与业务逻辑冲突。

虽然在UserRole模型中给companyUserId字段设置了unique: false，但数据库中仍存在该字段的唯一约束，大概率是因为：

1. 模型代码修改后未执行数据库迁移同步结构
2. 历史版本模型曾给该字段设置unique: true，同步后修改为false时，数据库约束未自动删除

解决步骤 #

1. 删除错误的唯一约束

ALTER TABLE users_roles DROP CONSTRAINT users_roles_company_user_id_key;

2. 添加符合业务逻辑的联合唯一约束
   根据需求，应该限制"同一个用户不能重复拥有同一个角色"，因此添加(company_user_id, role_id)的联合唯一约束：

ALTER TABLE users_roles ADD CONSTRAINT users_roles_user_role_unique UNIQUE (company_user_id, role_id);

3. 同步模型配置（可选）
   若需要通过模型维护约束，可在@Table装饰器中添加联合唯一约束配置：

@Table({ 
  tableName: 'users_roles',
  uniqueKeys: {
    user_role_unique: {
      fields: ['companyUserId', 'roleId']
    }
  }
})

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