ON DELETE
當該屬性同時是primary key也是foreign key時則不能 設成ON DELETE SET NULL，因為primary key不能是NULL。
- ON DELETE SET NULL
`emp_id`被刪掉，就把`manager_id`設成NULL-- Branch
CREATE TABLE `branch`(
`branch_id` INT PRIMARY KEY,
`branch_name` VARCHAR(20),
`manager_id` INT,
FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL
);
- ON DELETE CASCADE
如果對應的`employee`(`emp_id`)被刪掉，works_with的`emp_id`跟著一起刪掉資料
-- Works_With
CREATE TABLE `works_with`(
`emp_id` INT ,
`client_id` INT ,
`total_sales` INT,
PRIMARY KEY(`emp_id`,`client_id`),
FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);
實作 :
- 刪除'小綠'的員工資料
DELETE FROM `employee`
WHERE `emp_id` = 207;
SELECT * FROM `branch`;
SELECT * FROM `works_with`;
SELECT * FROM `branch`;
SELECT * FROM `works_with`;
