CODEWITHSUNDEEP
node.jssequelize.js
Fırat Kaya
Fırat Kaya

Reputation: 97

How can I truncate table and cascade with bulkDelete in Sequelize?

I have a seed and I use bulkDelete with query interface. It's okay but I need to truncate table. I set truncate: true options and I get an error:

Cannot truncate a table referenced in a foreign key constraint (vexus_panel.visits, CONSTRAINT visits_ibfk_4 FOREIGN KEY (countryId) REFERENCES vexus_panel.countries (id))

And then I enable cascade like this: cascade: true but still not working

My code:

  down: async (queryInterface, Sequelize) => {
    /**
     * Add commands to revert seed here.
     *
     * Example:
     * await queryInterface.bulkDelete('People', null, {});
     */
    await queryInterface.bulkDelete("Countries", null, {
      truncate: true,
      cascade: true,
    });
  },

Error:

ERROR: Cannot truncate a table referenced in a foreign key constraint (vexus_panel.visits, CONSTRAINT visits_ibfk_4 FOREIGN KEY (countryId) REFERENCES vexus_panel.countries (id))

Docs: https://sequelize.org/master/class/lib/dialects/abstract/query-interface.js~QueryInterface.html#instance-method-bulkDelete

Update:

I found a solution.

  down: async (queryInterface, Sequelize) => {
    const { sequelize } = queryInterface;
    try {
      await sequelize.transaction(async (transaction) => {
        const options = { transaction };
        await sequelize.query("SET FOREIGN_KEY_CHECKS = 0", options);
        await sequelize.query("TRUNCATE TABLE Countries", options);
        await sequelize.query("SET FOREIGN_KEY_CHECKS = 1", options);
      });
    } catch (error) {
      console.log(error);
    }
  }

Upvotes: 3

Views: 4352

Answers (1)

nbk
nbk

Reputation: 49385

Disable the constraint, for the truncation

queryInterface.removeConstraint(vexus_panel.visits,visits_ibfk_4  )

And when you are done add it again

Upvotes: 2

Related Questions