Cli: Migration with association reference causes error `ERROR: Cannot add foreign key constraint`

[shomanishikawa]

What are you doing?

Trying to create a very simple proof-of-concept to create table associations with migrations, but cannot add a foreign key when running through the cli - ./node_modules/.bin/sequelize db:migrate

Made sure to clear out my SequelizeMeta & SequelizeData tables and reset everything before running, but still get the error.

Migrations run fine when I remove the userId field definition in create-company.js

create-user.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      firstName: {
        type: Sequelize.STRING,
      },
      lastName: {
        type: Sequelize.STRING,
      },
      email: {
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  },
};

create-company.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('companies', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      name: {
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      },
      userId: {
        type: Sequelize.UUID,
        references: {
          model: 'users',
          key: 'id',
        },
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('companies');
  },
};

What do you expect to happen?

Create a foreign key field in my companies table

What is actually happening?

Get the following output:

Sequelize CLI [Node: 9.7.1, CLI: 4.0.0, ORM: 4.36.0]

Loaded configuration file "src/database/config.js".
Using environment "dev".
== 20180309004031-create-user: migrating =======
== 20180309004031-create-user: migrated (0.036s)

== 20180316173751-create-company: migrating =======

ERROR: Cannot add foreign key constraint

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] migrate:dev: `NODE_ENV=dev ./node_modules/.bin/sequelize db:migrate`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] migrate:dev script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/shoma/.npm/_logs/2018-03-16T18_22_27_398Z-debug.log

__Dialect:__ mysql innoDB utf8
__Dialect version:__ 5.7.21 Homebrew
__Database version:__ 5.7.21 Homebrew
__Sequelize version:__ Sequelize 4.35.2,
__Sequelize CLI version:__ Sequelize-cli 4.0.0
__Tested with latest release:__ No (If yes, specify that version)

[msolano00]

userId: {
        type: Sequelize.UUID,
        references: {
          model: 'users',
          key: 'id',
        },
      }

I am no expert but seems like the datatypes are different. One is an integer and the other one is a UUID.

[sushantdhiman]

@msolano00 is right

[amaanrajput940]

I am getting same error but I my application is running on EC2 instance when I execute this command sequelize db:migrate then I get this same error but when I migrate it on my local pc then it works fine. Anyone can help me please.