blocks|key|494002|text|我在使用HeidiSQL时也遇到了同样的问题。你收到的错误是非常隐秘的。我的问题最终是外键列和引用列的类型或长度不同。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494003|外键列是SMALLINT(5)+UNSIGNED，被引用的列是INT(10)+UNSIGNED。一旦我将它们设置为完全相同的类型，外键创建就可以完美地工作了。|offset|length|style|CODE|494004|entityMap^0|0|4|K|V|G|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|I|$]]

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.

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

blocks|key|709911|text|当使用MyISAM引擎创建父表时，我也遇到了同样的问题。这是一个愚蠢的错误，我改正了：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|709912|ALTER+TABLE+parent_table+ENGINE=InnoDB;|code-block|syntax|javascript|709913|entityMap^0|3|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I had the same problem when the parent table was created using <code>MyISAM</code> engine. It's a silly mistake, which I fixed with:

<pre><code>ALTER TABLE parent_table ENGINE=InnoDB;
</code></pre>

blocks|key|710074|text|确保列相同(类型相同)，如果引用列不是primary_key，请确保它是INDEXED。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|710075|entityMap^0|J|B|10|7|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

make sure columns are identical(of same type) and if reference column is not <code>primary_key</code>, make sure it is <code>INDEXED</code>.

blocks|key|494042|text|定义外键的语法是非常宽松的，但对于任何在这方面遇到困难的人来说，外键必须是“相同类型”的事实甚至适用于排序规则，而不仅仅是数据类型、长度和位符号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494043|并不是说您会在模型中混合使用排序规则(不是吗？)但如果您这样做了，请确保您的主键和外键字段在phpmyadmin或Heidi+SQL或您使用的任何东西中具有相同的排序规则类型。|494044|希望这能帮你省下四个小时的试错时间。|494045|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Syntax for defining foreign keys is very forgiving, but for anyone else tripping up on this, the fact that foreign keys must be "of the same type" applies even to collation, not just data type and length and bit signing.

Not that you'd mix collation in your model (would you?) but if you do, be sure your primary and foreign key fields are of the same collation type in phpmyadmin or Heidi SQL or whatever you use.

Hope this saves you the four hours of trial and error it cost me.

blocks|key|710106|text|mysql错误文本没有太多帮助，在我的例子中，列有"not+null“约束，因此不允许使用"on+delete+set+null”|type|unstyled|depth|inlineStyleRanges|entityRanges|data|710107|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

mysql error texts doesn't help so much, in my case, the column had "not null" constraint, so the "on delete set null" was not allowed

blocks|key|709962|text|只是为了完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|709963|如果您的外键带有VARCHAR(..)，则也可能出现此错误。并且被引用的表的字符集与引用它的表不同。|709964|例如，Latin1表中的VARCHAR(50)不同于UTF8表中的VARCHAR(50)。|709965|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Just for completion.

This error might be as well the case if you have a foreign key with VARCHAR(..) and the charset of the referenced table is different from the table referencing it.

e.g. VARCHAR(50) in a Latin1 Table is different than the VARCHAR(50) in a UTF8 Table.

blocks|key|494306|text|如果一切正常，只需在foregin+key的末尾添加->unsigned();即可。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|494307|如果不起作用，请检查两个字段的数据类型。它们必须是相同的。|494308|entityMap^0|A|B|Q|D|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

if everything is ok, just add <code>-&gt;unsigned();</code> at the end of <code>foregin key</code>.

if it does not work, check the datatype of both fields. they must be the same.

blocks|key|495046|text|显示此错误的另一个可能原因。我创建表格的顺序是错误的。我正在尝试引用尚未创建的表中的键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|495047|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One more probable cause for the display of this error. The order in which I was creating tables was wrong. I was trying to reference a key from a table that was not yet created.

