InnoDB 與 FOREIGN KEY

我們知道要讓兩個資料表有關聯，可以用外鍵(FOREIGN KEY )進行關聯。

【範例一】

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

CREATE TABLE myorder (
order_id INT NOT NULL,
prod_id INT,
PRIMARY KEY (order_id));

上述表單 product 的主鍵是 prod_id ; 表單 myorder 的主鍵是 order_id，並透過外鍵 myorder.prod_id 與product.prod_id做關聯。

然後插入資料

insert into product values (1,'Pen'),(2,'Pencil'),(3,'Box');
insert into myorder values (1,1),(2,2),(3,3);

但是當我們刪除product的資料時

delete from product where prod_id=1;

結果如下

系統根本不知道，prod_id=1這個資料是被另外表單參考的。
所以product中的資料被刪除，myorder.prod_id=1 就參考不到正確的資料了。

【範例二】

所以，我們刪除上面的表單後再另外宣告

CREATE TABLE product (
prod_id INT NOT NULL,
prod_name CHAR(20),
PRIMARY KEY (prod_id))

ENGINE = MYISAM;

CREATE TABLE myorder (
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 = MYISAM;

這次宣告了FOREIGN KEY(prod_id) REFERENCES product(prod_id)

同樣的再插入資料

insert into product values (1,'Pen'),(2,'Pencil'),(3,'Box');
insert into myorder values (1,1),(2,2),(3,3);

這次，但是當我們刪除product的資料時

delete from product where prod_id=1;

蝦米啊，也跟範例一完全相同，資料就刪除了，我們宣告FOREIGN KEY竟然完全沒有用。

【範例三】

所以，我們刪除上面的表單後再另外宣告

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

CREATE TABLE myorder (
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;

這次宣告ENGINE = INNODB;

同樣的再插入資料

insert into product values (1,'Pen'),(2,'Pencil'),(3,'Box');
insert into myorder values (1,1),(2,2),(3,3);

這次，但是當我們刪除product的資料時

delete from product where prod_id=1;

發現，刪除了product中的一筆資料，同時myorder對應的資料也不見了。

這就是ON DELETE CASCADE發揮效果了。

再進行

update product set prod_id=9 where prod_id=2;

發現了一件事

更新了product中的一筆資料，同時myorder對應的資料也更新了。

這就是ON UPDATE CASCADE發揮效果了。

然後我們進行

drop table product;

出現錯誤訊息，告訴你因為外鍵限制，刪除表單失敗。

因為表單product還要讓 myorder 參考，所以不能刪除。所以要先刪除 myorder，才能刪除 product。如下:

【範例四】

所以，我們刪除上面的表單後再另外宣告

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

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

這次只宣告 ON UPDATE CASCADE

同樣的再插入資料

insert into product values (1,'Pen'),(2,'Pencil'),(3,'Box');
insert into myorder values (1,1),(2,2),(3,3);

這次，但是當我們刪除product的資料時

delete from product where prod_id=1;

刪除時出現錯誤訊息，告訴你違反外鍵參考原則。

但是如果進行

update product set prod_id=9 where prod_id=2;

結果 :

只宣告 ON UPDATE CASCADE，因此會將有所關聯的紀錄行也會進行修改。

但是沒有須安告 ON DELETE CASCADE，因此無法進行刪除。

參數：
[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 相同。

所以，結論是設定外鍵參考，如果不是INNODB，尚無法真正建立外鍵參考限制，必須使用ENGINE = INNODB，然後才能啟用。

外鍵約束（FOREIGN KEY，縮寫FK）是用來實現數據庫表的參照完整性的。

[如何知道那些是innodb?]

SELECT table_schema, table_name 
FROM INFORMATION_SCHEMA.TABLES 
WHERE engine = 'innodb';

[如何在設定檔修改預設引擎?]
SET default_storage_engine=INNODB;

[如何知道預設引擎?]
mysql> SELECT @@default_storage_engine;
+--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+

[如何以指令設定引擎?]
CREATE TABLE t1 (i INT) ENGINE = INNODB;
OR
ALTER TABLE t1 ENGINE = InnoDB;