如何啟用和禁用oracle&DB2資料庫外鍵約束)

轉自:http://huangqiqing123.iteye.com/blog/1234817如何啟用和禁用oracle&DB2資料庫外鍵約束

部落格分類：
• db2
• oracle

在工作中，有時會需要把一個資料庫中的資料匯入到另外一個資料庫，由於外鍵的影響，在執行insert語句時，需要確保先插入父表中的資料，然後插入子表中的資料，由於一個項目中，資料庫表關係錯綜複雜，縷清主外鍵關係並排列好順序實非易事，此時暫時禁用外鍵約束變得非常的必要，另外，筆者在日常的API測試時，需要向資料庫中插入部分基礎資料，經常碰到此問題，特總結了Oracle和DB2資料庫下如何禁用外鍵約束的方法。

一、Oracle資料庫：

禁用約束基本文法：

alter table 資料庫表名 disable  constraint 約束名

 

假設現在需要關閉pub_organ的外鍵約束：

1、 首先查詢pub_organ存在哪些外鍵約束，此時需要用到oracle的字典表user_constraints。

select * from user_constraints where table_name='PUB_ORGAN';

 

就是查詢結果，其中各欄位含義如下：

OWNER: 表的所有者

CONSTRAINT_NAME: 約束名稱

CONSTRAINT_TYPE: 約束類型（R代表外鍵，P代表主鍵，C代表check約束）

TABLE_NAME: 表名稱

SEARCH_CONDITION: check約束的具體資訊

STATUS: ENABLED表示當前約束是啟用的，DISABLED表示當前約束未啟用。

2、 查詢出表存在哪些約束後，即可以通過alter語句啟用或禁用指定的約束了。

如禁用pub_organ表的外鍵PUBORGAN_FK1，則可以使用如下命令實現：

alter table PUB_ORGAN disable constraint   PUBORGAN_FK1;

執行後，重新查詢字典表user_constraints，如下：

 

此時往資料庫表pub_organ中插入資料時就不再受外鍵約束的影響了。

 

啟用約束基本文法：

alter table 資料庫表名 enable constraint 約束名

 

如現在需要重新啟用pub_organ的外鍵約束，可以使用如下命令：

alter table PUB_ORGAN  enable constraint   PUBORGAN_FK1;

二、DB2資料庫：

禁用約束基本文法：

ALTER TABLE 表名稱 ALTER FOREIGN KEY 約束名稱 NOT ENFORCED

 

啟用約束基本文法：

ALTER TABLE 表名稱 ALTER FOREIGN KEY 約束名稱  ENFORCED

 

相關字典表：SYSIBM.SYSTABCONST

如：select * from SYSIBM.SYSTABCONST where tbname='PUB_ORGAN';

 

各欄位含義如下：

NAME: 約束名稱

DEFINER: 定義者

CONSTRAINTTYP: 約束類型（P代表主鍵，F代表外鍵）

TBNAME: 表名稱

ENFORCED: 是否啟用（Y代表啟用，N代表未啟用）

三、封裝成java介面、批量執行

在實際工作中，經常會將若干個表，或者所有資料庫表的外鍵一起禁用，此時需要批量執行相關命令，筆者根據工作實際，使用java封裝了相關介面，以方便使用。

對外暴露介面如下：

/*      * 啟用目前使用者指定tableName的所有外鍵約束      * 入參使用可變參數（jdk5新特性）      * 調用方式：      * 1、enableFK("pub_organ")      * 2、enableFK("pub_organ","pub_stru")      * 3、enableFK(new String[]{"pub_organ","pub_stru"})      */     public static void enableFK(String...tableNames){        disableORenbaleFK(true,tableNames);     } /*      * 禁用目前使用者指定tableName的所有外鍵約束      */     public static void disableFK(String...tableNames){        disableORenbaleFK(false,tableNames);     } /*      * 啟用目前使用者所有表的外鍵約束      */     public static void enableAllFK(){        disableORenableAllConstraint(true);     }     /*      * 禁用目前使用者所有表的外鍵約束      */     public static void disableAllFK(){        disableORenableAllConstraint(false);     }

 

其中核心處理代碼如下：

if(tableNames==null||tableNames.length==0){            throw new RuntimeException("入參tableNames不可為空！");        }        //查詢指定表的外鍵約束        String sql = null;        String dbType = getDBType();        if(dbType.contains("ORACLE")){            sql = "select 'alter table ' || table_name || ' disable constraint ' || constraint_name from user_constraints where constraint_type='R' and TABLE_NAME in(";            if(isEnable){               sql = sql.replace("disable", "enable");            }             }else if(dbType.contains("DB2")){            sql = "select 'ALTER TABLE ' || TBNAME || ' ALTER FOREIGN KEY ' || NAME ||' NOT ENFORCED ' FROM SYSIBM.SYSTABCONST WHERE CONSTRAINTYP='F' and TBNAME in(";            if(isEnable){               sql = sql.replace("NOT ENFORCED", "ENFORCED");            }             }else{            throw new RuntimeException("資料庫類型無效(僅支援Oracle和DB2)，dbType="+dbType);        }          StringBuffer generateSQL = new StringBuffer(sql);        for(int i=0;i<tableNames.length;i++){             generateSQL.append(" '");            generateSQL.append(tableNames[i].toUpperCase());//注意須轉換成大寫            generateSQL.append("',");        }        generateSQL.deleteCharAt(generateSQL.length()-1);        generateSQL.append(")");        List<Map<String, Object>> dataSet = DBTool.executeQuery(generateSQL.toString());               //啟用or停止查詢出的外鍵約束        for(int i=0;i<dataSet.size();i++){            Map<String, Object> record = dataSet.get(i);             Iterator<Entry<String, Object>> itor = record.entrySet().iterator();                  while(itor.hasNext())                  {                     Entry<String, Object> e = itor.next();                   DBTool.executeUpdate(e.getValue().toString(),UpdateType.ALTER);                }                  }

 

本文到此結束！