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

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

检查下你的parent_id字段的数据类型，长度是不是和tb_shop_category表的shop_category字段的定义相同

然后既然parent_id是外键字段，就不要设置default null

先在父表中将对应的记录插进去，再插子表，因为子表中外键约束的一

列数据

必须在父表中存在。像学生表和学生成绩表，在学生成绩表中要插入一条记录，如果此记录中学号字段的值在学生表中找不到，即不存在这个学生，自然学生成绩表中就插不进去。

---