mysql 外键set null_外键约束 SET NULL

定义父表

CREATE TABLE provinces(

-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

-> pname VARCHAR(20) UNSIGNED

-> );

插入3个值

INSERT provinces(pname) VALUES('A');

INSERT provinces(pname) VALUES('B');

INSERT provinces(pname) VALUES('C');

定义子表

CREATE TABLE users2(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10),

pid SMALLINT UNSIGNED,

FOREIGN KEY (pid) REFERENCES provinces (id) ON UPDATE SET NULL

);

插入值

INSERT users2(username,pid) VALUES('Tom',3);

INSERT users2(username,pid) VALUES('Jon',2);

INSERT users2(username,pid) VALUES('Kang',3);

删除父表第3列报错！！！！！！！

DELETE FROM provinces WHERE id=3;

错误如下：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users2`, CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON UPDATE SET NULL)

求助：这是为什么呢，我子表的外键列pid并没有设置为NOT NULL，为什么这个外键不生效呢？删除不了父表来更新。