Turning off constraint checking
Robert Hale asked for the Informix 7.x command to turn off constraint checking while loading a large data set. Jonathan Leffler and Nebojsa pointed to SET CONSTRAINTS ALL DEFERRED (or by constraint name). Rudy Fernandes said there is no database-level command but SET INDEXES, CONSTRAINTS FOR table DISABLED works per table; referencing foreign keys must be disabled before a primary key.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
HI, a couple of years ago, I had to load a large data set into an Informix 7.? database. At the time, there was a command that could be used to turn all constraint checking off for the entire database or a set of tables. I've checked my notes, but cannot find the syntax for this command anywhere. Does anyone remember off hand what the command is to turn constraint checking off for a table?
"Hale, Robert [CREST:5K46:EXCH]" wrote: > a couple of years ago, I had to load a large data set into an Informix > 7.? database. At the time, there was a command that could be used to > turn all constraint checking off for the entire database or a set of > tables. I've checked my notes, but cannot find the syntax for this > command anywhere. Does anyone remember off hand what the command is to > turn constraint checking off for a table? RTFM? SET CONSTRAINTS ALL DEFERRED? I do note that the 9.1 Informix Guide to SQL: Syntax manual does not mention deferred or immediate constraints under CONSTRAINT in the index, which makes it harder to find than is desirable, so I've copied doc@informix.com on this message. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
On Wed, 30 Aug 2000 15:42:57 -0700, "Hale, Robert [CREST:5K46:EXCH]" <rhale@americasm05.nt.com> wrote: >HI, > a couple of years ago, I had to load a large data set into an Informix >7.? database. At the time, there was a command that could be used to >turn all constraint checking off for the entire database or a set of >tables. I've checked my notes, but cannot find the syntax for this >command anywhere. Does anyone remember off hand what the command is to >turn constraint checking off for a table? SET CONSTRAINTS constr_name DEFERRED or SET CONSTRAINTS ALL DEFERRED So, if you want to "turn off" constraints for one table you have to know contraint's names on that table. Nebojsa
"Hale, Robert [CREST:5K46:EXCH]" wrote: > HI, > a couple of years ago, I had to load a large data set into an Informix > 7.? database. At the time, there was a command that could be used to > turn all constraint checking off for the entire database or a set of > tables. I've checked my notes, but cannot find the syntax for this > command anywhere. Does anyone remember off hand what the command is to > turn constraint checking off for a table? There isn't any command to do it at the Database level. However, you can disable constraints and indexes at the table level (probably what you need) using the command SET INDEXES, CONSTRAINTS FOR <table> DISABLED; Note : you will not be able to disable the PK of a table if it is still being referenced - you need to disable all FK referring to it first. Rudy