如何在Sequelize多对多关系中允许重复记录（图书借阅场景）

问题描述 #

实现图书借阅功能时，逻辑如下：

• 借阅图书时创建记录：

const userBook = await UserBook.create({
  UserId,
  BookId,
  status: 0,
});

• 归还图书时更新状态：

await userBook.update({ status: 1 });
userBook.save();

初始功能正常，但同一用户再次借阅已归还的图书时，触发主键重复错误：

{
    code: 'ER_DUP_ENTRY',
    errno: 1062,
    sqlState: '23000',
    sqlMessage: "Duplicate entry '1-1' for key 'UserBooks.PRIMARY'",
    sql: 'INSERT INTO `UserBooks` (`status`,`UserId`,`BookId`,`createdAt`,`updatedAt`) VALUES (?,?,?,?,?);',
    parameters: [ 0, '1', '1', '2023-08-26 12:55:03', '2023-08-26 12:55:03' ]
  },
  fields: { PRIMARY: '1-1' },
  sql: 'INSERT INTO `UserBooks` (`status`,`UserId`,`BookId`,`createdAt`,`updatedAt`) VALUES (?,?,?,?,?);'
}

现有模型定义 #

User模型 #

module.exports = (sequelize) => {
  const { DataTypes, Model } = require('sequelize');
  class User extends Model {}

  User.init(
    {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    },
    {
      sequelize,
      timestamps: true,
      deletedAt: true,
      modelName: 'User',
    }
  );
  User.associations = () => {
    User.belongsToMany(sequelize.models.Book, {
      through: { model: sequelize.models.UserBook, unique: false },
      constraints: false,
    });
  };
  return User;
};

Book模型 #

module.exports = (sequelize) => {
  const { DataTypes, Model } = require('sequelize');
  class Book extends Model {}

  Book.init(
    {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    },
    {
      sequelize,
      timestamps: true,
      deletedAt: true,
      modelName: 'Book',
    }
  );
  Book.associations = () => {
    Book.belongsToMany(sequelize.models.User, {
      through: { model: sequelize.models.UserBook, unique: false },
      constraints: false,
    });
  };
  return Book;
};

UserBook模型 #

module.exports = (sequelize) => {
  const { DataTypes, Model } = require('sequelize');
  class UserBook extends Model {}

  UserBook.init(
    {
      status: { type: DataTypes.INTEGER, allowNull: false },
      UserId: { type: DataTypes.INTEGER, allowNull: false },
      BookId: { type: DataTypes.INTEGER, allowNull: false },
    },
    {
      sequelize,
      timestamps: true,
      deletedAt: true,
      modelName: 'UserBook',
    }
  );

  UserBook.associations = () => {
    UserBook.hasOne(sequelize.models.Book, {
      constraints: false,
    });
    UserBook.hasOne(sequelize.models.User, {
      constraints: false,
    });
  };
  return UserBook;
};

期望业务场景 #

Example Scenerio I want:  |  UserBook Table
userId 1 borrows bookId 1 -> id:1 status:0, userId:1, bookId:1
userId 1 returns bookId 1 -> id:1 status:1, userId:1, bookId:1
userId 1 borrows bookId 1 -> id:2 status:0, userId:1, bookId:1
userId 1 returns bookId 1 -> id:2 status:1, userId:1, bookId:1

尝试过的方案 #

曾尝试为UserBook的字段添加primaryKey:true，但未解决问题：

UserBook.init(
  {
    status: { type: DataTypes.INTEGER, primaryKey:true, allowNull: false },
    UserId: { type: DataTypes.INTEGER, primaryKey:true, allowNull: false },
    BookId: { type: DataTypes.INTEGER, primaryKey:true, allowNull: false },
  },
  // ...其他配置
);

解决方案 #

1. 修改UserBook模型，添加自增主键 #

核心问题是原模型默认将UserId和BookId作为复合主键，导致同一用户与图书只能存在一条记录。添加自增主键id后，每条借阅记录成为独立行：

module.exports = (sequelize) => {
  const { DataTypes, Model } = require('sequelize');
  class UserBook extends Model {}

  UserBook.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      status: { type: DataTypes.INTEGER, allowNull: false },
      UserId: { type: DataTypes.INTEGER, allowNull: false },
      BookId: { type: DataTypes.INTEGER, allowNull: false },
    },
    {
      sequelize,
      timestamps: true,
      deletedAt: true,
      modelName: 'UserBook',
    }
  );

  // 修正关联关系：UserBook属于User和Book，而非hasOne
  UserBook.associations = () => {
    UserBook.belongsTo(sequelize.models.Book, { foreignKey: 'BookId', constraints: false });
    UserBook.belongsTo(sequelize.models.User, { foreignKey: 'UserId', constraints: false });
  };
  return UserBook;
};

2. 同步数据库结构 #

如果数据库已存在UserBooks表，需要同步模型变更：

• 开发环境可使用自动同步（谨慎用于生产）：

await sequelize.sync({ alter: true });

• 生产环境建议使用迁移脚本：

npx sequelize-cli migration:generate --name add-primary-key-to-userbook

然后编写迁移文件修改表结构，添加自增id主键。

3. 保留原有业务逻辑 #

借阅和归还的代码无需修改，新增的自增主键会自动生成，同一用户多次借阅同一本书时会创建新的记录，满足业务需求。

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