sequelize: Database migrations fail to add foreign key

I have an integer column called orderId.

Now I want to add a foreign key to it:

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.changeColumn("orderitems", "orderId", 
      {
        type: DataTypes.INTEGER,
        references: "orders",
        referenceKey: "id",
        onUpdate: "CASCADE",
        onDelete: "RESTRICT"
      }
    ).complete(done);
  }
}

but the migration fails with this:

{ '0':
   { [error: syntax error at or near "REFERENCES"]
     length: 93,
     name: 'error',
     severity: 'ERROR',
     code: '42601',
     detail: undefined,
     hint: undefined,
     position: '185',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     file: 'scan.l',
     line: '1002',
     routine: 'scanner_yyerror' } }
Completed in 9ms

events.js:74
        throw TypeError('Uncaught, unspecified "error" event.');
              ^
TypeError: Uncaught, unspecified "error" event.
    at TypeError (<anonymous>)
    at EventEmitter.emit (events.js:74:15)
    at null.<anonymous> (/Users/hoitz/develop/salad/node_modules/sequelize/lib/migrator.js:95:44)
    at EventEmitter.emit (events.js:98:17)
    at module.exports.finish (/Users/hoitz/develop/salad/node_modules/sequelize/lib/query-chainer.js:138:30)
    at exec (/Users/hoitz/develop/salad/node_modules/sequelize/lib/query-chainer.js:92:16)
    at onError (/Users/hoitz/develop/salad/node_modules/sequelize/lib/query-chainer.js:72:11)
    at EventEmitter.emit (events.js:95:17)
    at /Users/hoitz/develop/salad/node_modules/sequelize/lib/migration.js:65:19
    at null.<anonymous> (/Users/hoitz/develop/salad/node_modules/sequelize/lib/emitters/custom-event-emitter.js:52:38)

The error is caused by this query:

ALTER TABLE "orderitems" 
ALTER COLUMN "orderId" TYPE INTEGER REFERENCES "orders" ("id") 
ON DELETE RESTRICT 
ON UPDATE CASCADE;

which is invalid SQL. It should add a constraint to the column.

About this issue

Original URL
State: closed
Created 11 years ago
Comments: 67 (16 by maintainers)

Most upvoted comments

👍

ee99ee on Jul 24, 2014

So I am not sure if I am late or missing something but this seems to be working fine for me. Anyone see issues with this? cc/ @mickhansen

"use strict";

module.exports = {
  up: function(migration, DataTypes, done) {

    migration.addColumn('Locations', 'PostId',
        {
            type: DataTypes.INTEGER,
            references: "Posts",
            referencesKey: "id"
        });

    done();
  },

  down: function(migration, DataTypes, done) {
    migration.removeColumn('Locations', 'PostId');
    done();
  }
};

corbanb on May 1, 2015

Ok, I’ve come to a realization. Me and everyone else here are wrong.

Neither pg nor MySQL actually support REFERENCES in ALTER COLUMN. pg emits an error. MySQL may “say” it’s valid syntax, but InnoDB actually ignores it.

You can modify a column that has a reference, all you do is exclude the reference information from the column options. The ALTER COLUMN operation does not modify the foreign key.

The proper way to modify foreign key references is using ADD [CONSTRAINT [symbol]] FOREIGN KEY ( column_name [, ... ] ) REFERENCES ... and DROP FOREIGN KEY (mysql) / DROP CONSTRAINT [ IF EXISTS ] constraint_name (pg).

So the real result for this bug should be:

An extra set of migration functions should be added to handle references, something like addColumnReference / dropColumnReference.
changeColumn should throw an error if the options contains references. Something like “reference cannot be used in changeColumn, use addColumnReference to add new foreign key references to a column”.

dantman on Aug 28, 2015

👍

👀 Waiting for this issue

dgmike on May 26, 2014

👍

cusspvz on Sep 1, 2014

👍

jlouthan on Aug 29, 2014

👍

sampatbadhe on Aug 28, 2014

👍

TimmyCarbone on Aug 26, 2014

👍

eanglay on Jul 25, 2014

Not related to original issue but addColumn should now support foreign key constraints as of https://github.com/sequelize/sequelize/commit/0c1ec1cc0f38edb1d57d0a37d26f3664440c6f49

mickhansen on Sep 9, 2014

@Budry AFAIK no one is working on #5212, For now you can issue raw constraint change queries if you want to. Or you can help by working on #5212

sushantdhiman on Aug 30, 2016

@Budry with #5212 you will be able to do that by pairing removeConstraint and addConstraint

sushantdhiman on Aug 29, 2016

@sushantdhiman I’m not sure if it solves my problem too. I have this code:

module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.changeColumn('accounts', 'user_id', {
      type: Sequelize.INTEGER,
      onDelete: 'CASCADE',
      onUpdate: 'SET NULL'
    });
  },

  down: function(queryInterface, Sequelize) {
    return queryInterface.changeColumn('accounts', 'user_id', {
      type: Sequelize.INTEGER,
      onDelete: 'CASCADE',
      onUpdate: 'CASCADE'
    });
  }
};

Change is only on onUpdate from CASCADE to SET NULL, but after run sequelize db:migrate is created a new foreign key on user_id columns and old and wrong fk is still there

Budry on Aug 29, 2016

Is there anyone working on a pull request for this, or should I work on a PR myself?

legomind on Sep 12, 2015

@corbanb You are right, the original issue had a typo referenceKey instead of referencesKey. I’ll close this unless someone posts new code that still doesn’t work.

mickhansen on Jun 10, 2015

@theoptz No, this issues is not resolved yet. The current code only handles foreign keys in the context of table creation, not column updates.

janmeier on May 19, 2014