Sequelize - "Multiple primary key defined" when defining N:M relationship

Question

I am using Sequelize and MySQL for database.

There are 2 tables session and question_answer which are N-to-M relationship, so I created a junction table session_question_answer to connect them.

error occurs:

{"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;"}

Not sure why this happens because I have only one primary key id in session_question_answer

session.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;
};

question-answer.model.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;
};

Edit 1
When I run show keys from session_question_answer, it shows 2 primary key QuestionAnswerId and sessionId.

How to fix it?

Answer 1

I use the following for a user / roles association for user management. You can change the model names and properties to suit your needs. For details about model associations, check out this section of the sequelize documentation

user.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;

};

role.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;
  
};

roleuser.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;

};