MySQL 外键 - 父表更新或删除对子…

CREATE TABLE department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

name VARCHAR(20) NOT NULL UNIQUE

) ENGINE = INNODB;

INSERT department(name) VALUE('设计部');

INSERT department(name) VALUE('营销部');

INSERT department(name) VALUE('管理部');

CREATE TABLE employee(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

name VARCHAR(20) NOT NULL UNIQUE,

depId TINYINT UNSIGNED,

CONSTRAINT emp_fk_dep   FOREIGN KEY(depId) REFERENCES department(id) 

ON DELETE CASCADE

ON UPDATE CASCADE

)ENGINE = INNODB;

INSERT employee(name, depId) VALUES('Kylie', 1);

INSERT employee(name, depId) VALUES('Dannii', 2);

INSERT employee(name, depId) VALUES('jimmy', 3);

----------------------------------------------------------------------------

级联操作 CASCADE

...

ON DELETE CASADE

ON UPDATE CASADE

...

设为空 SET NULL

ON DELETE    SET NULL

ON UPDATE    SET NULL

拒绝操作 RESTRICT 或 NO ACTION ?

   

物理外键（INNODB） 和 逻辑外键（删除外键时进行提示）