SQL 外鍵(foreign key)

綱要(schema)

對資料表內的資料描述(資料欄與資料表),以及任何相關物件和各種連結方式的描述。

外鍵(foreign key)

資料表裡的某一欄,它參照到另一個資料表的主鍵。

外鍵可能與它參照的主鍵名稱不同。

外鍵使用的主鍵,也被稱為父鍵(parent key),主鍵所在的的資料表,又稱為父資料表(parent table)

外鍵能用於確認甲資料表裡的紀錄與乙資料表能夠對應。

外鍵值可以是NULL,即使主鍵值不可為NULL。

外鍵值不需要獨一無二,通常都沒有唯一性。

外鍵值為NULL,表示在父資料表裡沒有相符的主鍵。

可透過限制條件(constraint)確認外鍵包含有意義,已儲存在父資料裡面的值。

外鍵限制

參照完整性 (referential integrity)

插入外鍵欄位的值,必需已經存在父資料表的來源欄位裡。

使用外鍵參照父資料表裡面某個獨一無二的值,
外鍵不見得必須是父資料表的主鍵,但必須具有唯一性。



MYSQL 內設定 foreign key需滿足

1.該資料表類型需設為InnoDB

2.外鍵欄位須建立索引類型index

3.確認外鍵的資料型態需與父鍵相同後,在關聯查看內設定constraint

        選擇關聯的表及欄位                    自訂約束名稱                     選擇reference_option

reference_option:

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

alter table 增加constraint語法 

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]