blocks|key|494911|text|我也有同样的问题，两个列都是INT(11)+NOT+NULL，但是我不能创建外键。我必须禁用外键检查才能成功运行它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494912|SET+FOREIGN_KEY_CHECKS=OFF;
ALTER+TABLE+...+ADD+CONSTRAINT+...
SET+FOREIGN_KEY_CHECKS=ON;|code-block|syntax|javascript|494913|希望这对某些人有帮助。|494914|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I had the same issue, both columns were INT(11) NOT NULL but I wan't able to create the foreign key.
I had to disable foreign keys checks to run it successfully :

<pre><code>SET FOREIGN_KEY_CHECKS=OFF;
ALTER TABLE ... ADD CONSTRAINT ...
SET FOREIGN_KEY_CHECKS=ON;
</code></pre>

Hope this helps someone.

blocks|key|494628|text|检查表格引擎，两个表格必须是同一个引擎，这对我帮助很大。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494629|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Check the tables engine, both tables have to be the same engine, that helped me so much.

blocks|key|495658|text|当您在迁移中引用外键表table1之后进行table2迁移时，Laravel中也会出现此问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|495659|您必须保留迁移的顺序，以便foreign+key功能正常工作。|495660|database/migrations/2020_01_01_00001_create_table2_table.php
database/migrations/2020_01_01_00002_create_table1_table.php|code-block|syntax|javascript|495661|应该是：|495662|database/migrations/2020_01_01_00001_create_table1_table.php
database/migrations/2020_01_01_00002_create_table2_table.php|495663|entityMap^0|B|6|L|6|V|7|0|D|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|R|$]]

This problem also occur in <code>Laravel</code> when you have the foreign key table <code>table1</code> migration after the migration in which you reference it <code>table2</code>.
You have to preserve the order of the migration in order to <code>foreign key</code> feature to work properly.
<pre><code>database/migrations/2020_01_01_00001_create_table2_table.php
database/migrations/2020_01_01_00002_create_table1_table.php
</code></pre>
should be:
<pre><code>database/migrations/2020_01_01_00001_create_table1_table.php
database/migrations/2020_01_01_00002_create_table2_table.php
</code></pre>

blocks|key|494864|text|我为此输了好几个小时！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494865|一个表中的主键是utf8，另一个表中的主键是utf8_unicode_ci！|offset|length|style|CODE|494866|entityMap^0|0|8|4|M|F|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|I|$]]

I lost for hours for that!

PK in one table was <code>utf8</code> in other was <code>utf8_unicode_ci</code>!

blocks|key|494409|text|我也有同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494410|问题是引用列不是主键。|494411|让它成为主键，问题就解决了。|494412|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I had the same problems.

The issue is the reference column is not a primary key.

Make it a primary key and problem is solved.

blocks|key|711263|text|对于像我一样在这个问题上苦苦挣扎的人来说，这就是我的问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|711264|我试图修改一个表，以便将字段从VARCHAR(16)更改为VARCHAR(255)，这引用了另一个数据类型仍为VARCHAR(16)的表列...|711265|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

For anyone struggling as I was with this issue, this was my problem:
I was trying to alter a table to change a field from VARCHAR(16) to VARCHAR(255) and this was referencing another table column where the datatype was still VARCHAR(16)...

blocks|key|494250|text|我在Symfony+2.8上也遇到了同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494251|我一开始没有得到它，因为没有类似的外键长度等问题。|494252|最后，我不得不在项目文件夹中执行以下操作。(服务器重新启动没有帮助！)|494253|app/console+doctrine:cache:clear-metadata+++app/console+doctrine:cache:clear-query++app/console+doctrine:cache:clear-result|offset|length|style|CODE|494254|entityMap^0|0|0|0|0|3F|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@$H|R|I|S|J|K]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

I had the same issue with Symfony 2.8.

I didn't get it at first, because there were no similar problems with int length of foreign keys etc.

