mysql外键约束格式错误 [英] mysql Foreign key constraint is incorrectly formed error

问题描述

我有两个表，table1是具有列ID的父表，而table2是具有列IDFromTable1的父表(不是实际名称)，当我在IDFromTable1上将FK放置到ID时在table1中，我得到错误Foreign key constraint is incorrectly formed error.如果要删除table1记录，我想删除表2记录.感谢您的帮助

I have two tables, table1 is the parent table with a column ID and table2 with a column IDFromTable1 (not the actual name) when I put a FK on IDFromTable1 to ID in table1 I get the error Foreign key constraint is incorrectly formed error. I would like to delete table 2 record if table1 record gets deleted. Thanks for any help

ALTER TABLE `table2`  
   ADD CONSTRAINT `FK1` 
      FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`) 
      ON UPDATE CASCADE 
      ON DELETE CASCADE;

让我知道是否需要其他信息.我是mysql的新手

Let me know if any other information is needed. I am new to mysql

推荐答案

我在HeidiSQL中遇到了同样的问题.您收到的错误非常神秘.我的问题最终是外键列和引用列的类型或长度不同.

I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length.

外键列为SMALLINT(5) UNSIGNED，引用列为INT(10) UNSIGNED.一旦将它们设置为完全相同的类型，外键创建就可以完美地工作了.

The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same exact type, the foreign key creation worked perfectly.

这篇关于mysql外键约束格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！