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

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

這次宣告了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 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 相同。

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


[如何知道那些是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;

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理