Finally I had to do the following in the project folder. (A server restart didn't help!)

<code>app/console doctrine:cache:clear-metadata 
 app/console doctrine:cache:clear-query
 app/console doctrine:cache:clear-result</code>

blocks|key|494536|text|我在使用Alter+table在两个表之间添加外键时遇到了问题，而帮助我的是确保我试图添加外键关系的每一列都被索引。要在PHP+myAdmin中执行此操作:转到表并单击structure选项卡。单击索引选项为所需的列建立索引，如屏幕截图所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494537|​|494538|📷|atomic|offset|length|494539|494540|一旦我索引了我试图用外键引用的两列，我就能够成功地使用alter+table并创建外键关系。您将看到列被索引，如下面的屏幕截图所示：|494541|494542|494543|494544|注意zip_code是如何在两个表中显示的。|494545|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/3592c1b695f66e83f71d63585b9a61eb.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/941d6a07b1e5df29105d44075153fd22.png^0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@$G|14|H|15|1|16]]|A|$]]|$1|I|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|18|8|@]|9|@]|A|$]]|$1|L|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|M|3|E|5|F|7|1A|8|@]|9|@$G|1B|H|1C|1|1D]]|A|$]]|$1|N|3|C|5|6|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X|Y|-4]]|Z|$5|T|U|V|A|$W|10|Y|-4]]]]

I had issues using Alter table to add a foreign key between two tables and the thing that helped me was making sure each column that I was trying to add a foreign key relationship to was indexed. To do this in PHP myAdmin:
Go to the table and click on the structure tab.
Click the index option to index the desired column as shown in screenshot:

<a href="https://i.stack.imgur.com/H9eLH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/H9eLH.png" alt="enter image description here"></a>

Once I indexed both columns I was trying to reference with my foreign keys, I was able to successfully use the alter table and create the foreign key relationship. You will see that the columns are indexed like in the below screenshot:

<a href="https://i.stack.imgur.com/D2wMn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/D2wMn.png" alt="enter image description here"></a>

notice how zip_code shows up in both tables.

blocks|key|710292|text|您需要检查两者的所有属性是否相同，包括“排序规则”中的属性|type|unstyled|depth|inlineStyleRanges|entityRanges|data|710293|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You need check that both be same in all its properties, inclusive in "Collation"

blocks|key|710759|text|我刚才遇到了同样的问题。在我的例子中，我所要做的就是确保我在外键中引用的表必须在当前表之前创建(在代码的前面)。因此，如果您引用一个变量(x*5)，系统应该知道x是什么(x必须在前面的代码行中声明)。这解决了我的问题，希望能对别人有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|710760|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I ran into the same issue just now. In my case, all I had to do is to make sure that the table I am referencing in the foreign key must be created prior to the current table (earlier in the code). So if you are referencing a variable (x*5) the system should know what x is (x must be declared in earlier lines of code). This resolved my issue, hope it'll help someone else.

blocks|key|495276|text|我的情况是，我在引用的列上有一个打字错误：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|495277|MariaDB+[blog]>+alter+table+t_user+add+FOREIGN+KEY+(+country_code+)+REFERENCES+t_country+(+coutry_code+);
ERROR+1005+(HY000):+Can't+create+table+`blog`.`t_user`+(errno:+150+"Foreign+key+constraint+is+incorrectly+formed")|code-block|syntax|javascript|495278|错误消息非常隐蔽，我已经尝试了所有方法--验证列的类型、排序规则、引擎等。|495279|我花了一段时间才注意到这个拼写错误，在修复之后一切都很好：|495280|MariaDB+[blog]>+alter+table+t_user+add+FOREIGN+KEY+(+country_code+)+REFERENCES+t_country+(+country_code+);
Query+OK,+2+rows+affected+(0.039+sec)++++++++++++++
Records:+2++Duplicates:+0++Warnings:+0|495281|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

My case was that I had a typo on the referred column:

<pre><code>MariaDB [blog]&gt; alter table t_user add FOREIGN KEY ( country_code ) REFERENCES t_country ( coutry_code );
ERROR 1005 (HY000): Can't create table `blog`.`t_user` (errno: 150 "Foreign key constraint is incorrectly formed")
</code></pre>

