{"action":"opened","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/5826","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/5826/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/5826/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/5826/events","html_url":"https://github.com/sequelize/sequelize/issues/5826","id":152031336,"number":5826,"title":"Non-unique constraint names in MySQL","user":{"login":"erwinhaas","id":5842376,"avatar_url":"https://avatars.githubusercontent.com/u/5842376?v=3","gravatar_id":"","url":"https://api.github.com/users/erwinhaas","html_url":"https://github.com/erwinhaas","followers_url":"https://api.github.com/users/erwinhaas/followers","following_url":"https://api.github.com/users/erwinhaas/following{/other_user}","gists_url":"https://api.github.com/users/erwinhaas/gists{/gist_id}","starred_url":"https://api.github.com/users/erwinhaas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/erwinhaas/subscriptions","organizations_url":"https://api.github.com/users/erwinhaas/orgs","repos_url":"https://api.github.com/users/erwinhaas/repos","events_url":"https://api.github.com/users/erwinhaas/events{/privacy}","received_events_url":"https://api.github.com/users/erwinhaas/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-04-30T13:55:25Z","updated_at":"2016-04-30T13:55:25Z","closed_at":null,"body":"I think there is bug in the mysql dialect `addColumnQuery` query generator. \r\n\r\n## What I'm doing\r\n\r\nI'm adding a column to an existing table with a migration:\r\n\r\n```javascript\r\nreturn queryInterface.addColumn('carousel', 'category_id', {\r\n      type: Sequelize.INTEGER(11).UNSIGNED,\r\n      defaultValue: null,\r\n      allowNull: true,\r\n      references: {\r\n        model: 'categories',\r\n        key: 'id'\r\n      },\r\n      onUpdate: 'CASCADE',\r\n      onDelete: 'SET NULL'\r\n    });\r\n```\r\n\r\nThere is already another table that references the same `category` table which uses the field named `category_id`.\r\n\r\n### What I expect to happen\r\n\r\nI would expect that the column is added with an foreign key constraint in the database.\r\n\r\n## What is actually happening?\r\nWhat happens is that adding a column fails with the following error:\r\n\r\n```\r\nExecuting (default): ALTER TABLE `carousel` ADD `category_id` INTEGER(11) UNSIGNED DEFAULT NULL, ADD CONSTRAINT `category_id_foreign_idx` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;\r\n\r\nUnhandled rejection SequelizeDatabaseError: ER_DUP_KEY: Can't write; duplicate key in table '#sql-d45_999'\r\n```\r\n\r\n### Why this is happening\r\n\r\nI already did some digging in the code to see where the problem originates.\r\n\r\nThe naming convention that is used creates a foreign key is `foreign key + '_foreign_idx'` (see https://github.com/sequelize/sequelize/blob/master/lib/dialects/mysql/query-generator.js#L270-L277)\r\n\r\n```javascript\r\n      if (options && options.context === 'addColumn' && options.foreignKey) {\r\n        var attrName = options.foreignKey;\r\n\r\n        template += Utils._.template(', ADD CONSTRAINT <%= fkName %> FOREIGN KEY (<%= attrName %>)')({\r\n          fkName: this.quoteIdentifier(attrName + '_foreign_idx'),\r\n          attrName: this.quoteIdentifier(attrName)\r\n        });\r\n      }\r\n```\r\n\r\nAdding multiple constraints with the same `key` name doesn't work on MySQL, as the constraint symbol value should be unique within the database.\r\n\r\n> If the CONSTRAINT symbol clause is given, the symbol value, if used, must be unique in the database. A duplicate symbol will result in an error similar to: ERROR 1022 (2300): Can't write; duplicate key in table '#sql- 464_1'. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically.\r\n\r\nSource: https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html\r\n\r\nMy proposal is to update the naming convention to include the table name in name of the constraint, like  `table name + '_' + foreign key + '_fk'`, to make sure that the names are unique within a database. If you want, I can create a pull request implementing this.\r\n\r\n__Dialect:__ mysql\r\n__Database version:__ `5.7.11 Homebrew`\r\n__Sequelize version:__   sequelize 3.22\r\n"}}