MySQL FOREIGN KEY

條件

使用innodb

範例

CREATE TABLE product (
prod_id INT NOT NULL,
prod_name CHAR(20),
PRIMARY KEY (prod_id))
ENGINE = INNODB;

CREATE TABLE order (
order_id INT NOT NULL,
prod_id INT,
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (order_id))
ENGINE = INNODB;

設定 foreign key
 order table 的 prod_id 為foreign key

設定參數
ON DELETE CASCADE
ON UPDATE CASCADE

效果
此時如果 delete product table的某筆資料，
也會 delete order 有foreign key參考到的資料。

此時如果update product table的某筆 prod_id，
也會 update order 有foreign key參考到的 prod_id。

此時drop product table 會出錯，因為 order table 參考到它。

Foreign Key 參數說明

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

當關聯父資料表的主鍵紀錄行被刪除或修改時，InnoDB 對子資料表中紀錄行的處理方式：

CASCADE - 會將有所關聯的紀錄行也會進行刪除或修改。
SET NULL - 會將有所關聯的紀錄行設定成 NULL。
NO ACTION - 有存在的關聯紀錄行時，會禁止父資料表的刪除或修改動作。
RESTRICT - 與 NO ACTION 相同。

Foreign Key效用

1. 利用 db schema 防止程式錯誤及阻擋不合法寫入。
2. 加速 join 效率。

參考

參考