mysql - Sequelize - 表中的 2 个可选外键

Question

有 3 个表attendance_record, student,teacher具有以下关系。

student(1) : attendance_record(Many)  > studentId(foreign key)
teacher(1) : attendance_record(Many)  > teacherId(foreign key)

当我创建一个出席记录时，有效负载将是这样的：

{
    "studentId": "xxxxx",
    "isAttended": true,
    "date": 1625020428
}

它显示以下错误

Cannot add or update a child row: a foreign key constraint fails (`my_db`.`attendance_record`, CONSTRAINT `attendance_record_ibfk_34` FOREIGN KEY (`teacherId`) REFERENCES `teacher` (`id`) ON DELETE CASCADE O)

我知道这是因为我不包含teacherId在有效负载中，但我希望它是可选的（有效负载中只有 studentId 或 teacherId）。
我该怎么做

出勤记录.model.js

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

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const attendanceRecord = sequelizeClient.define('attendance_record', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    studentId: {
      allowNull: false,
      type: DataTypes.UUID,
      references: { model: 'student', key: 'id' },
      defaultValue: Sequelize.UUIDV4,
      unique: 'studentId_foreign_idx'
    },
    teacherId: {
      allowNull: false,
      type: DataTypes.UUID,
      references: { model: 'teacher', key: 'id' },
      defaultValue: Sequelize.UUIDV4,
      unique: 'teacherId_foreign_idx'
    },
    isAttended: {
      type: DataTypes.BOOLEAN,
    },
    date:{
      type: DataTypes.DATE,
    },
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  // eslint-disable-next-line no-unused-vars
  attendanceRecord.associate = function (models) {
    attendanceRecord.belongsTo(models.student,{
      as: 'students',
      foreignKey: 'studentId'
    });
    attendanceRecord.belongsTo(models.teacher, {
      as: 'teachers',
      foreignKey: 'teacherId'
    });
  };

  return attendanceRecord;
};

学生模型.js

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const student = sequelizeClient.define('student', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      isEmail: true,
      unique: 'email'
    },
    firstName:{
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: '',
    },
    lastName:{
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: '',
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  // eslint-disable-next-line no-unused-vars
  student.associate = function (models) {
    student.hasMany(models.attendance_record,{
      foreignKey: 'studentId'
    });
  };

  return student;
};

教师模型.js

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

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

  // eslint-disable-next-line no-unused-vars
  teacher.associate = function (models) {
    teacher.hasMany(models.attendance_record,{
      foreignKey: 'teacherId'
    })
  };

  return teacher;
};

标签： mysqlsqlnode.jssequelize.jsrelational-database