SequelizeDatabaseError: Cannot truncate a table referenced in a foreign key constraint

Question

I am using "sequelize": "^5.8.6" and I have defined two models, company and dividend.

My company.js model looks like the following:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Company = sequelize.define('Company', {
    company_name: DataTypes.STRING,
    company_link: DataTypes.STRING,
    ticker: DataTypes.STRING,
    description: DataTypes.STRING
  }, {});
  Company.associate = function(models) {
    Company.hasMany(models.Rating, { onDelete: 'cascade' });
    Company.hasMany(models.Dividend, { onDelete: 'cascade' });
  };
  return Company;
};

My dividend.js model looks like the following:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Dividend = sequelize.define('Dividend', {
    period: DataTypes.STRING,
    amount: DataTypes.FLOAT,
    payable_date: DataTypes.DATE,
  }, {});
  Dividend.associate = function(models) {
    Dividend.belongsTo(models.Company, { onDelete: 'cascade' });
  };
  return Dividend;
};

My truncate.js function looks like the following:

const models = require('../models');

const truncateTable = (modelName) =>
  models[modelName].destroy({
    where: {},
    force: true,
  });

module.exports = async function truncate(model) {
  if (model) {
    return truncateTable(model);
  }

  return Promise.all(
    Object.keys(models).map((key) => {
      if (['sequelize', 'Sequelize'].includes(key)) return null;
      return truncateTable(key);
    })
  );
}

When trying to truncate both models I get the following error:

