javascriptsqlconstraintssequelize.js

Remove constraints in sequelize migration


I'm adding a unique constraint in a migration via the migrations.changeColumn function.

Adding the constraint works, but since you need to provide a “backwards migration“, removing it the same way does not. It doesn't give any errors when migrating backwards, but again applying the forward migration results in Possibly unhandled SequelizeDatabaseError: relation "myAttribute_unique_idx" already exists.

(The used database is postgres)

module.exports = {
  up: function (migration, DataTypes, done) {
    migration.changeColumn(
      'Users',
      'myAttribute',
      {
        type: DataTypes.STRING,
        unique: true                 // ADDING constraint works
      }
    ).done(done);
  },

  down: function (migration, DataTypes, done) {
    migration.changeColumn(
      'Users',
      'myAttribute',
      {
        type: DataTypes.STRING,
        unique: false                // REMOVING does not
      }
    ).done(done);
  }
};

I also tried using removeIndex

migration.removeIndex('Users', 'myAttribute_unique_idx').done(done);

But it gives the following error when reverting the migration:

Possibly unhandled SequelizeDatabaseError: cannot drop index "myAttribute_unique_idx" because constraint myAttribute_unique_idx on table "Users" requires it

Solution

  • As of 2017 with Sequelize 4.4.2, we can remove constraints with queryInterface API:

    queryInterface.removeConstraint(tableName, constraintName)

    Documentation is here.