Sequelize - Cannot add foreign key constraint for 1:N relationship

Viewed 196

I am defining the database schema and relationship using Sequelize.

The relationship:

users <----(1:N)----> notifications <----(N:1)----> admins

The below error results after I save the files:

error: Unhandled Rejection at: Promise 

{"name":"SequelizeDatabaseError","parent":

{"code":"ER_CANNOT_ADD_FOREIGN","errno":1215,"sqlState":"HY000",

"sqlMessage":"Cannot add foreign key constraint",

"sql":"ALTER TABLE `notifications` ADD `adminId` CHAR(36) BINARY, ADD CONSTRAINT `notifications_adminId_foreign_idx` FOREIGN KEY (`adminId`) 
REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;"}

Then I inspected the database schema using mysqlworkbench, I find that userId is successfully added in notifications table, but no adminId is added in the table.

users.model.js

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

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

  // eslint-disable-next-line no-unused-vars
  users.associate = function (models) {
    users.hasMany(models.notifications);
  };
  return users;
};

admin.model.js

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

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

  // eslint-disable-next-line no-unused-vars
  admins.associate = function (models) {
    admins.hasMany(models.notifications);
  };
  return admins;
};

notifications.model.js

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

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

  // eslint-disable-next-line no-unused-vars
  notifications.associate = function (models) {
    notifications.belongsTo(models.users);
    notifications.belongsTo(models.admins);
  };

  return notifications;
};

0 Answers
Related