【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
外來鍵約束可以起到限定了一個列的取值範圍的作用。透過這個實驗，展示一下主外來鍵參照關係中一些有趣的現象（約束與限制）。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 外來鍵約束
- Javaweb-約束-外來鍵約束JavaWeb
- 關於外來鍵約束
- 約束外來鍵筆記筆記
- oracle外來鍵約束的總結Oracle
- AppBoxFuture(七): 分散式外來鍵約束APP分散式
- 教你mysql如何增加外來鍵約束MySql
- SQL外來鍵約束的含義及建立SQL
- mysql不能新增外來鍵約束怎麼辦MySql
- mysql~資料完整性考慮~外來鍵約束MySql
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- MariaDB資料庫的外來鍵約束例項程式碼介紹詳解資料庫
- 資料遷移無法新增外來鍵約束，錯誤程式碼 1215
- 主鍵和外來鍵
- MYSQL的外來鍵MySql
- sqlserver外來鍵SQLServer
- indexedDB 內鍵與外來鍵Index
- 生成指令碼，得到所有表的外來鍵約束，然後刪除並重建這些約束指令碼
- Mysql-基本練習(06-唯一約束、外來鍵約束、新增、刪除單列)MySql
- 外來鍵的變種
- 10.30 索引，外來鍵索引
- 通過外來鍵找主鍵
- 實現 MongoDB 外來鍵關聯MongoDB
- Mysql關於資料庫是否應該使用外來鍵約束詳解說明創磅MySql資料庫
- 新的主鍵和外來鍵的語法
- [資料庫]資料庫中為什麼不推薦使用外來鍵約束資料庫
- 【MySQL】MySQL進階（外來鍵約束、多表查詢、檢視、備份與恢復）MySql
- mysql建立外來鍵語句MySql
- 為什麼不用外來鍵
- django外來鍵如何賦值Django賦值
- 《資料庫系統概論》5.0——常見約束 大學生學習筆記（主鍵 外來鍵）資料庫筆記
- 批量刪除MSSQL 中主外來鍵約束SQL
- 無索引的外來鍵之主表子表DML操作實驗及結論索引
- Laravel 學習總結二：get () 和 first () 的區別、@each () 的用法和新增外來鍵約束Laravel
- MySQL 用隨機資料填充外來鍵表MySql隨機
- openGauss-外來鍵鎖增強
- 資料庫：外來鍵是什麼資料庫
- Django（15）外來鍵和表關係Django
- Entity Framework 實體載入外來鍵屬性的問題Framework