Generating the referential constraints order from database

Hello all, We have referential integrity turned ON in some of the databases. I tried to find out the sequence from the database so that the customer can load the data without turning OFF the constraints and which makes our life easier too. Referred the sysconstraints, sysrefernces, sysobjects but i am not able to come to a solution. Also whether any body had a problem in implementing recursive relations constraints pointing to the same table) Thanks in advance Dhanesh

Dhanesh Kottile Veedu wrote: > > Hello all, > > We have referential integrity turned ON in some of the databases. > I tried to find out the sequence from the database so that the customer can > load the data without turning OFF the constraints and which makes our life > easier too. Referred the sysconstraints, sysrefernces, sysobjects but i am > not able to come to a solution. > > Also whether any body had a problem in implementing recursive relations > constraints pointing to the same table) If you have logging on your database you can defer constraint checking until the COMMIT or ROLLBACK is executed which solves the problem and will let you add rows to the relation in whatever order is natural to the application. Thus in your app before any transaction or at the database level if you want deffered checking to be the default: SET CONSTRAINTS ALL DEFERRED; -or- SET CONSTRAINTS constr_1, constr_2, ... DEFERRED; You can either leave that as the database default or change it back at the end of the application: SET CONSTRAINTS ALL IMMEDIATE; This of course presumes that you are using explicit transactions with BEGIN WORK (if not ANSI Mode) and COMMIT WORK. Art S. Kagel

In article <37430175.4161@bloomberg.net>, kagel@bloomberg.net wrote: > Dhanesh Kottile Veedu wrote: > > > > Hello all, > > > > We have referential integrity turned ON in some of the databases. > > I tried to find out the sequence from the database so that the customer can > > load the data without turning OFF the constraints and which makes our life > > easier too. Referred the sysconstraints, sysrefernces, sysobjects but i am > > not able to come to a solution. > > > > Also whether any body had a problem in implementing recursive relations > > constraints pointing to the same table) > > If you have logging on your database you can defer constraint checking > until the COMMIT or ROLLBACK is executed which solves the problem and > will let you add rows to the relation in whatever order is natural to > the application. Thus in your app before any transaction or at the > database level if you want deffered checking to be the default: > > SET CONSTRAINTS ALL DEFERRED; > > -or- > > SET CONSTRAINTS constr_1, constr_2, ... DEFERRED; > > You can either leave that as the database default or change it back at > the end of the application: > > SET CONSTRAINTS ALL IMMEDIATE; > > This of course presumes that you are using explicit transactions with > BEGIN WORK (if not ANSI Mode) and COMMIT WORK. > > Art S. Kagel > You can't defer constraints as default, ie. you cannot execute the set constraints statement outside transactions; you have to execute it in every transaction where you want to have it -- at least in my database, 7.30. -- Gabor Heppes IBM Global Services gaborh@au1.ibm.com --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---

Gabor is correct! We can DEFER the constraint checking to the end of a transaction, meaning we must decide that INSIDE a transaction. Constraint checking will revert to the default (IMMEDIATE) as soon as the transaction ends. You can, however, change to DETACHED constraint checking whenever you want, and keep the setting througout your session, even across multiple transactions. HTH Gabor Heppes wrote: > > In article <37430175.4161@bloomberg.net>, > kagel@bloomberg.net wrote: > > Dhanesh Kottile Veedu wrote: > > > > > > Hello all, > > > > > > We have referential integrity turned ON in some of the databases. > > > I tried to find out the sequence from the database so that the > customer can > > > load the data without turning OFF the constraints and which makes > our life > > > easier too. Referred the sysconstraints, sysrefernces, sysobjects > but i am > > > not able to come to a solution. > > > > > > Also whether any body had a problem in implementing recursive > relations > > > constraints pointing to the same table) > > > > If you have logging on your database you can defer constraint > checking > > until the COMMIT or ROLLBACK is executed which solves the problem and > > will let you add rows to the relation in whatever order is natural to > > the application. Thus in your app before any transaction or at the > > database level if you want deffered checking to be the default: > > > > SET CONSTRAINTS ALL DEFERRED; > > > > -or- > > > > SET CONSTRAINTS constr_1, constr_2, ... DEFERRED; > > > > You can either leave that as the database default or change it back > at > > the end of the application: > > > > SET CONSTRAINTS ALL IMMEDIATE; > > > > This of course presumes that you are using explicit transactions with > > BEGIN WORK (if not ANSI Mode) and COMMIT WORK. > > > > Art S. Kagel > > > > You can't defer constraints as default, ie. you cannot execute the > set constraints statement outside transactions; you have to execute > it in every transaction where you want to have it -- at least in my > database, 7.30. > > -- > Gabor Heppes > IBM Global Services > gaborh@au1.ibm.com > > --== Sent via Deja.com http://www.deja.com/ ==-- > ---Share what you know. Learn what you don't.--- -- __________________________________________________________ Paulo Silva mailto:psilva@informix.com Consultant/Trainer phone : +351 1 412 89 40 Informix Software Portugal fax : +351 1 410 84 37