The error message is quite cryptic and I've tried everything - verifying the types of the columns, collations, engines, etc.

It took me awhile to note the typo and after fixing it all worked fine:

<pre><code>MariaDB [blog]&gt; alter table t_user add FOREIGN KEY ( country_code ) REFERENCES t_country ( country_code );
Query OK, 2 rows affected (0.039 sec) 
Records: 2 Duplicates: 0 Warnings: 0
</code></pre>

blocks|key|709885|text|我在MariaDB+10.1上使用Laravel+5.1迁移模式生成器时也遇到了同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|709886|问题是在设置列时，我输入的是unigned而不是unsigned(缺少s字母)。|offset|length|style|CODE|709887|在修复了拼写错误之后，我就修复了这个错误。|709888|entityMap^0|0|E|7|O|8|Z|1|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]|$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

I had the same issue with Laravel 5.1 migration Schema Builder with MariaDB 10.1.

The issue was that I had typed <code>unigned</code> instead of <code>unsigned</code>(the <code>s</code> letter was missing) while setting the column.

After fixing the typo error was fixed for me.

blocks|key|494807|text|甚至我在mysql和liquibase上也遇到了同样的问题。这就是问题所在:您希望引用其他表的列的表在数据类型或数据类型的大小方面都不同。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494808|Error+appears+in+below+scenario:
Scenario+1:
Table+A+has+column+id,+type=bigint
Table+B+column+referenced_id+type+varchar(this+column+gets+the+value+from+the+id+column+of+Table+A.)
Liquibase+changeset+for+table+B:

++++<changeset+id="XXXXXXXXXXX-1"+author="xyz">
++++++++++++<column+name="referenced_id"+**type="varchar"**>
++++++++</column>
++++++++++++</changeset>
++++<changeSet+id="XXXXXXXXXXX-2"+author="xyz">
++++++++++++++++<addForeignKeyConstraint+constraintName="FK_table_A"
++++++++++++++++++++referencedTableName="A"+**baseColumnNames="referenced_id**"
++++++++++++++++++++referencedColumnNames="id"+baseTableName="B"+/>
++++</changeSet>

Table+A+changeSet:

++++<changeSet+id="YYYYYYYYYY"+author="xyz">
+++++<column+**name="id"**+**type="bigint"**+autoIncrement="${autoIncrement}">
++++++++++++++++++++<constraints+primaryKey="true"+nullable="false"/>
++++++++++++++++</column>
++++</changeSet>

Solution:+
correct+the+type+of+table+B+to+bigint+because+the+referenced+table+has+type+bigint.

Scenrario+2:
The+type+might+be+correct+but+the+size+might+not.
e.g.+:
Table+B+:+referenced+column+type="varchar+50"
Table+A+:+base+column+type+="varchar+255"

Solution+change+the+size+of+referenced+column+to+that+of+base+table's+column+size.|code-block|syntax|javascript|494809|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Even i ran into the same issue with mysql and liquibase.
 So this is what the problem is:
 The table from which you want to reference a column of other table is different either in case of datatype or in terms of size of the datatype.

<pre><code>Error appears in below scenario:
Scenario 1:
Table A has column id, type=bigint
Table B column referenced_id type varchar(this column gets the value from the id column of Table A.)
Liquibase changeset for table B:

 &lt;changeset id="XXXXXXXXXXX-1" author="xyz"&gt;
 &lt;column name="referenced_id" **type="varchar"**&gt;
 &lt;/column&gt;
 &lt;/changeset&gt;
 &lt;changeSet id="XXXXXXXXXXX-2" author="xyz"&gt;
 &lt;addForeignKeyConstraint constraintName="FK_table_A"
 referencedTableName="A" **baseColumnNames="referenced_id**"
 referencedColumnNames="id" baseTableName="B" /&gt;
 &lt;/changeSet&gt;

