javascript - SequelizeDatabaseError:无法截断外键约束中引用的表
问题描述
我正在使用"sequelize": "^5.8.6"
并且我定义了两个模型,company和股息。
我的company.js
模型如下所示:
'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;
};
我的dividend.js
模型如下所示:
'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;
};
我的truncate.js
函数如下所示:
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);
})
);
}
尝试截断两个模型时,出现以下错误:
{ 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`' }
有什么建议为什么我会收到此错误?
感谢您的回复!
解决方案
干得好 :
sequelize.query('SET FOREIGN_KEY_CHECKS = 0', null, { raw: true })
当您需要重新创建表并以任何父子顺序加载数据时,临时禁用引用约束(将 FOREIGN_KEY_CHECKS 设置为 0)很有用。和SET FOREIGN_KEY_CHECKS = 0
代码 :
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
流动 :
- 第一: 使用 SET FOREIGN_KEY_CHECKS = 0 -- 不检查引用约束
- 第二: 所以我们可以销毁所有的表并且我们不会得到关于外键约束的错误
- 第三: 一旦所有表都被截断,我们将设置 SET FOREIGN_KEY_CHECKS = 1 back -- 指定检查引用
约束
推荐阅读
- javascript - 如何根据列值对每一行显示复选框 - JQuery DataTable
- javascript - 如何替换 HTML 字符串中的 value 属性?
- sql-server - 在 SSIS 中生成唯一的随机数
- python - 如何根据注释属性对networkx python图中的节点进行分组?
- mysql - mysqldump 后删除目录时出现错误代码
- r - R 为 sfc_POLYGON 中的多边形分配 ID 以进行绘图
- c - CLion 中的多个 C 源文件
- python - Heroku Flask 应用程序:将 wsgi.py 放在哪里?
- php - 为什么 PHP 不显示所有语法错误?
- arrays - 无法在 API React JS 返回的数组中获取数组