3

Here is my function:

up: queryInterface => {
  return queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0')
   .then(() => queryInterface.dropTable('my_table'));
},

This query works in the table:

SET FOREIGN_KEY_CHECKS = 0; 
DROP TABLE IF EXISTS `my_table`;

The error is

Cannot delete or update a parent row: a foreign key constraint fails

Nikronis
  • 141
  • 2
  • 11
  • 1
    I have no clue about sequelize, but as a DBA I can tell you, that the `set foreign_key_checks=0` statement has session scope. If possible, just execute the two statements in the same session. – fancyPants Feb 11 '19 at 14:45
  • @fancyPants It seems you can't execute two statements in the same session. I removed references from other tables and it worked. – Nikronis Feb 11 '19 at 14:52
  • Is something/someone else working in the database at the same time? If yes, then removing references can't be the solution. That's also why I downvoted Zendem's answer. – fancyPants Feb 11 '19 at 15:06
  • I didn't notice that there were two tables, which reference mine. I removed columns from them and it worked. SET FOREIGN_KEY_CHECKS = 0 is unsafe. – Nikronis Feb 12 '19 at 08:02
  • You rather have no referential integrity at all? And you remove columns? Okay then. Have a nice day. – fancyPants Feb 12 '19 at 08:04
  • In this migration, I needed to remove a table. So yes, references aren't needed anymore. Have a nice day. – Nikronis Feb 12 '19 at 08:35
  • Not working for me too :( – BartusZak Jul 31 '19 at 13:52
  • @BartusZak The way I see things is sequelize uses different threads for each query, therefore dropTable has FOREIGN_KEY_CHECKS = 1. Instead of setting FOREIGN_KEY_CHECKS, which is not safe, you need to remove all references to your table from the other tables and then drop it. – Nikronis Aug 01 '19 at 14:11

1 Answers1

0

If you want to remove a constraint/foreignKey in Sequelize migration, you need use this:

// an example of table projects and users
await queryInterface.removeConstraint('projects', 'projects_ibfk_1'); // tableName, constraintId
await queryInterface.dropTable('users');

Is better that execute 'SET FOREIGN_KEY_CHECKS = 0'.

If you don't know constraint ids (in my case is projects_ibfk_1), use:

const refs = await queryInterface.getForeignKeyReferencesForTable('projects');
console.log(refs);
celibacy0297
  • 26
  • 1
  • 2