mysql Sequelize -定义N：M关系时“定义多个主键”

我使用Sequelize和MySQL作为数据库。
有两个表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;"}

字符串
不知道为什么会发生这种情况，因为我在session_question_answer中只有一个主键id
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;
};

型

编辑1当我运行show keys from session_question_answer时，它显示2个主键QuestionAnswerId和sessionId。

怎么修？

我使用下面的方法来管理user/roles关联。您可以根据需要更改模型名称和属性。有关模型关联的详细信息，请查看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;
};

型

展开查看全部

mysql Sequelize -定义N：M关系时“定义多个主键”

1条答案

相关问题

热门标签

最新问答