4

When using the sequelize cli utility's migration feature, new foreign keys can be created by adding a new column. However, I'm running into an error when trying to create multiple foreign keys to the same model. The foreign key names produced by running sequelize db:migrate are not distinctive. The DB engine requires that all foreign keys are assigned a unique name, but SequelizeJS seem to initally name all foreign keys as:

// Excerpt from sequelize/lib/dialects/mysql/query-generator.js
// Line 195
{fkName: this.quoteIdentifier(attrName + '_foreign_idx')}

and this will of course produce equal keys if the index number idx isn't correctly incremented.

Somewhere in the Sequelize library, the idx part of _foreign_idx must be replaced by an actual numeric value when initializing models using db.sync(), but I haven't been able to identify where. I've also verified that sequelize indeed increment the index value when using db.sync() by inspecting the foreign keys in another database. In that database, the foreign keys were named as _ibfk_1, _ibfk_2, .., _ibfk_n

  • Have anyone encountered a problem where the foreign key generator produces identical names?
  • Does anyone have a suggestion as to how this can be avoided / alleviated when using Sequelize migrations?

I'm using MySQL as the database engine, but the foreign key name generator follows the same procedure for eg. postgre as well, as far as I was able to interpret the sequelize source code.

Example

The following migration will create three models and create a relations between them using the function addColumn function from sequelize cli.

The script models a scenario where both a coach and a team is sponsored by some sponsor. In this model, we would like to hold a reference to the sponsor id in both our coach and team model. Unfortunately, this will create two foreign keys with the name sponsorId_foreign_idx (one on the coach and one on the team model) and thus the foreign keys will not have unique names. This would however be avoided if idx was changed by some incremented value.

var Promise = require('bluebird');

module.exports = {
  up: function (queryInterface, Sequelize) {
    return Promise
      .join(
        queryInterface
          .createTable('sponsor', {
            id: {
              autoIncrement: true,
              primaryKey: true,
              type: Sequelize.INTEGER
            },
            name: {
              type: Sequelize.STRING
            },
          }),
        queryInterface
          .createTable('team', {
            id: {
              autoIncrement: true,
              primaryKey: true,
              type: Sequelize.INTEGER
            },
            name: {
              type: Sequelize.STRING
            },
          }),
        queryInterface
          .createTable('coach', {
            id: {
              autoIncrement: true,
              primaryKey: true,
              type: Sequelize.INTEGER
            },
            name: {
              type: Sequelize.STRING
            },
          })
      )
      .then(function(){
        return queryInterface
          .addColumn('team', 'sponsorId', {
            type: Sequelize.INTEGER,
            references: { model: 'sponsor', key: 'id' }
          })
          .then(function(){
            return queryInterface
              .addColumn('coach', 'sponsorId', {
                type: Sequelize.INTEGER,
                references: { model: 'sponsor', key: 'id' }
              });
          });
      });
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.dropAllTables(); 
  }
};

Complete error log dump

