I think in my message what I remember now was that we had the concept of "trusted loads". In those cases I thought there was a way to disable constraint checking to avoid a big penalty on performance. But sometimes I get confused about what customers asked for and what we had implemented (old age 😕). For bulk loads it is probably more efficient to drop the constraint, load, and then add the constraint again. For incremental updates that becomes hard to do - just validating the new data.
Rohit On Jun 19, 2017, at 3:32 PM, Anoop Sharma <[email protected]<mailto:[email protected]>> wrote: when a constraint (check, unique or RI) is added to a table with existing data, then that constraint is validated against existing data. An error is returned and constraint creation fails if data validation fails. For check constr failure, this error is returned: >>alter table tc add constraint tc1 check (a < 10); *** ERROR[1083] Validation for constraint TRAFODION.SCH.TC1 failed; incompatible data exists in table. --- SQL operation failed with errors. >> anoop From: Eric Owhadi [mailto:[email protected]] Sent: Monday, June 19, 2017 12:26 PM To: [email protected]<mailto:[email protected]> Subject: RE: is there a way to disable a specific check constraint without dropping it? I don’t believe constraints are checked against existing data. Only new data upserted are being validated. Am I correct? Meaning if I add constraint on existing data, there is no complaining about existing data violating constraints? Eric From: Rohit Jain [mailto:[email protected]] Sent: Monday, June 19, 2017 2:19 PM To: [email protected]<mailto:[email protected]> Subject: Re: is there a way to disable a specific check constraint without dropping it? Right, because re-enabling will have to recheck the constraint anyway. Rohit On Jun 19, 2017, at 1:26 PM, Eric Owhadi <[email protected]<mailto:[email protected]>> wrote: Hi Dave, It was a trick I was trying to use to store sql statement associated with a table as a check constraint, without enforcing it. I now know I can use the _MD_.TEXT for this purpose, so no worries about this feature anymore. Thanks, Eric From: Dave Birdsall [mailto:[email protected]] Sent: Monday, June 19, 2017 11:22 AM To: [email protected]<mailto:[email protected]> Subject: RE: is there a way to disable a specific check constraint without dropping it? Hi Eric, There doesn’t appear to be. I noticed the SQL parser does have such syntax, but the productions return an error if invoked. So someone thought about implementing ALTER TABLE <table> DISABLE ALL CONSTRAINTS at one point but did not complete it. Just curious: What’s the use case? Why does dropping and then recreating not work? Dave From: Eric Owhadi [mailto:[email protected]] Sent: Friday, June 16, 2017 8:35 AM To: [email protected]<mailto:[email protected]> Subject: is there a way to disable a specific check constraint without dropping it? Hi Trafodioneers, I am wondering if there is a way to disable check constraint without dropping them? Is there? Thanks in advance for the help, Eric