{ SequelizeDatabaseError: Cannot truncate a table referenced in a foreign key constraint (`test-db`.`dividends`, CONSTRAINT
`dividends_ibfk_1` FOREIGN KEY (`CompanyId`) REFERENCES `test-db`.`companies` (`id`))
    at Query.formatError (c:\test\node_modules\sequelize\lib\dialects\mysql\query.js:239:16)
    at Query.handler [as onResult] (c:\test\node_modules\sequelize\lib\dialects\mysql\query.js:46:23)
    at Query.execute (c:\test\node_modules\mysql2\lib\commands\command.js:30:14)
    at Connection.handlePacket (c:\test\node_modules\mysql2\lib\connection.js:449:32)
    at PacketParser.Connection.packetParser.p [as onPacket] (c:\test\node_modules\mysql2\lib\connection.js:72:12)
    at PacketParser.executeStart (c:\test\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.Connection.stream.on.data (c:\test\node_modules\mysql2\lib\connection.js:79:25)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'SequelizeDatabaseError',
  parent:
   { Error: Cannot truncate a table referenced in a foreign key constraint (`test-db`.`dividends`, CONSTRAINT `dividends_ibfk_1` FOREIGN KEY (`CompanyId`) REFERENCES `test-db`.`companies` (`id`))
       at Packet.asError (c:\test\node_modules\mysql2\lib\packets\packet.js:684:17)
       at Query.execute (c:\test\node_modules\mysql2\lib\commands\command.js:28:26)
       at Connection.handlePacket (c:\test\node_modules\mysql2\lib\connection.js:449:32)
       at PacketParser.Connection.packetParser.p [as onPacket] (c:\test\node_modules\mysql2\lib\connection.js:72:12)
       at PacketParser.executeStart (c:\test\node_modules\mysql2\lib\packet_parser.js:75:16)
       at Socket.Connection.stream.on.data (c:\test\node_modules\mysql2\lib\connection.js:79:25)
       at Socket.emit (events.js:189:13)
       at addChunk (_stream_readable.js:284:12)
       at readableAddChunk (_stream_readable.js:265:11)
       at Socket.Readable.push (_stream_readable.js:220:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     code: 'ER_TRUNCATE_ILLEGAL_FK',
     errno: 1701,
     sqlState: '42000',
     sqlMessage:
      'Cannot truncate a table referenced in a foreign key constraint (`test-db`.`dividends`, CONSTRAINT `dividends_ibfk_1`
FOREIGN KEY (`CompanyId`) REFERENCES `test-db`.`companies` (`id`))',
     sql: 'TRUNCATE `Companies`' },
  original:
   { Error: Cannot truncate a table referenced in a foreign key constraint (`test-db`.`dividends`, CONSTRAINT `dividends_ibfk_1` FOREIGN KEY (`CompanyId`) REFERENCES `test-db`.`companies` (`id`))
       at Packet.asError (c:\test\node_modules\mysql2\lib\packets\packet.js:684:17)
       at Query.execute (c:\test\node_modules\mysql2\lib\commands\command.js:28:26)
       at Connection.handlePacket (c:\test\node_modules\mysql2\lib\connection.js:449:32)
       at PacketParser.Connection.packetParser.p [as onPacket] (c:\test\node_modules\mysql2\lib\connection.js:72:12)
       at PacketParser.executeStart (c:\test\node_modules\mysql2\lib\packet_parser.js:75:16)
       at Socket.Connection.stream.on.data (c:\test\node_modules\mysql2\lib\connection.js:79:25)
       at Socket.emit (events.js:189:13)
       at addChunk (_stream_readable.js:284:12)
       at readableAddChunk (_stream_readable.js:265:11)
       at Socket.Readable.push (_stream_readable.js:220:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     code: 'ER_TRUNCATE_ILLEGAL_FK',
     errno: 1701,
     sqlState: '42000',
     sqlMessage:
      'Cannot truncate a table referenced in a foreign key constraint (`test-db`.`dividends`, CONSTRAINT `dividends_ibfk_1`
FOREIGN KEY (`CompanyId`) REFERENCES `test-db`.`companies` (`id`))',
     sql: 'TRUNCATE `Companies`' },
  sql: 'TRUNCATE `Companies`' }

Any suggestions why I get this error?

I appreciate your replies!

Vivek Doshi · Accepted Answer · 2019-08-10T09:49:32.047

Here you go :

sequelize.query('SET FOREIGN_KEY_CHECKS = 0', null, { raw: true })

Temporarily disabling referential constraints (set FOREIGN_KEY_CHECKS to 0) is useful when you need to re-create the tables and load data in any parent-child order. with SET FOREIGN_KEY_CHECKS = 0

Code :

const models = require('../models');

const truncateTable = (modelName) =>
    models[modelName].destroy({
        where: {},
        force: true,
    });

module.exports = async function truncate(model) {
    if (model) {
        return truncateTable(model);
    }
    await sequelize.query('SET FOREIGN_KEY_CHECKS = 0', null, { raw: true }); //<---- Do not check referential constraints
    return Promise.all(
        Object.keys(models).map((key) => {
            if (['sequelize', 'Sequelize'].includes(key)) return null;
            return truncateTable(key);
        })
    );
}

// once you get the response from truncate, run this, and it will set foreign key checks again
sequelize.query('SET FOREIGN_KEY_CHECKS = 1', { raw: true }); // <-- Specify to check referential constraints

Flow :

First : With SET FOREIGN_KEY_CHECKS = 0 -- Do not check referential constraints

Second : So we can destroy all the tables and we'll not get error regarding foreign key constraint

Third : once all the tables got truncate we'll set SET FOREIGN_KEY_CHECKS = 1 back -- Specify to check referential
constraints

score 2 · Answer 2 · answered Aug 08 '19 at 02:40

2

I think this has nothing to do with Sequelize, but the database instead.

At How to truncate a foreign key constrained table?, are shown some options to deal with this restriction, that could be applies in a Sequelize transaction

answered Aug 08 '19 at 02:40

Coding Edgar

1,285
1
8
22

score 1 · Answer 3 · answered Jun 02 '20 at 08:35

I know its quiet late but hope it helps someone. I am using sequelize 3.35.1 and this resolved the issue for me:

    sequelize = new Sequelize("database_name", "Username", "password");

    sequelize.query('SET GLOBAL FOREIGN_KEY_CHECKS = 0;', { raw: true });
    // Do Some Action
    sequelize.query('SET GLOBAL FOREIGN_KEY_CHECKS = 1;', { raw: true });

Only SET GLOBAL FOREIGN_KEY_CHECKS = 0; was not doing the trick for me.

SequelizeDatabaseError: Cannot truncate a table referenced in a foreign key constraint

3 Answers3