存在外部索引鍵關聯的主表truncate如何做

存在外部索引鍵關聯的主表truncate如何做

主外鍵是資料庫提供的一種兩表之間強制關聯的方法，也可以從應用程式層實現。

	優點	缺點
資料庫實現的主外鍵	由資料庫層機制保證，無需應用額外實現	強關聯，不易擴充變更
應用實現的主外鍵	易擴充變更	完全由應用控制，要求較高

我認為需要根據實際情況進行取捨，例如表不複雜，可以由應用實現，若表之間關聯較多且複雜，那麼交由資料庫處理，至少保證不會錯。

存在主外部索引鍵關聯的主表，由於存在外部索引鍵關聯關係，因此有些操作就會禁止，例如truncate。

實驗

1. 建立測試表
SQL> create table tbl_a(id number, remark varchar2(1));
Table created.

SQL> create table tbl_b(id number, a_id number, remark varchar2(1));
Table created.

SQL> alter table tbl_a add constraint pk_tbl_a primary key(id);
Table altered.

SQL> alter table tbl_b add constraint pk_tbl_b primary key(id);
Table altered.

SQL> alter table tbl_b add constraint fk_tbl_b_a foreign key(a_id) references tbl_a(id);
Table altered.
 
tbl_a是主表，tbl_b是子表，關聯tbl_a。

2. 現在主表和子表沒有任何資料，此時執行truncate主表

SQL> truncate table tbl_a;
Table truncated.
可以執行。

3. 向主表插入一條記錄，再次執行truncate
SQL> insert into tbl_a values(1, 'a');
1 row created.

SQL> commit;
Commit complete.

SQL> truncate table tbl_a;
truncate table tbl_a
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
 
此時提示了ORA-02266：唯一/主鍵被啟用的外鍵引用
 

看看ORA-02266的解釋：

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or
//        primary keys referenced by foreign keys enabled in another table.
//        Other operations not allowed are dropping/truncating a partition of a
//        partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the
//          foreign key constraints in other tables. You can see what
//          constraints are referencing a table by issuing the following
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

比較清楚地說明了問題，以及解決方案：可以在執行前，先禁用外鍵約束，執行truncate後再恢複外鍵約束。

4. 禁用外鍵約束，刪除後執行恢複操作

看到外鍵約束名稱：FK_TBL_B_A：
 SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                      P ENABLED
FK_TBL_B_A                    R ENABLED

 

禁止外鍵約束：
SQL> alter table tbl_b disable constraint FK_TBL_B_A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                      P ENABLED
FK_TBL_B_A                    R DISABLED

STATUS狀態變為DISABLED了。

truncate表：
SQL> truncate table tbl_a;

Table truncated.

恢複約束：
SQL> alter table tbl_b enable constraint FK_TBL_B_A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                      P ENABLED
FK_TBL_B_A                    R ENABLED

總結：
 
1. 主外鍵是資料庫提供的強約束，可以協助我們控制主子表之間的關係，但同時還是一把雙刃劍，當然，我們認為既然定義了主外鍵，就是需要這種強制關係，但有時可能就會有一些變更，因此，如何取捨，需要根據實際情況來決策。

2. 主外部索引鍵關聯中的主表，如果有資料，則不能直接用truncate方式刪除，因為會認為有外鍵和其關聯，不能直接截斷主表，若需要做，可以先禁止外鍵約束，主表變成一個獨立的表，這樣就可以執行truncate了。