Sequelize migration 'Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")'

Question

When adding Sequelize to my project, I've had quite a struggle getting started with the migration and adding Foreign Key contraints.

Documentation is... well, there's room for improvement!
According to my online search, it seems others are struggling too.

Wanting to associate two tables in a one-to-many relation:

Model 1: Userstatus (values: 'init', 'active', 'inactive' ...)
Model 2: User (username, email, pwdhash, ...)

First of all, create the migration files:

$ sequelize model:generate --name Userstatus --attributes name:string,value:tinyint,comment:string
$ sequelize model:generate --name User --attributes username:string,pwdhash:string,email:string,statusId:tinyint

ending up with these four files (including modifications):

1. migrations/xxxxxxxxxxxxx1-create-userstatus.js

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("Userstatus", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.TINYINT,
      },
      name: {
        type: Sequelize.STRING,
      },
      comment: {
        type: Sequelize.STRING,
      },
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable("Userstatus");
  },
};

2. migrations/xxxxxxxxxxxxx2-create-users.js

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("Users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.BIGINT,
      },
      username: {
        type: Sequelize.STRING,
      },
      pwdhash: {
        type: Sequelize.STRING,
      },
      email: {
        type: Sequelize.STRING,
      },
      statusId: {
        type: Sequelize.TINYINT,
        references: {
          model: "Userstatus",
          key: "id",
        },
        onUpdate: "CASCADE",
        onDelete: "SET DEFAULT",
      },
      statusUntil: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      deletedAt: {
        allowNull: true,
        type: Sequelize.DATE,
      },
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable("Users");
  },
};

3. models/userstatus.js (runtime, not relevant for the migration)
4. models/user.js (runtime, not relevant for the migration)

Executing the command:

$ sequelize db:migrate

Sequelize CLI [Node: 14.17.0, CLI: 6.2.0, ORM: 6.9.0]

Loaded configuration file "db\config\config.js".
Using environment "development".
== xxxxxxxxxxxxx1-create-userstatus: migrating =======
== xxxxxxxxxxxxx1-create-userstatus: migrated (0.101s)

== xxxxxxxxxxxxx2-create-users: migrating =======

ERROR: Can't create table myDb.Users (errno: 150 "Foreign key constraint is incorrectly formed")

Answer 1

Adding detailed logging to config shows that this is a MySQL error caused by onDelete (not accepting SET DEFAULT):
Can't create foreign key with ON DELETE SET DEFAULT

Somewhere down the line, I also had the exact same error response caused by a type mismatch (TINYINT vs. INTEGER):

1. migrations/xxxxxxxxxxxxx1-create-userstatus.js

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("Userstatus", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.TINYINT,
      },
  :
};

2. migrations/xxxxxxxxxxxxx2-create-users.js

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("Users", {
      :
      statusId: {
        type: Sequelize.INTEGER,
        references: {
          model: "Userstatus",
          key: "id",
        },
        onUpdate: "CASCADE",
      },
  :
};

Lesson learned: When migration errors occur, focus on DB and SQL first - make sure that the resulting SQL actually works when executed manually.

This is where I personally ended up spending more time than need be - I tried to 'fix' the Sequelize migration before I knew what was going on ... the real fix was turning on logging in order to acces the SQL directly.

UPDATE: Defining Foreign Key Constraints directly where table is created, spawns another issue to take care of - in order to undo such migration, the 'undo'-part of the migration needs to hold an extra queryInterface.removeConstraint(....) command, introducing the need to use Promise.all([...]) (example here)

Maybe it's just a matter of taste... I now choose to define Foreign Key Constraints in separate migration files like this:

migrations/xxxxxxxxxxxxxx-fk-userstatus-associate.js

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    return await queryInterface.addConstraint("Users", {
      type: "FOREIGN KEY",
      fields: ["statusId"], // field name of the foreign key
      name: "fk_users_statusId",
      references: {
        table: "Userstatus", // Target model
        field: "id", // key in Target model
      },
      onUpdate: "CASCADE",
      onDelete: "RESTRICT",
    });
  },

  down: async (queryInterface, Sequelize) => {
    return await queryInterface.removeConstraint(
      "Users", // Source model
      "fk_users_statusId" // key to remove
    );
  },
};

Wins:

• Smooth undo handling: $ sequelize db:migrate:undo
• I get to name my constraints as I please