MySql-Sequalize-无法添加外键约束 [英] MySql - Sequalize - Cannot add foreign key constraint

查看:101
本文介绍了MySql-Sequalize-无法添加外键约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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指的是您原始查询集中不存在的shop表.当您更改订单时,商店表已经存在,因此不会发生错误.

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;

这篇关于MySql-Sequalize-无法添加外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