mysql - Sequelize - "Multiple primary key defined"在定义 N :M relationship 时

Question

我使用 Sequelize 和 MySQL 作为数据库。
有 2 个表 session 和 question_answer 是 N 到 M 的关系，所以我创建了一个连接表 session_question_answer 来连接它们。
发生错误:

{"code":"ER_MULTIPLE_PRI_KEY","errno":1068,"sqlState":"42000","sqlMessage":"Multiple primary key defined",
"sql":"ALTER TABLE `session_question_answer` ADD `questionAnswerId` CHAR(36) BINARY PRIMARY KEY, 
ADD CONSTRAINT `session_question_answer_questionAnswerId_foreign_idx` FOREIGN KEY (`questionAnswerId`) REFERENCES `question_answer` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE;"},"sql":"ALTER TABLE `session_question_answer` ADD `questionAnswerId` CHAR(36) BINARY PRIMARY KEY, 
ADD CONSTRAINT `session_question_answer_questionAnswerId_foreign_idx` 
FOREIGN KEY (`questionAnswerId`) REFERENCES `question_answer` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE;"}

不知道为什么会发生这种情况，因为我在 id 中只有一个主键 session_question_answersession.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const session = sequelizeClient.define('session', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });
  session.associate = function (models) {
    session.belongsToMany(models.question_answer, { as: 'question_answers', through: 'session_question_answer', foreignKey: 'sessionId', onDelete: 'cascade' })
  };

  return session;
};

问答模型.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const questionAnswer = sequelizeClient.define('question_answer', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  questionAnswer.associate = function (models) {
    questionAnswer.belongsToMany(models.session, { as: 'sessions', through: 'session_question_answer', foreignKey: 'questionAnswerId', onDelete: 'cascade' })
  };

  return questionAnswer;
};

session-question-answer.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const sessionQuestionAnswer = sequelizeClient.define('session_question_ans', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    sessionId: {
      allowNull: false,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    questionAnswerId: {
      allowNull: false,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    },
    indexes: [
      { name: 'ix_session_q_ans', unique: true, fields: ['sessionId', 'questionAnswerId'] },
    ],
  });
  sessionQuestionAnswer.associate = function (models) {
  };
  return sessionQuestionAnswer;
};

编辑 1
当我运行 show keys from session_question_answer 时，它​​显示了 2 个主键 QuestionAnswerId 和 sessionId 。
如何解决？

Answer 1

我将以下用于用户管理的 user/roles 关联。您可以更改模型名称和属性以满足您的需要。有关模型关联的详细信息，请查看 this section of the sequelize documentation
用户.js

const { Model, DataTypes } = require('sequelize');

module.exports = (sequelize) => {

  class User extends Model {

    static associate(models) {
      models.User.belongsToMany(models.Role, { through: models.RoleUser, as: 'roles', foreignKey: 'userId' });
    }
  };

  User.init({
    // Put your properties here...
  }, {
    sequelize,
    modelName: 'User'
  });

  return User;

};

角色.js

const { Model, DataTypes } = require('sequelize');

module.exports = (sequelize) => {

  class Role extends Model {

    static associate(models) {
      models.Role.belongsToMany(models.User, { through: models.RoleUser, as: 'users', foreignKey: 'roleId' });
    }

  };

  Role.init({
    // Put your properties here///
  }, {
    sequelize,
    modelName: 'Role',
  });

  return Role;
  
};

角色用户.js

const { Model, DataTypes } = require('sequelize');

module.exports = (sequelize) => {

  class RoleUser extends Model {

    static associate(models) {}

  };

  RoleUser.init({
    roleId: DataTypes.INTEGER,
    userId: DataTypes.INTEGER,
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE
  }, {
    sequelize,
    modelName: 'RoleUser',
  });

  return RoleUser;

};