如何使用sequelize-typescript通过非主键字段建立一对多关联

错误信息翻译：
sqlMessage: "外键约束'users_ibfk_1'中的引用列'account_number'与被引用列'id'不兼容。"

问题出在Customer模型的@HasMany关联配置上——默认情况下Sequelize会自动使用模型主键id作为关联的目标字段，但你实际想基于非主键account_number建立关联，导致字段类型（string vs number）不匹配，触发外键约束错误。

修正方案 #

在Customer的@HasMany装饰器中，明确指定关联的外键字段（User表中的account_number）和被引用的源字段（Customer表中的account_number），同时确保两个表的account_number字段类型完全一致。

修正后的代码 #

User模型（保持原有配置即可）

// User模型代码
import {
  Table,
  Column,
  Model,
  ForeignKey,
  PrimaryKey,
  AutoIncrement,
  Unique,
  CreatedAt,
  UpdatedAt,
  BelongsTo,
} from "sequelize-typescript";
import sequelize from "../../db_config";
import Customer from "../customer/customer.model";
import UserDTO from "./dto/user.dto";

@Table({ tableName: "users", initialAutoIncrement: "1000" })
class User extends Model<UserDTO> {
  @Column
  user_id: string;

  @Unique
  @Column({ unique: true })
  email: string;

  @Column
  password: string;

  @Column
  user_name: string;

  @Unique
  @Column({ unique: true })
  phone_number: string;

  @ForeignKey(() => Customer)
  @Column
  account_number: string;

  @BelongsTo(() => Customer, { foreignKey: "account_number" })
  customer: Customer;

  @PrimaryKey
  @AutoIncrement
  @Unique
  @Column
  id: number;

  @Column({ allowNull: false, defaultValue: false })
  status: boolean;

  @CreatedAt
  created_at: Date;

  @UpdatedAt
  updated_at: Date;
}

export default User;

Customer模型（修改@HasMany配置）

// Customer模型代码
import {
  Table,
  Column,
  Model,
  ForeignKey,
  PrimaryKey,
  AutoIncrement,
  Unique,
  CreatedAt,
  UpdatedAt,
  HasMany,
} from "sequelize-typescript";
import User from "../user/user.model";
import CustomerDTO from "./dto/customer.dto";

@Table({ tableName: "customers", initialAutoIncrement: "1000" })
class Customer extends Model<CustomerDTO> {
  @Column
  customer_name: string;

  @Unique
  @Column({ unique: true })
  account_number: string;

  @Column
  city: string;

  @Column
  owner_name: string;

  // 关键修改：指定foreignKey和sourceKey
  @HasMany(() => User, { foreignKey: "account_number", sourceKey: "account_number" })
  user: User[];

  @PrimaryKey
  @AutoIncrement
  @Unique
  @Column
  id: number;

  @Column({ allowNull: false, defaultValue: false })
  status: boolean;

  @CreatedAt
  created_at: Date;

  @UpdatedAt
  updated_at: Date;
}

export default Customer;

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