allowNull false is not set on changeColumn

[virtser]

I have two migrations scripts.

The first one adds a new column to table and with allowNull: true:

    return queryInterface.addColumn(
      'likes',
      'apartment_id',
      {
        type: Sequelize.INTEGER,
        allowNull: true
      }
    );

The following one sets allowNull: false and adds a reference to another table as FK:

    return queryInterface.changeColumn(
      'likes',
      'apartment_id',
      {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'apartments',
          key: 'id'
        }
      }
    );

Eventually after all migrations were executed, the filed in MySQL DB still allows NULL:

`apartment_id` int(11) DEFAULT NULL,

[Americas]

Looking at the code, there doesn't seem to be a reason why that would fail. Can you show the output SQL that was generated?

[ArslanAnjum]

same issue. can you update if solution is found

[hamxabaig]

It doesn't work for me too, How do i get SQL output for the migration?

[suavedev]

For some reason, allowNull: false on a field with reference doesn't work on my end and throws SequelizeDatabaseError: Cannot add foreign key constrain . Info: Node 8.10.0, sequelize 4.38.0 with sequelize-cli 4.0.0.

[nlevchuk]

Hi everyone. Was the issue fixed?

[LeoDoldan7]

Having the same problem here but adding unique: true instead of allowNull: false. Any solution?

[ghost]

same issue with node 10.11.0 and sequelize 4.42.0, both with addColumn() and changeColumn()

[LeoDoldan7]

I think you have to use queryInterface.removeConstraint in order to make it work.

[karltaylor]

You can't have any rows with a null value in if you're changing that column to allowNull

[kasp4770]

You can't have any rows with a null value in if you're changing that column to allowNull

If there is no records/rows in the table, you should be able to create a column with allowNull: false... yet it doesn't seem to be possible with createColumn or changeColumn.

But there is a solution to this one. You must add the foreign key constraint when you create the table. That means that you must first create the referenced table.

If you must change a column in an existing table with existing records, then I don't know.

[adrigardi90]

Not exactly the same issue, but I also faced a problem trying to update a column allowing null values (from not null constraint)

In my case I had to define a default value (defaultValue), otherwise the migration file was executed successfully but the null constraint was still there.

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(table, column, {
      type: Sequelize.BIGINT,
      allowNull: true,
      defaultValue: null
    })
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(table, column, {
      type: Sequelize.BIGINT,
      allowNull: false,
    })
  }
}

[jeff-le-goldenowl]

Dear, it's not work for me

      isNight: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: false,
      },

[ogtfaber]

Not exactly the same issue, but I also faced a problem trying to update a column allowing null values (from not null constraint)

In my case I had to define a default value (defaultValue), otherwise the migration file was executed successfully but the null constraint was still there.

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(table, column, {
      type: Sequelize.BIGINT,
      allowNull: true,
      defaultValue: null
    })
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(table, column, {
      type: Sequelize.BIGINT,
      allowNull: false,
    })
  }
}

Yes also had to define the type to make it work.

[flipflopsimsommer]

Same with references :disappointed: in sequelize 6.28 with Node v18.

01.migrate

// Setup
await createTable('tn',{col: {reference:{...},allowNull:true});

02.migrate

// does not set NULL
await changeColumn('tn','col', {allowNull:true,type: DataTypes.BIGINT.UNSIGNED,references:{...}})
// sets NULL
await changeColumn('tn','col', {allowNull:true,type: DataTypes.BIGINT.UNSIGNED})