sequelize: sync() with new alter mode fails on second run when using CONSTRAINTS

is there solution to this yet

Any updates on this?

No idea if this is safe, but needed a quick fix for testing:

function dropForeignKeyConstraints(database) {
    //this is a hack for dev only!
    //todo: check status of posted github issue, https://github.com/sequelize/sequelize/issues/7606
    const queryInterface = database.getQueryInterface();
    return queryInterface.showAllTables()
    .then(tableNames => {
        return Promise.all(tableNames.map(tableName => {
            return queryInterface.showConstraint(tableName)
            .then(constraints => {
                return Promise.all(constraints.map(constraint => {
                    if (constraint.constraintType === 'FOREIGN KEY') {
                        return queryInterface.removeConstraint(tableName, constraint.constraintName);
                    }
                }));
            });
        }));
    })
    .then(() => database);
}

Run before sync to drop all foreign keys. BRUTAL!

Workaround that I found: Move all the unique indexes definitions to second argument of define method

const Voucher = sequelize.define('Voucher', {
    code: {
      type: DataTypes.STRING
    },
    receiver_name: {
      type: DataTypes.STRING,
    },
  }, {
    indexes: [
      {
        unique: true,
        fields: ['code']
      }
    ]
  });

Don’t know why, but it works with sync({alter: true}) (still doesn’t work with unique:true property defined directly at column definition) checked with Sequelize version 4.37.8 and 4.42.0

This is a bit of a pain… is anyone working on a fix?

@The solution would be to check for existing constraint as a method exists to do it. As a quick fix but not a long term solution, I’ve written the following code in the query-interface file in the changeColumn method to check if a constraint already exists and it worked pretty well (starting line 556):

if (this.sequelize.options.dialect === 'sqlite') {
    // sqlite needs some special treatment as it cannot change a column
    return SQLiteQueryInterface.changeColumn.call(this, tableName, attributes, options);
} else {
    /************************************************************
    * HERE IS WHERE I CHECK FOR AN EXISTING CONSTRAINT
    ************************************************************/
    if (attributes[attributeName].references) {
        this.showConstraint(tableName, `${tableName}_${attributeName}_foreign_idx`).then((result) => {
            if (result.length === 0) {
                const query = this.QueryGenerator.attributesToSQL(attributes);
                const sql = this.QueryGenerator.changeColumnQuery(tableName, query);

                return this.sequelize.query(sql, options);
            }
        });
    } else {
        const query = this.QueryGenerator.attributesToSQL(attributes);
        const sql = this.QueryGenerator.changeColumnQuery(tableName, query);

        return this.sequelize.query(sql, options);
    }
}

It is not a long term solution as some code has been duplicated. I hope it helps

@sushantdhiman I don’t think it works for unique constraints 😕

https://github.com/sequelize/sequelize/pull/8795

Any news on this?

I had a similar problem. My solution was setting names to index. Probably the autogenerated name was too long. An autogenerated name is built from fields’s names. After setting the name all work fine

indexes: [
    {
        fields: ['barCode'],
        unique: true
    },
    {
        fields: [
    	    'productGroupId',
    	    'attributeValue_1',
    	    'attributeValue_2',
    	    'attributeValue_3'
        ],
        unique: true,
        name: 'products_product_group_id_attribute_values'
    }
];

+1

+1