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

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

所以不能创建外键。

解决办法：

给   level_lv   加一个  唯一约束。

下面是一个例子：

-- 创建测试主表.  无主键.

CREATE TABLE test_main2 (

  id      INT   NOT  NULL,

  value   VARCHAR(10)

)

-- 创建测试子表. 

CREATE TABLE test_sub2 (

  id      INT   NOT  NULL,

  main_id INT,

  value   VARCHAR(10),

  PRIMARY KEY(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 KEY (main_id)

    ->    REFERENCES  test_main2(id)

ERROR 1005 (HY000): Can't create table 'test.#sql-608_1' (errno: 150)

 

默认情况下，无法创建外键

需要创建 unique 约束

 

mysql> ALTER TABLE test_main2

    ->   CHANGE COLUMN id id INT UNIQUE

Query OK, 2 rows affected (0.17 sec)

Records: 2  Duplicates: 0  Warnings: 0

  

unique 约束创建完毕后，外键创建成功。

mysql> ALTER TABLE test_sub2

    ->    ADD CONSTRAINT main_id_cons2

    ->    FOREIGN KEY (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

---