How to enable and disable Oracle and DB2 foreign key constraints)

Transferred from: http://huangqiqing123.iteye.com/blog/1234817what is the start and suspension of external key constraints of oracle&db2data warehouse

Blog type:
• DB2
• Oracle

At work, you sometimes need to import data from a database to another database. Due to the influence of foreign keysInsertMake sure that the data in the parent table is inserted first, and then the data in the child table is inserted. Because of the complexity of the database table relationship in a project, it is not easy to clear the relationship between the primary and Foreign keys and arrange the order. It is necessary to temporarily disable the foreign key constraint.APIDuring the test, some basic data needs to be inserted into the database. This problem is often encountered.OracleAndDB2How to disable foreign key constraints in a database.

I,OracleDatabase:

Disable constraint Syntax:

ALTER TABLEDatabase Table NameDisable ConstraintConstraint name

 

If you need to disablePub_organForeign key constraints:

1,First QueryPub_organWhich foreign key constraints are required at this time?OracleDictionary tableUser_constraints.

Select * From user_constraints where table_name = 'pub _ organ ';

 

Is the query result. The meaning of each field is as follows:

Owner:Table owner

Constraint_name:Constraint name

Constraint_type:Constraint type (RForeign key,PRepresents the primary key,CRepresentativeCheckConstraints)

Table_name:Table Name

Search_condition: CheckConstraints

Status: EnabledIndicates that the current constraint is enabled,DisabledIndicates that the current constraint is not enabled.

2,After querying which constraints exist in the table, you can useAlterThe specified constraint is enabled or disabled.

If disabledPub_organForeign key of the tablePuborgan_fk1, You can use the following command:

Alter table pub_organ disable ConstraintPuborgan_fk1;

Query the dictionary table againUser_constraints, As follows:

 

To the database tablePub_organWhen data is inserted, it is no longer affected by the foreign key constraint.

 

Enable constraint Syntax:

ALTER TABLEDatabase Table NameEnable ConstraintConstraint name

 

To enable it againPub_organTo use the following command:

Alter table pub_organ enable ConstraintPuborgan_fk1;

II,DB2Database:

Disable constraint Syntax:

Alter table table nameAlter foreign key constraint name not enforced

 

Enable constraint Syntax:

Alter table table nameAlter foreign key constraint name enforced

 

Related dictionary table:Sysibm. systabconst

For example:Select * From sysibm. commanabconst where tbname = 'pub _ organ ';

 

The meanings of each field are as follows:

Name:Constraint name

Definer:Definer

Constrainttyp:Constraint type (PRepresents the primary key,FRepresents the foreign key)

Tbname:Table Name

Enforced:Whether to enable (YIndicates enabled,NIndicates that it is not enabled)

3. encapsulatedJavaInterface and batch execution

In practice, several tables or foreign keys of all database tables are often disabled. In this case, you need to execute commands in batches.JavaEncapsulates related interfaces for ease of use.

The interface for external exposure is as follows:

/* *Enable all foreign key constraints specified by the current user for tablename *Variable parameters for input parameters (New Features of JDK 5) *Call method: * 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 ); } /* *Disable all foreign key constraints specified by the current user for tablename */ Public Static Void Disablefk (string... tablenames ){ Disableorenbalefk(False, Tablenames ); } /* *Enable foreign key constraints for all tables of the current user */ Public Static VoidEnableallfk (){ Disableorenableallconstraint(True); } /* *Disable foreign key constraints for all tables of the current user */ Public Static VoidDisableallfk (){ Disableorenableallconstraint(False); }

 

Core ProcessingCodeAs follows:

If(Tablenames =Null| Tablenames. Length = 0 ){ Throw NewRuntimeexception ("the input parameter tablenames cannot be blank! "); } // Query the foreign key constraints of the specified table 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 enabled' from sysibm. using abconst where constraintyp = 'F' and tbname in ("; If(Isenable ){ SQL = SQL. Replace ("Not enforced", "enforced "); } }Else{ Throw NewRuntimeexception ("the database type is invalid (only oracle and DB2 are supported), dbtype =" + dbtype ); } Stringbuffer generatesql =NewStringbuffer (SQL ); For(IntI = 0; I <tablenames. length; I ++ ){ Generatesql. append ("'"); Generatesql. append (tablenames [I]. touppercase ());// Note that it must be converted to uppercase Generatesql. append ("',"); } Generatesql. deletecharat (generatesql. Length ()-1 ); Generatesql. append (")"); List <Map <string, Object> dataset = dbtool.Executequery(Generatesql. tostring ()); // Enable or disable the foreign key constraint For(IntI = 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); } }

 

This article ends now!