Turning off constraint checking 
Author Message
 Turning off constraint checking
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?


Wed, 18 Jun 1902 08:00:00 GMT
 Turning off constraint checking

Quote:

>   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

--
Yours,

Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"



Wed, 18 Jun 1902 08:00:00 GMT
 Turning off constraint checking
On Wed, 30 Aug 2000 15:42:57 -0700, "Hale, Robert [CREST:5K46:EXCH]"

Quote:

>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



Wed, 18 Jun 1902 08:00:00 GMT
 Turning off constraint checking

SET CONSTRAINTS DISABLED;
SET INDEXES DISABLED;
?


Quote:

>   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?

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.



Wed, 18 Jun 1902 08:00:00 GMT
 Turning off constraint checking


Quote:

>SET CONSTRAINTS DISABLED;
>SET INDEXES DISABLED;
>?

Just to clarify, these both need a list of indexes and constraints you want
to defer.
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.



Wed, 18 Jun 1902 08:00:00 GMT
 Turning off constraint checking

Quote:

> 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



Mon, 17 Feb 2003 19:24:51 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Turn off constraint checking using bcp to load

2. Turn off constraints

3. Foreign keys: how to turn referential integrity constraint off

4. Turn On/Off Foreign Key Constraints?

5. turn off dbcc check

6. Column check constraint vs table check constraint

7. Change table check constraint to column check constraint?

8. Turn it off, turn it on

9. way to turn off all constraint/trigger checking during file transfer?

10. Check Constraint vs. Foreign Key Constraint

11. checking the violation of constraint with existing data (after enabling of the constraint)

12. check constraint or referential constraint


 
Powered by phpBB® Forum Software