首页 > 代码库 > mysq添加外键报错:check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 1

mysq添加外键报错:check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 1

今天用navcat往一个表添加外键的时候报错:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘TYPE=InnoDB‘ at line 1

排查之后发现是因为两个表的类型不一样所致:

 SQL:SHOW TABLE STATUS 查询出数据表的状态,Engine那一列就是

技术分享

 

 使用语句修改表类型:ALTER TABLE `cms_channel` ENGINE = INNODB;

问题解决

===============================================================

 

顺便给出添加外键的sql语句:

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

例子:

alter table cms_article add constraint FK_ARTICLE_DEPT_1 foreign key(dept_id) REFERENCES sys_department(id);

 

用这个语句运行的时候不会报错,客户端也会提示功能修改了多少条数据,但是重新查询数据库表结构的时候发现修改不成功,因为表类型不一样是不能建立外键的

 

mysq添加外键报错:check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 1