Question

Mysql 添加外键 出现#1215的错误





Answer 1

因为 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

追问

对，我删掉一个主键他就好了！虽然早就弄好啦，但是你的答案很对