MySQL 基本語法 (十三) ON DELETE

ON DELETE

當該屬性同時是primary key也是foreign key時則不能 設成ON DELETE SET NULL，因為primary key不能是NULL。

1. 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
);

2. 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
);

實作 :

3. 刪除'小綠'的員工資料

DELETE FROM `employee`
WHERE `emp_id` = 207; 

SELECT * FROM `branch`;
SELECT * FROM `works_with`;