{ SequelizeBaseError: ER_DUP_KEY: Can't write; duplicate key in table '#sql-3b7_f1'
    at Query.formatError (/home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:175:14)
    at Query._callback (/home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:49:21)
    at Query.Sequence.end (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Sequence.js:86:24)
    at Query.ErrorPacket (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/home/usr/me/node_modules/mysql/lib/protocol/Parser.js:74:12)
    at Protocol.write (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/usr/me/node_modules/mysql/lib/Connection.js:109:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:551:20)
  name: 'SequelizeDatabaseError',
  message: 'ER_DUP_KEY: Can\'t write; duplicate key in table \'#sql-3b7_f1\'',
  parent: 
   { Error: ER_DUP_KEY: Can't write; duplicate key in table '#sql-3b7_f1'
       at Query.Sequence._packetToError (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
       at Query.ErrorPacket (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
       at Protocol._parsePacket (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:280:23)
       at Parser.write (/home/usr/me/node_modules/mysql/lib/protocol/Parser.js:74:12)
       at Protocol.write (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:39:16)
       at Socket.<anonymous> (/home/usr/me/node_modules/mysql/lib/Connection.js:109:28)
       at emitOne (events.js:96:13)
       at Socket.emit (events.js:188:7)
       at readableAddChunk (_stream_readable.js:176:18)
       at Socket.Readable.push (_stream_readable.js:134:10)
       at TCP.onread (net.js:551:20)
       --------------------
       at Protocol._enqueue (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:141:48)
       at Connection.query (/home/usr/me/node_modules/mysql/lib/Connection.js:214:25)
       at /home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
       at Promise._execute (/home/usr/me/node_modules/bluebird/js/release/debuggability.js:300:9)
       at Promise._resolveFromExecutor (/home/usr/me/node_modules/bluebird/js/release/promise.js:481:18)
       at new Promise (/home/usr/me/node_modules/bluebird/js/release/promise.js:77:14)
       at Query.run (/home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
       at /home/usr/me/node_modules/sequelize/lib/sequelize.js:849:20
       at /home/usr/me/node_modules/retry-as-promised/index.js:40:21
       at Promise._execute (/home/usr/me/node_modules/bluebird/js/release/debuggability.js:300:9)
       at Promise._resolveFromExecutor (/home/usr/me/node_modules/bluebird/js/release/promise.js:481:18)
       at new Promise (/home/usr/me/node_modules/bluebird/js/release/promise.js:77:14)
       at retryAsPromised (/home/usr/me/node_modules/retry-as-promised/index.js:30:10)
       at /home/usr/me/node_modules/sequelize/lib/sequelize.js:848:12
       at tryCatcher (/home/usr/me/node_modules/bluebird/js/release/util.js:16:23)
       at Promise._settlePromiseFromHandler (/home/usr/me/node_modules/bluebird/js/release/promise.js:510:31)
     code: 'ER_DUP_KEY',
     errno: 1022,
     sqlState: '23000',
     index: 0,
     sql: 'ALTER TABLE `coach` ADD `sponsorId` INTEGER, ADD CONSTRAINT `sponsorId_foreign_idx` FOREIGN KEY (`sponsorId`) REFERENCES `sponsor` (`id`);' },
  original: 
   { Error: ER_DUP_KEY: Can't write; duplicate key in table '#sql-3b7_f1'
       at Query.Sequence._packetToError (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
       at Query.ErrorPacket (/home/usr/me/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
       at Protocol._parsePacket (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:280:23)
       at Parser.write (/home/usr/me/node_modules/mysql/lib/protocol/Parser.js:74:12)
       at Protocol.write (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:39:16)
       at Socket.<anonymous> (/home/usr/me/node_modules/mysql/lib/Connection.js:109:28)
       at emitOne (events.js:96:13)
       at Socket.emit (events.js:188:7)
       at readableAddChunk (_stream_readable.js:176:18)
       at Socket.Readable.push (_stream_readable.js:134:10)
       at TCP.onread (net.js:551:20)
       --------------------
       at Protocol._enqueue (/home/usr/me/node_modules/mysql/lib/protocol/Protocol.js:141:48)
       at Connection.query (/home/usr/me/node_modules/mysql/lib/Connection.js:214:25)
       at /home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:40:21
       at Promise._execute (/home/usr/me/node_modules/bluebird/js/release/debuggability.js:300:9)
       at Promise._resolveFromExecutor (/home/usr/me/node_modules/bluebird/js/release/promise.js:481:18)
       at new Promise (/home/usr/me/node_modules/bluebird/js/release/promise.js:77:14)
       at Query.run (/home/usr/me/node_modules/sequelize/lib/dialects/mysql/query.js:39:17)
       at /home/usr/me/node_modules/sequelize/lib/sequelize.js:849:20
       at /home/usr/me/node_modules/retry-as-promised/index.js:40:21
       at Promise._execute (/home/usr/me/node_modules/bluebird/js/release/debuggability.js:300:9)
       at Promise._resolveFromExecutor (/home/usr/me/node_modules/bluebird/js/release/promise.js:481:18)
       at new Promise (/home/usr/me/node_modules/bluebird/js/release/promise.js:77:14)
       at retryAsPromised (/home/usr/me/node_modules/retry-as-promised/index.js:30:10)
       at /home/usr/me/node_modules/sequelize/lib/sequelize.js:848:12
       at tryCatcher (/home/usr/me/node_modules/bluebird/js/release/util.js:16:23)
       at Promise._settlePromiseFromHandler (/home/usr/me/node_modules/bluebird/js/release/promise.js:510:31)
     code: 'ER_DUP_KEY',
     errno: 1022,
     sqlState: '23000',
     index: 0,
     sql: 'ALTER TABLE `coach` ADD `sponsorId` INTEGER, ADD CONSTRAINT `sponsorId_foreign_idx` FOREIGN KEY (`sponsorId`) REFERENCES `sponsor` (`id`);' },
  sql: 'ALTER TABLE `coach` ADD `sponsorId` INTEGER, ADD CONSTRAINT `sponsorId_foreign_idx` FOREIGN KEY (`sponsorId`) REFERENCES `sponsor` (`id`);' }
jorgenkg
  • 4,140
  • 1
  • 34
  • 48
  • Did you ever find a suitable workaround? Looks like this [was fixed in their v4 development](https://github.com/sequelize/sequelize/pull/6008) but not in v3. – N Jones Mar 25 '17 at 20:20
  • I resorted to write tailored SQL queries using the interface exposed at `queryInterface.sequelize.query` ([it's existence is mentioned on the github page](https://github.com/sequelize/cli)) – jorgenkg Mar 26 '17 at 15:06

1 Answers1

2

After some digging I found that this is a bug introduced in v3.21. Someone kindly fixed it in the v4 alpha branch but not in v3 which is the current release branch. I submitted a backport of the patch for v3, but it will be a while until it makes it into a release.

In the meantime, I resorted to using manual queries like you recommended in your comment above. It's not too painful because it only affects adding / changing queries on existing tables but not foreign keys on new tables.

The examples are a bit vague in the docs, so for others that may stumble across this question here's what I have from an umzug migration.

up: function (queryInterface, Sequelize) {
    return queryInterface
        .addColumn('operators', 'organization_id', {
            type: Sequelize.INTEGER,
            allowNull: true,
            // This bit will cause the naming conflict
            // references: { model: 'organizations', key: 'id' },
            // onUpdate: 'CASCADE',
            // onDelete: 'RESTRICT'
        })
        .then(() => queryInterface
            .sequelize
            .query('ALTER TABLE `operators` ADD CONSTRAINT `operators_organization_id_foreign_idx` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;',
                { type: Sequelize.QueryTypes.RAW }));
},
N Jones
  • 1,004
  • 11
  • 18