-- 建立測試主表. ID 是主鍵.

CREATE TABLE test_main (

id INT,

value VARCHAR(10),

PRIMARY KEY(id)

);

-- 建立測試子表.

CREATE TABLE test_sub (

id INT,

main_id INT,

value VARCHAR(10),

PRIMARY KEY(id)

);

預設外來鍵約束方式

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;

DELETE CASCADE 方式

ALTER TABLE test_sub

ADD CONSTRAINT main_id_cons

FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;

UPDATE CASCADE方式

-- 建立外來鍵(使用 ON UPDATE CASCADE 選項，更新主表的主鍵時候，同時更新子表外來鍵)

ALTER TABLE test_sub

ADD CONSTRAINT main_id_cons

FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;

SET NULL方式

ALTER TABLE test_sub

ADD CONSTRAINT main_id_cons

FOREIGN KEY (main_id) REFERENCES test_main ON DELETE SET NULL;