以第26天的泰大與MM們的為例.
有三個TABLE,分別是Girls,Billings,Worklog.
但未設外鍵限制.
今天將新增外鍵限制,來看增加了外鍵限制後,
會出現的情況.
修改Table,增加外鍵限制.

ALTER TABLE Billings
  ADD CONSTRAINT FOREIGN KEY (galid) REFERENCES Girls (galid);

ALTER TABLE Worklog
  ADD CONSTRAINT FOREIGN KEY (galid) REFERENCES Girls (galid);

當增加外鍵限制以後,在Billings嘗試輸入5號,而5號在Girls並沒有資料,
如下所示:

INSERT INTO Billings(galid, bill_date, bill_rate) VALUES
(5, '2013-01-01', 35);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`akina`.`Billings`, CONSTRAINT `Billings_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`))

會出現錯誤訊息! 無法隨意新增.
如要UPDATE,也會受到限制.
如下所示:

UPDATE Billings
   SET galid = 5
 WHERE galid = 1;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`akina`.`Billings`, CONSTRAINT `Billings_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`))

一樣會受到限制!

那UPDATE Girls的呢?

UPDATE Girls
   SET galid = 5
 WHERE galid = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`akina`.`Billings`, CONSTRAINT `Billings_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`))

一樣是受到限制,訊息不同.一個是child,另一個是parent.

當要把Girls的1號刪除:

DELETE 
  FROM Girls
 WHERE galid = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`akina`.`Billings`, CONSTRAINT `Billings_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`))

因為有Child的資料,所以Parent的資料無法刪除.

這樣就確保了資料之間的關聯正常,以避免Parent的資料刪除了,
而相關的Child的資料,會出現NULL的狀況.

而外鍵限制卻不只上述的情況,留待明天繼續討論.