sequelize-typescript多对多关联中间表唯一键约束错误及复合主键需求

现有模型代码 #

User.model.ts #

import {
  AllowNull,
  BelongsToMany,
  Column,
  Model,
  PrimaryKey,
  Table
} from "sequelize-typescript";

import { Contact } from "./Contact.model";

@Table
export class User extends Model {
    @PrimaryKey
    @Column
    declare username: string;

    @AllowNull(false)
    @Column
    declare email: string;

    @AllowNull(false)
    @Column
    declare password: string;

    @AllowNull(false)
    @Column
    declare displayName: string;

    @Column
    declare dateOfBirth: Date;

    @Column
    declare avatar: string;

    @BelongsToMany(() => User, () => Contact, "username", "contactUsername")
    declare contacts: Array<User & {Contact: Contact}>;
}

Contact.model.ts #

import {
  Column,
  ForeignKey,
  Model,
  Table
} from "sequelize-typescript";

import { User } from "./User.model";

@Table
export class Contact extends Model {
  @ForeignKey(() => User)
  @Column
  declare username: string;

  @ForeignKey(() => User)
  @Column
  declare contactUsername: string
}

问题说明 #

当前模型下，可为用户添加首个联系人，但添加第二个联系人时会触发唯一键约束错误，提示username已存在。需求是将Contact模型的username和contactUsername设置为复合主键，确保同一用户可添加多个联系人，且联系人关系唯一。

解决方案 #

修改Contact.model.ts，为username和contactUsername字段添加@PrimaryKey装饰器，将二者设为复合主键：

import {
  Column,
  ForeignKey,
  Model,
  PrimaryKey,
  Table
} from "sequelize-typescript";

import { User } from "./User.model";

@Table
export class Contact extends Model {
  @PrimaryKey
  @ForeignKey(() => User)
  @Column
  declare username: string;

  @PrimaryKey
  @ForeignKey(() => User)
  @Column
  declare contactUsername: string
}

补充说明 #

• 复合主键会让数据库层面约束(username, contactUsername)组合唯一，既允许同一用户添加多个不同联系人，也避免重复添加同一联系人。
• 修改模型后，需执行数据库同步（如调用sequelize.sync()）或创建迁移脚本，更新数据库表结构，使新的主键约束生效。

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