MySql - Squalize - 无法添加外键约束

2022-01-19 00:00:00 sql database mysql sequelize.js

我正在尝试使用 Nodejs sequelize 创建数据库.被调用的命令是

I am trying to using Nodejs sequelize to create database. The commands being invoked are

CREATE TABLE IF NOT EXISTS `wheel` (`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `shopId` VARCHAR(255), PRIMARY KEY (`id`), 
FOREIGN KEY (`shopId`) REFERENCES `shop` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `segments` (`segmentID` VARCHAR(255) NOT NULL , `heading` VARCHAR(255) NOT NULL, `subHeading` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `wheelId` INTEGER, PRIMARY KEY (`segmentID`),
 FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `shop` (`id` VARCHAR(255) NOT NULL , `accessToken` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

但是我得到了这个错误

未处理的拒绝 SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN:无法添加外键约束

Unhandled rejection SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

当我尝试查看最后一个外键错误时，它说

When I try to see the last foreign key error , it says 

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-07-28 19:23:21 0x700000d95000 Error in foreign key constraint of table exitpopup/segments:
FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB:
Cannot resolve table name close to:
 (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB

奇怪的是，当我将 sql 语句放在 sql console 中时，它可以工作并且没有任何错误.我做错了什么?

Strangely, When I put the sql statements in sql console , it works and there isn't any error. What am I doing wrong ?

推荐答案

顺序需要改变.在创建 shop 表之前，您正在创建 wheel 表.但是，wheel 指的是原始查询集中不存在的商店表.当您更改订单时，车间表已经存在，因此不会发生错误.

The order needs to change. You are creatig the wheel table before you have created the shop table. However wheel refers to the shop table which does not exists in your original set of queries. When you change the order the shop table already exists so the error does not occur.

CREATE TABLE IF NOT EXISTS `shop` 
 (`id` VARCHAR(255) NOT NULL , `accessToken` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, 
 PRIMARY KEY (`id`)) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `wheel` 
(`id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `shopId` VARCHAR(255), 
 PRIMARY KEY (`id`), 
 FOREIGN KEY (`shopId`) REFERENCES `shop` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `segments` 
(`segmentID` VARCHAR(255) NOT NULL , `heading` VARCHAR(255) NOT NULL, `subHeading` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `wheelId` INTEGER, 
 PRIMARY KEY (`segmentID`),
 FOREIGN KEY (`wheelId`) REFERENCES `wheel` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

相关文章