【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
外來鍵約束可以起到限定了一個列的取值範圍的作用。透過這個實驗，展示一下主外來鍵參照關係中一些有趣的現象（約束與限制）。
1.建立父表t_parent，並初始化父表資料
1）建立父表，其中包含兩列，第一列存放小名或綽號（nickname），第二列存放全名（fullname）資訊
sec@ora10g> create table t_parent (nickname varchar2(10), fullname varchar2(30)) tablespace tbs_sec_d;
Table created.
2）在nickname列上建立主鍵
sec@ora10g> alter table t_parent add constraint pk_t_parent primary key (nickname) using index tablespace tbs_sec_i;
Table altered.
3）向父表中初始化如下4條資料
sec@ora10g> insert into t_parent values ('sec', 'Secooler Hou');
sec@ora10g> insert into t_parent values ('Hou', 'Andy Hou');
sec@ora10g> insert into t_parent values ('TuTu', 'Daining Hou');
sec@ora10g> insert into t_parent values ('Xu', 'Anna Xu');
sec@ora10g> insert into t_parent values ('RQ', 'Richard Qin');
sec@ora10g> commit;
2.建立子表t_child，並初始化子表資料
1）建立子表，這裡簡單的包含兩列資訊，一列是這個表的id資訊t_child_id，另外一列為nickname，用作參照完整性引用列
sec@ora10g> create table t_child (t_child_id number not null, nickname varchar2(10)) tablespace tbs_sec_d;
Table created.
2）在t_child_id列建立主鍵
sec@ora10g> alter table t_child add constraint pk_t_child primary key (t_child_id) using index tablespace tbs_sec_i;
Table altered.
3）建立外來鍵約束。建立完成後這個子表t_child的nickname列將會參照父表t_parent的nickname列內容
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname);
Table altered.
建立外來鍵約束的語法裡需要注意的一個細節
上面的建立語句完全可以寫成如下的簡略形式，如果沒有顯示的指定參照欄位，預設參照欄位是父表的主鍵列。
alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent;
但是，如果外來鍵欄位參照的是非主鍵欄位，則必須在add constraint語句中指定欄位名。
4）在外來鍵上建立索引。當應用程式投入生產之前，一定要記得給外來鍵建立索引，否則會因為子表的鎖定機制導致應用程式效率極其低下！
sec@ora10g> create index t_child_idx on t_child (nickname);
Index created.
5）初始化如下十條資料，展示一個父表和子表之間的一對多的關係
sec@ora10g> insert into t_child values (1, 'sec');
sec@ora10g> insert into t_child values (2, 'TuTu');
sec@ora10g> insert into t_child values (3, 'sec');
sec@ora10g> insert into t_child values (4, 'Hou');
sec@ora10g> insert into t_child values (5, '');
sec@ora10g> insert into t_child values (6, '');
sec@ora10g> insert into t_child values (7, 'Xu');
sec@ora10g> insert into t_child values (8, 'Xu');
sec@ora10g> insert into t_child values (9, 'Xu');
sec@ora10g> insert into t_child values (10, 'Xu');
sec@ora10g> commit;
3.檢視一下初始化資料之後父子表中的資料情況，有一個直觀的印象
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
sec Secooler Hou
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
1 sec
2 TuTu
3 sec
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
10 rows selected.
4.到此，已經完成了整個父子表的初始化工作。我們來探索一下在主外來鍵參照關係中都有哪些有趣的現象（約束與限制）
1）有趣現象一：對父表更新的限制
sec@ora10g> update t_parent set nickname='NEW' where nickname='RQ';
1 row updated.
sec@ora10g> update t_parent set nickname='NEW' where nickname='sec';
update t_parent set nickname='NEW' where nickname='sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found
為什麼第一條可以成功，但是第二條無法完成呢？
原因很簡單，因為“sec”已經在子表中存在了，所有不能對父表的對應記錄進行更新操作；
然而，“RQ”因為沒有在子表中出現過，所以沒有這個限制。
2）有趣現象二：對父表刪除的限制
sec@ora10g> delete from t_parent where nickname = 'RQ';
1 row deleted.
sec@ora10g> delete from t_parent where nickname = 'sec';
delete from t_parent where nickname = 'sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found
為什麼第一條可以成功，但是第二條無法完成呢？
原因也很簡單，和第一個現象的原因相同。僅僅可以刪除子表中沒有使用過的父表記錄，對子表中已經應用的資訊父表是不能隨便刪除的。
不過，有一個被稱作“級聯刪除”的選項可以改變上面的行為，演示如下：
先drop掉原有的外來鍵約束
sec@ora10g> alter table t_child drop constraint fk_t_child_nickname;
Table altered.
再建立帶級聯刪除（on delete cascade）功能的外來鍵約束
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname) on delete cascade;
Table altered.
OK，重新演示一下上面的刪除語句
為比較刪除前和刪除後的區別，先得到刪除前的父子表中資料情況
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
sec Secooler Hou
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
1 sec
2 TuTu
3 sec
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
10 rows selected.
執行刪除操作，可以看到，這回操作成功了。
sec@ora10g> delete from t_parent where nickname = 'sec';
1 row deleted.
看一下刪除後的父子表中資料情況
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
2 TuTu
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
8 rows selected.
發現效果了吧，不但父表中有關“sec”的記錄被刪除了，而且，子表中的所有與“sec”關聯的資訊（這個實驗中是兩條“sec”記錄）也同樣的被刪除掉了。這就是級聯刪除選項在起作用。
3）有趣現象三：對子表插入的限制
sec@ora10g> insert into t_child values (11,'Rex');
insert into t_child values (11,'Rex')
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found
不能向子表中插入這條資料的原因非常的簡單：因為主表中沒有“Rex”記錄，所以這條子表插入操作被拒之門外了。
4）有趣現象四：對子表更新的限制
sec@ora10g> update t_child set nickname = 'Rex' where nickname = 'sec';
update t_child set nickname = 'Rex' where nickname = 'sec'
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found
不能更新子表的原因也是因為父表中不存在“Rex”資訊，所以沒有辦法更新成這個值。從錯誤提示資訊中可以得到有效的提醒。
5.外來鍵約束小結
在資料庫表設計的過程中，外來鍵約束可以有效的保證表與表之間的約束關係。
永遠記住在建立外來鍵之後，要在外來鍵上建立索引！
深刻了解外來鍵帶給我們的約束和限制場景，避免在應用使用中出現不該有的錯誤。
總結一下外來鍵的約束限制：
1）對父表更新的限制：不能把父表中的值更新為子表仍在使用而父表中不存在的值；
2）對父表刪除的限制：子表中引用的父表記錄不允許被刪除；
3）對子表插入的限制：不允許相子表中插入父表中不存在的值；
4）對子表更新的限制：不允許將子表的值更新為父表中不存在的值。
本人是“實踐派”的，用實驗說話，並一直堅持認為：基本概念是“萬物之源”。希望這個小文兒能對您澄清外來鍵約束這個概念有所幫助。
Goodluck.
-- The End --
1.建立父表t_parent，並初始化父表資料
1）建立父表，其中包含兩列，第一列存放小名或綽號（nickname），第二列存放全名（fullname）資訊
sec@ora10g> create table t_parent (nickname varchar2(10), fullname varchar2(30)) tablespace tbs_sec_d;
Table created.
2）在nickname列上建立主鍵
sec@ora10g> alter table t_parent add constraint pk_t_parent primary key (nickname) using index tablespace tbs_sec_i;
Table altered.
3）向父表中初始化如下4條資料
sec@ora10g> insert into t_parent values ('sec', 'Secooler Hou');
sec@ora10g> insert into t_parent values ('Hou', 'Andy Hou');
sec@ora10g> insert into t_parent values ('TuTu', 'Daining Hou');
sec@ora10g> insert into t_parent values ('Xu', 'Anna Xu');
sec@ora10g> insert into t_parent values ('RQ', 'Richard Qin');
sec@ora10g> commit;
2.建立子表t_child，並初始化子表資料
1）建立子表，這裡簡單的包含兩列資訊，一列是這個表的id資訊t_child_id，另外一列為nickname，用作參照完整性引用列
sec@ora10g> create table t_child (t_child_id number not null, nickname varchar2(10)) tablespace tbs_sec_d;
Table created.
2）在t_child_id列建立主鍵
sec@ora10g> alter table t_child add constraint pk_t_child primary key (t_child_id) using index tablespace tbs_sec_i;
Table altered.
3）建立外來鍵約束。建立完成後這個子表t_child的nickname列將會參照父表t_parent的nickname列內容
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname);
Table altered.
建立外來鍵約束的語法裡需要注意的一個細節
上面的建立語句完全可以寫成如下的簡略形式，如果沒有顯示的指定參照欄位，預設參照欄位是父表的主鍵列。
alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent;
但是，如果外來鍵欄位參照的是非主鍵欄位，則必須在add constraint語句中指定欄位名。
4）在外來鍵上建立索引。當應用程式投入生產之前，一定要記得給外來鍵建立索引，否則會因為子表的鎖定機制導致應用程式效率極其低下！
sec@ora10g> create index t_child_idx on t_child (nickname);
Index created.
5）初始化如下十條資料，展示一個父表和子表之間的一對多的關係
sec@ora10g> insert into t_child values (1, 'sec');
sec@ora10g> insert into t_child values (2, 'TuTu');
sec@ora10g> insert into t_child values (3, 'sec');
sec@ora10g> insert into t_child values (4, 'Hou');
sec@ora10g> insert into t_child values (5, '');
sec@ora10g> insert into t_child values (6, '');
sec@ora10g> insert into t_child values (7, 'Xu');
sec@ora10g> insert into t_child values (8, 'Xu');
sec@ora10g> insert into t_child values (9, 'Xu');
sec@ora10g> insert into t_child values (10, 'Xu');
sec@ora10g> commit;
3.檢視一下初始化資料之後父子表中的資料情況，有一個直觀的印象
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
sec Secooler Hou
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
1 sec
2 TuTu
3 sec
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
10 rows selected.
4.到此，已經完成了整個父子表的初始化工作。我們來探索一下在主外來鍵參照關係中都有哪些有趣的現象（約束與限制）
1）有趣現象一：對父表更新的限制
sec@ora10g> update t_parent set nickname='NEW' where nickname='RQ';
1 row updated.
sec@ora10g> update t_parent set nickname='NEW' where nickname='sec';
update t_parent set nickname='NEW' where nickname='sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found
為什麼第一條可以成功，但是第二條無法完成呢？
原因很簡單，因為“sec”已經在子表中存在了，所有不能對父表的對應記錄進行更新操作；
然而，“RQ”因為沒有在子表中出現過，所以沒有這個限制。
2）有趣現象二：對父表刪除的限制
sec@ora10g> delete from t_parent where nickname = 'RQ';
1 row deleted.
sec@ora10g> delete from t_parent where nickname = 'sec';
delete from t_parent where nickname = 'sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found
為什麼第一條可以成功，但是第二條無法完成呢？
原因也很簡單，和第一個現象的原因相同。僅僅可以刪除子表中沒有使用過的父表記錄，對子表中已經應用的資訊父表是不能隨便刪除的。
不過，有一個被稱作“級聯刪除”的選項可以改變上面的行為，演示如下：
先drop掉原有的外來鍵約束
sec@ora10g> alter table t_child drop constraint fk_t_child_nickname;
Table altered.
再建立帶級聯刪除（on delete cascade）功能的外來鍵約束
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname) on delete cascade;
Table altered.
OK，重新演示一下上面的刪除語句
為比較刪除前和刪除後的區別，先得到刪除前的父子表中資料情況
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
sec Secooler Hou
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
1 sec
2 TuTu
3 sec
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
10 rows selected.
執行刪除操作，可以看到，這回操作成功了。
sec@ora10g> delete from t_parent where nickname = 'sec';
1 row deleted.
看一下刪除後的父子表中資料情況
sec@ora10g> select * from t_parent;
NICKNAME FULLNAME
---------- ------------------------------
RQ Richard Qin
Hou Andy Hou
TuTu Daining Hou
Xu Anna Xu
sec@ora10g> select * from t_child;
T_CHILD_ID NICKNAME
---------- ----------
2 TuTu
4 Hou
5
6
7 Xu
8 Xu
9 Xu
10 Xu
8 rows selected.
發現效果了吧，不但父表中有關“sec”的記錄被刪除了，而且，子表中的所有與“sec”關聯的資訊（這個實驗中是兩條“sec”記錄）也同樣的被刪除掉了。這就是級聯刪除選項在起作用。
3）有趣現象三：對子表插入的限制
sec@ora10g> insert into t_child values (11,'Rex');
insert into t_child values (11,'Rex')
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found
不能向子表中插入這條資料的原因非常的簡單：因為主表中沒有“Rex”記錄，所以這條子表插入操作被拒之門外了。
4）有趣現象四：對子表更新的限制
sec@ora10g> update t_child set nickname = 'Rex' where nickname = 'sec';
update t_child set nickname = 'Rex' where nickname = 'sec'
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found
不能更新子表的原因也是因為父表中不存在“Rex”資訊，所以沒有辦法更新成這個值。從錯誤提示資訊中可以得到有效的提醒。
5.外來鍵約束小結
在資料庫表設計的過程中，外來鍵約束可以有效的保證表與表之間的約束關係。
永遠記住在建立外來鍵之後，要在外來鍵上建立索引！
深刻了解外來鍵帶給我們的約束和限制場景，避免在應用使用中出現不該有的錯誤。
總結一下外來鍵的約束限制：
1）對父表更新的限制：不能把父表中的值更新為子表仍在使用而父表中不存在的值；
2）對父表刪除的限制：子表中引用的父表記錄不允許被刪除；
3）對子表插入的限制：不允許相子表中插入父表中不存在的值；
4）對子表更新的限制：不允許將子表的值更新為父表中不存在的值。
本人是“實踐派”的，用實驗說話，並一直堅持認為：基本概念是“萬物之源”。希望這個小文兒能對您澄清外來鍵約束這個概念有所幫助。
Goodluck.
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-613521/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL的主鍵和外來鍵約束SQL
- Oracle定義約束 外來鍵約束Oracle
- oracle外來鍵約束的總結Oracle
- 修改外來鍵為validate時需要驗證資料是否符合外來鍵約束
- 關於外來鍵約束和對應主鍵資訊的查詢指令碼指令碼
- 教你mysql如何增加外來鍵約束MySql
- MySQL禁用恢復外來鍵約束MySql
- 小議Oracle外來鍵約束脩改行為（七）Oracle
- 小議Oracle外來鍵約束脩改行為（六）Oracle
- 小議Oracle外來鍵約束脩改行為（五）Oracle
- 小議Oracle外來鍵約束脩改行為（四）Oracle
- 小議Oracle外來鍵約束脩改行為（三）Oracle
- 小議Oracle外來鍵約束脩改行為（二）Oracle
- 小議Oracle外來鍵約束脩改行為（一）Oracle
- mysql 刪除老是報外來鍵約束MySql
- SQL外來鍵約束的含義及建立SQL
- Oracle外來鍵約束中NULL的處理OracleNull
- AppBoxFuture(七): 分散式外來鍵約束APP分散式
- mysql不能新增外來鍵約束怎麼辦MySql
- mysql啟動和關閉外來鍵約束MySql
- 詳解外來鍵約束（foreign key）
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- PostgreSQL11preview-支援陣列外來鍵約束SQLView陣列
- 外來鍵約束drop table cascade constraintsAI
- 遷移後處理外來鍵約束的問題
- 資料完整性約束：主鍵、外來鍵、各種約束的建立刪除語句
- 資料庫約束 主鍵-唯一性-Check-外來鍵資料庫
- mysql資料庫匯入外來鍵約束問題MySql資料庫
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- 外來鍵有無索引帶來的影響學習與測試索引
- MySQL·捉蟲動態·DROPDATABASE外來鍵約束的GTIDBUGMySqlDatabaseTiDB
- ORACLE: 查詢(看)表的主鍵、外來鍵、唯一性約束和索引Oracle索引
- MYSQL的外來鍵MySql
- sqlserver外來鍵SQLServer
- 分散式資料庫環境中，外來鍵約束的問題？？分散式資料庫
- 解決無法刪除表，提示被外來鍵約束引用