到目前為止，已經知道Normalization要消除資料重覆性以及消除資料不完整，接著也認識了Primary Key與Foreign Key可以用來讓資料表對應找到彼此，最後用JOIN語法將資料表真正的連結起來，可以依據不同情境取得我們要的資料，不過如果關係改變了呢?

例如王小虎老師離職了，那麼原本由他教導的程式設計課程teacher_id的資料值1將無法在teachers的資料表當中找到資料，如此一來又再次出現了資料不完整的情況。

第二個情況，課程資料表新增了一堂課，但是teacher_id卻輸入了一個在teacher資料表當中不存在的id(例如100)，那麼就如同上個情況，資料無法對應，一樣會發生資料不完整的情況。

截至目前為止，我們所使用建立的TABLE跟JOIN，存在資料庫完整性的問題。

引用完整性

需要認識的術語，先來看看維基百科怎麼說。

• 引用完整性(Referential Integrity)
• 外鍵限制(Foreign Key Constraints)

參照完整性，又稱引用完整性，是數據的屬性，用以表明引用之有效。
參照的完整性不允許關係中有不存在的實體引用。
參照完整性與實體完整性二者，皆是關係模型必須滿足的完整性約束條件
，其目的在於保證數據一致性。

資料來源 : 維基百科

我給翻譯翻譯什麼叫引用完整性、什麼叫外鍵限制

1. 我們不要資料庫出現引用的資料找不到的情形發生，這種情況或這個要求被稱為引用完整性。
2. 可以透過資料庫所提供的外鍵限制功能去防止這件事情發生，這個功能是使用REFERENCES告訴資料庫這個外鍵實質上是對應到哪個被引用資料表。
   1. [欄位名稱]通常使用被引用資料表的單數然後再加上底線ID表示
   2. [資料型別]被引用的資料表如果是使用SERIAL建立就會是INT，如果是自定義則可能是VARCHAR
   3. [引用]使用REFERENCES關鍵字來告訴資料庫，接下來要引用哪張資料表。
   4. [資料表]被引用的資料表名稱。
   5. [欄位]被引用的資料表要用哪一個欄位去對應，如果是被引用表的PRIMARY KEY則可以省略，不過加上會更為清楚，因此還是建議加上。

動手做

把原本DAY17的資料，加上外鍵限制並且執行看看。
結果發生異常，因為建立users的時候，資料庫還沒有addresses，所以無法引用。

relation "addresses" does not exist

-- 先刪掉原本DAY17所建立的內容。
DROP TABLE users;
DROP TABLE addresses;
DROP TABLE districts;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    email VARCHAR(300) NOT NULL,
    address_id INT REFERENCES addresses (id)
);

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    address VARCHAR(300) NOT NULL,
    district_id INT REFERENCES districts (id)
);

CREATE TABLE districts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) UNIQUE NOT NULL
);

所以建立表格的時候要反過來建立，依照這個建立的順序就不會發生引用資料表尚不存在的這個狀況發生了，再執行一次就成功了:)

CREATE TABLE districts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) UNIQUE NOT NULL
);

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    address VARCHAR(300) NOT NULL,
    district_id INT REFERENCES districts (id)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    email VARCHAR(300) NOT NULL,
    address_id INT REFERENCES addresses (id)
);

把昨天的資料寫入也沒有問題。

INSERT INTO districts (name)
VALUES ('雲林縣'),('嘉義縣'),('台南市'),('高雄市');

INSERT INTO addresses (address, district_id)
VALUES 
    ('雲林縣斗南鎮賢者模式路0號', 1),
    ('嘉義縣民雄鄉肉包路35號',2),
    ('台南市歸仁區沙城路980號',3),
		('高雄市旗津區砲台路168號',4);

INSERT INTO users (name, email, address_id)
VALUES 
    ('鄭小元', 'smallbaby@gmail.com', 1),
    ('李小刀', 'okok148@gmail.com',2),
    ('詹小舜', 'lbjking2003@gmail.com',3);

接著關鍵了，我們來測試一下寫入一筆不存在的外鍵會發生什麼事?
結果顯示系統異常，這是因為現在有外鍵約束，表格之間的對應必需要能對應起來，你要插入一筆不能對應的資料的話，系統是不給過的，這樣的結果符合引用完整性，正是我們要的。

insert or update on table "users" violates foreign key constraint "users_address_id_fkey"

INSERT INTO users (name, email, address_id)
VALUES ('梁小伯', 'thesamllball@gmail.com', 100);

但是刪除或更新的時候呢?

我們成功在寫入資料的時候顧及了引用完整性，但是想想看萬一資料被刪除或異動了，莫非又要變成發生資料對不上的問題了嗎?為了不讓這件事情發生，資料庫預設不讓你刪除或更新資料。(嗯…)

在這裡我把有設置外鍵的表格(就是有引用其他資料表)稱為本表，被引用的資料表我稱為被引用表。

-- 本表
CREATE TABLE users (
	address_id INT REFERENCES addresses(id) 
)

-- 被引用表
CREATE TABLE addresses (
	id SERIAL PRIMARY KEY,
)

不過這樣又會產生另一個嚴重的問題，我要刪除或更新的時候怎麼辦?
幸好這件事情有對應的解法，我們可以告訴資料庫被引用表刪除或更新的時候，本表要做什麼事。

一旦本表與被引用表是對應關係的時候 :

CASCADE or SET NULL

實務上最常見的可能會是CASCADE，因為這既符合引用完整性又可以進行資料的刪除或修改，不過我想特別提一下 SET NULL 或許也適合一些關聯情境，舉上例來說，刪除地址資料就會把使用者資料一併刪除，但是我們不一定想這麼做，因為使用者資料可能已經被線上RUN的產品使用中，被刪掉的話會嚴重影響到產品，更恐怖的是可能會有許多系統都有使用到這張資料表，因此替代方案是我們可以設置SET NULL，讓使用者引用暫時為空，待地址更新後再補上外鍵值，因此使用哪一個還是要分析一下再做決定。

-- 本表
CREATE TABLE users (
	address_id INT REFERENCES addresses(id) ON DELETE SET NULL 
)

-- 被引用表
CREATE TABLE addresses (
	id SERIAL PRIMARY KEY,
)