sequelize: changeColumn does not remove NOT NULL contraint

As @nsfm pointed out, following is working for me as well Sequelize CLI [Node: 8.12.0, CLI: 4.1.1, ORM: 4.38.0]

  up: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(tableName, columnName, {
      type: Sequelize.INTEGER,
      allowNull: true
    })
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(tableName, columnName, {
      type: Sequelize.INTEGER,
      allowNull: false
    })
  }

The lack of the type property was the issue in my case:

up(queryInterface, Sequelize) {
  return queryInterface.changeColumn(tableName, columnName, {
    allowNull: true
  })
}

doesn’t work, but with

 up(queryInterface, Sequelize) {
   return queryInterface.changeColumn(tableName, columnName, {
+    type: Sequelize.STRING,
     allowNull: true
   })
 }

it works.

@sigod Have you tried excluding reference from changeColumn statement?

like so:

await queryInterface.changeColumn('tariffs', 'operator_id', {
	type: Sequelize.INTEGER,
	allowNull: true // note this
});

If this doesn’t work, I’ll look into fixing this behaviour.

Removing references from the changeColumn parameters worked - it changed allowNull to true without removing the foreign key. I assume that if I wanted to modify the constraint I’d use a combination of QueryInterface.removeConstraint and QueryInterface.addConstraint?

I’m using Sequelize 4.38.0.

It’s good to mention that using changeColumn and setting allowNull: false DOES NOT remove the foreign key to the table, but it does remove the allowNull constraint. If you want to remove the foreign key, you have to use queryInterface.removeConstraint.

sequelize’s version: 6.6.5 I think the issue was fixed because it worked for me: (both up and down functions)

  up: async (queryInterface, Sequelize) => {
    await queryInterface.changeColumn(
      'menu_item_review', 'restaurant_campaign_id',
      {
        type: Sequelize.INTEGER,
        allowNull: true, // Although not necessary - for the change to be clear
      },
    );
 

  down: async (queryInterface, Sequelize) => {
    await queryInterface.changeColumn(
      'menu_item_review', 'restaurant_campaign_id',
      {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
    );
  };

The ‘references’ is not needed it’s just creating more and more FKs as you can see here:

MY OWN ISSUE WAS : I didn’t change allowNull: false, allowNull: true, on my model.

I got the same issue.

I’m doing a migration that adds a new column, update the information, and set the column to not nullable.

// This does notwork
await queryInterface.changeColumn('Documents', 'documentCategoryId', {
  type: Sequelize.INTEGER,
  allowNull: false,
  references: {
    model: 'DocumentCategories',
    key: 'id'
  }
})

// At this point, the column is nullable, but the reference is added

But when splitting this, it seems to work.

// This work using 2 changes instead
await queryInterface.changeColumn('Documents', 'documentCategoryId', {
  type: Sequelize.INTEGER,
  allowNull: false
})
await queryInterface.changeColumn('Documents', 'documentCategoryId', {
  type: Sequelize.INTEGER,
  allowNull: false,
  references: {
    model: 'DocumentCategories',
    key: 'id'
  }
})
// At this point, the first one changed the column to being not nullable + adds the reference

If I remember correctly it then removes the foreign key.