ORA-02266錯誤的批量生成腳本解決方案

ORA-02266: unique/primary keys in table referenced by enabled foreign keys這篇博客是很早之前總結的一篇文章，最近導數時使用TRUNCATE清理主表數據又遇到了這個錯誤，發現還有其它解決方案：

a) 禁用與主表相關的外鍵約束

b) TRUNCATE TABLE

c) 啟用那些外鍵約束。

在實際操作中，發現使用上面的流程操作雖然正確，但是要寫很多腳本，有些主表中的欄位可能是多個表的外鍵約束。那麼我們必須寫多個腳本，那麼我們必須使用腳本批量生成。

快速解決問題，具體如下所示：。

--生成禁用約束的腳本,解決ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME      AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER           AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" ,

       'ALTER TABLE ' || DF.OWNER  || '.' || DF.TABLE_NAME || ' DISABLE CONSTRAINT ' || DF.CONSTRAINT_NAME || ';'

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

               C.CONSTRAINT_NAME, 

               C.R_CONSTRAINT_NAME, 

               C.TABLE_NAME 

        FROM   DBA_CONSTRAINTS C 

        WHERE  CONSTRAINT_TYPE = 'R') DF 

WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME 

       AND DC.OWNER =UPPER('&OWNER')

       AND DC.TABLE_NAME=UPPER('&TABLE_NAME');

--生成啟用約束的腳本,解決ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME      AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER           AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" ,

       'ALTER TABLE ' || DF.OWNER  || '.' || DF.TABLE_NAME || ' ENABLE CONSTRAINT ' || DF.CONSTRAINT_NAME || ';'

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

               C.CONSTRAINT_NAME, 

               C.R_CONSTRAINT_NAME, 

               C.TABLE_NAME 

        FROM   DBA_CONSTRAINTS C 

        WHERE  CONSTRAINT_TYPE = 'R') DF 

WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME 

       AND DC.OWNER =UPPER('&OWNER')

       AND DC.TABLE_NAME=UPPER('&TABLE_NAME');

原因分析：對於由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，因為TRUNCATE不會觸發觸發器，不會去驗證任何約束。所以語法上是不允許的：有外鍵約束的表只能用DELETE刪除數據，不能用TRUNCATE刪除數據。

You cannot truncate a table with an enabled foreign key that points to it. Truncate does not fire any triggers, does not validate any constraints. It does not care of the child table is empty or not -- in this case the child table might actually not be empty.