Table A changeSet:

 &lt;changeSet id="YYYYYYYYYY" author="xyz"&gt;
 &lt;column **name="id"** **type="bigint"** autoIncrement="${autoIncrement}"&gt;
 &lt;constraints primaryKey="true" nullable="false"/&gt;
 &lt;/column&gt;
 &lt;/changeSet&gt;

Solution: 
correct the type of table B to bigint because the referenced table has type bigint.

Scenrario 2:
The type might be correct but the size might not.
e.g. :
Table B : referenced column type="varchar 50"
Table A : base column type ="varchar 255"

Solution change the size of referenced column to that of base table's column size.
</code></pre>

blocks|key|494990|text|检查是否以正确的大小写指定了表的名称(如果数据库中的表名区分大小写)。在我的情况下我不得不改变|type|unstyled|depth|inlineStyleRanges|entityRanges|data|494991|+CONSTRAINT+`FK_PURCHASE_customer_id`+FOREIGN+KEY+(`customer_id`)+REFERENCES+`customer`+(`id`)+ON+UPDATE+CASCADE+ON+DELETE+CASCADE|code-block|syntax|javascript|494992|至|494993|+CONSTRAINT+`FK_PURCHASE_customer_id`+FOREIGN+KEY+(`customer_id`)+REFERENCES+`CUSTOMER`+(`id`)+ON+UPDATE+CASCADE+ON+DELETE+CASCADE|494994|请注意，customer已更改为CUSTOMER。|offset|length|style|CODE|494995|entityMap^0|0|0|0|0|4|8|G|8|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Check that you've specified name of the table in the proper case (if table names are case-sensitive in your database). In my case I had to change

<pre><code> CONSTRAINT `FK_PURCHASE_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
</code></pre>

to

<pre><code> CONSTRAINT `FK_PURCHASE_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `CUSTOMER` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
</code></pre>

note the <code>customer</code> changed to <code>CUSTOMER</code>.

blocks|key|710721|text|或者你可以使用DBDesigner4，它有一个图形化的界面来创建你的数据库，并使用FK链接它们。右键单击您的表并选择'Copy+table+SQL+Create‘，这将创建代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|710722|​|710723|📷|atomic|offset|length|710724|710725|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/32bc6709168e63533708db104bc0be76.jpeg|imageAlt^0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@$G|V|H|W|1|X]]|A|$]]|$1|I|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q|R|-4]]]]

Or you can use DBDesigner4 which has a graphical interface to create your database and linking them using FK. Right click on your table and select 'Copy Table SQL Create' which creates the code.
<a href="https://i.stack.imgur.com/BP9Yc.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BP9Yc.jpg" alt="enter image description here" /></a>

blocks|key|710787|text|这是一个古老的话题，但我发现了一些东西。在构建MySQL工作台时，它还会获取其他表的关系。只需留下与您相关的支柱即可。清除其他自动添加的列。这对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|710788|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

It's an old subject but I discovered something. While building a MySQL workbench, it also gets the relationships of the other table. just leave the pillars you relate to. Clear other automatically added columns. This works for me.

blocks|key|495739|text|我也犯了同样的错误，我发现在我自己的例子中，一个表是MyISAM，另一个是INNO。有一次，我将MyISAM表切换到INNO。它解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|495740|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I had the same error, and I discovered that on my own case, one table was MyISAM, and the other one INNO. Once I switched the MyISAM table to INNO. It solved the issue.

blocks|key|711177|text|我在这里缺少的另一个解决方案是，被引用表的每个主键在创建约束的表中都应该有一个带有外键的条目。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|711178|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One more solution which I was missing here is, that each primary key of the referenced table should have an entry with a foreign key in the table where the constraint is created.

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

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

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

mysql Foreign key constraint is incorrectly formed error

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

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

问mysql外键约束格式不正确错误
EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql外键约束格式不正确错误EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql外键约束格式不正确错误
EN