添加外键时Missing index for constraint

发现在添加再见时报错:Failed to add the foreign key constraint. Missing index for constraint

原因:作为被引用的外键，它必须是唯一值，因此得添加约束unique或者primary key.

我遇到的原因：由于我的 tb_article 表的category_id设置为 not null ，并且 tb_category 的主键 id 也为 not null ，但是我的的 tb_article 表中的一行数据中， category_id 为 null ，所以就创建失败了

把 category_id 字段填写好，就OK了

你的 tb_aryicle 的 category_id 字段的设置，和 tb_category 的 id 字段的设置不一样，一定要保证他们 datatype 和一些属性(如： not null 等)一致。

这也是之前遇到过的问题，这里记录一下

我遇到的原因：当前数据表和引用表的对应字段属性不一样(疏忽大意)

把当前数据表的 category_id 字段改成varchar类型就好了

因为 log_level 表的 level_lv 不是主键。 也没有 唯一约束。

所以不能创建外键。

解决办法：

给 level_lv 加一个 唯一约束。

下面是一个例子：1234567891011121314151617181920212223242526272829303132333435363738394041424344-- 创建测试主表. 无主键.CREATE TABLE test_main2 ( id <A class=baidu-highlight href="https://www.baidu.com/s?wd=INT&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>INT</A> NOT <A class=baidu-highlight href="https://www.baidu.com/s?wd=NULL&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>NULL</A>, value <A class=baidu-highlight href="https://www.baidu.com/s?wd=VARCHAR&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>VARCHAR</A>(10))-- 创建测试子表. CREATE TABLE test_sub2 ( id <A class=baidu-highlight href="https://www.baidu.com/s?wd=INT&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>INT</A> NOT <A class=baidu-highlight href="https://www.baidu.com/s?wd=NULL&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>NULL</A>, main_id <A class=baidu-highlight href="https://www.baidu.com/s?wd=INT&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>INT</A>, value <A class=baidu-highlight href="https://www.baidu.com/s?wd=VARCHAR&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>VARCHAR</A>(10), PRIMARY <A class=baidu-highlight href="https://www.baidu.com/s?wd=KEY&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>KEY</A>(id) )-- 插入测试主表数据.INSERT INTO test_main2(id, value) VALUES (1, 'ONE')INSERT INTO test_main2(id, value) VALUES (2, 'TWO')-- 插入测试子表数据.INSERT INTO test_sub2(id, main_id, value) VALUES (1, 1, 'ONEONE')INSERT INTO test_sub2(id, main_id, value) VALUES (2, 2, 'TWOTWO') mysql>ALTER TABLE test_sub2-> ADD CONSTRAINT main_id_cons2-> FOREIGN <A class=baidu-highlight href="https://www.baidu.com/s?wd=KEY&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>KEY</A>(main_id)-> REFERENCES test_main2(id)ERROR 1005 (HY000): Can't create table 'test.#sql-608_1' (errno: 150) 默认情况下，无法创建外键需要创建 <A class=baidu-highlight href="https://www.baidu.com/s?wd=unique&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>unique</A>约束 mysql>ALTER TABLE test_main2-> CHANGE COLUMN id id INT <A class=baidu-highlight href="https://www.baidu.com/s?wd=UNIQUE&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>UNIQUE</A>Query OK, 2 rows affected (0.17 sec)Records: 2 Duplicates: 0 Warnings: 0<A class=baidu-highlight href="https://www.baidu.com/s?wd=unique&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>unique</A>约束创建完毕后，外键创建成功。mysql>ALTER TABLE test_sub2-> ADD CONSTRAINT main_id_cons2-> FOREIGN <A class=baidu-highlight href="https://www.baidu.com/s?wd=KEY&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1dbmyRvn1NhmH0Ynj63m1mv0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHf4PH0LrHmLPWDvnjD1n1RLrf" target=_blank>KEY</A>(main_id)-> REFERENCES test_main2(id)Query OK, 2 rows affected (0.14 sec)Records: 2 Duplicates: 0 Warnings: 0

---