Duplicated constraint name in migration for different tables

[xafilox]

Hi.

What you are doing?

I have an initial migration that creates a table "offer" and a table "purchase" (among others) and defines a foreign key "offerId" at "purchase" that points to "offer" with the queryInterface.addColumn method. This works well.
Then, I'm creating a new migration that creates a new table "timeRange" that also defines a foreign key "offerId" at "timeRange" that points to "offer".

What do you expect to happen?

Both migrations should work (maybe using a combination of both table names and column names) or at least it would be great to be able to manually specify the constraint name to use in the queryInterface.addColumn method.

What is actually happening?

The second migration is failing. This is because it is creating the same constraint name "offerId_foreign_idx" for "timeRange" table that created in the first migration for "purchase" table.

This is the SQL it is generating:

ALTER TABLE `timeRange` ADD `offerId` INTEGER(11), ADD CONSTRAINT `offerId_foreign_idx` FOREIGN KEY (`offerId`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

And the full error:

{ [SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table `TestDB`.`#sql-177b_101` (errno: 121 "Duplicate key on write or update")]
  name: 'SequelizeDatabaseError',
  message: 'ER_CANT_CREATE_TABLE: Can\'t create table `TestDB`.`#sql-177b_101` (errno: 121 "Duplicate key on write or update")',
  parent: 
   { [Error: ER_CANT_CREATE_TABLE: Can't create table `TestDB`.`#sql-177b_101` (errno: 121 "Duplicate key on write or update")]
     code: 'ER_CANT_CREATE_TABLE',
     errno: 1005,
     sqlState: 'HY000',
     index: 0,
     sql: 'ALTER TABLE `timeRange` ADD `offerId` INTEGER(11), ADD CONSTRAINT `offerId_foreign_idx` FOREIGN KEY (`offerId`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;' },
  original: 
   { [Error: ER_CANT_CREATE_TABLE: Can't create table `TestDB`.`#sql-177b_101` (errno: 121 "Duplicate key on write or update")]
     code: 'ER_CANT_CREATE_TABLE',
     errno: 1005,
     sqlState: 'HY000',
     index: 0,
     sql: 'ALTER TABLE `timeRange` ADD `offerId` INTEGER(11), ADD CONSTRAINT `offerId_foreign_idx` FOREIGN KEY (`offerId`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;' },
  sql: 'ALTER TABLE `timeRange` ADD `offerId` INTEGER(11), ADD CONSTRAINT `offerId_foreign_idx` FOREIGN KEY (`offerId`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;' }

Dialect: mysql (MariaDB)
Database version: 10.1.13
Sequelize version: 3.23.2

[sushantdhiman]

I think #6008 fixed it, It will be released soon

[xafilox]

Yeah, that seems to fix it. Thanks for your help!