Re: Disable Constraints

On Fri, Aug 15, 2008 at 7:42 PM, MICHELLE KELLEY <shannon_csis@hotmail.com> wrote: > I'm glad this site exists, it's about the only one left on Informix, lol. Not quite - and in particular, the ids@iiug.org forum would be more appropriate for technical questions such as this. > Anyhow, If a certain condition is true, I have to disable constraints, change > a bunch of data and the enable the constraints. > > I can successfully disable the constraints using: > SET CONSTRAINTS constraint_name DISABLED; > > And enable them the same way. > > Something is making me feel a bit uneasy though. I noticed that if I set the > state of the object in the sysobjstate table to 'D' (for disabled) it doesn't > actually disable the constraint so the restrictions are still there. So the > issue here is then when I go to re-enable the constraints I was hoping to be > able to look at the sysobjstate table to see what constraints are disabled and > enable them ... but as I have found the table could be lieing to me and having > a state of 'D' doesn't 100% mean it's disabled. > > How do I know for sure if the constraint is disabled? I have tables that could > have millions of entries each so messing up here would be bad. Please tell us which version of IDS and which platform you are using. These could be decisive. Why do you think that the constraint is not disabled even if sysobjstate says 'D'? Can you illustrate your issues? My test:- CREATE TABLE elements ( atomic_number INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements CHECK (atomic_number > 0 AND atomic_number < 120), symbol CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements, name CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements, atomic_weight DECIMAL(8,4) NOT NULL, stable CHAR(1) DEFAULT 'Y' NOT NULL CHECK (stable IN ('Y', 'N')) ); It happens to be tabid = 101. The constraints are: Black JL: sqlcmd -d stores SQL[2970]: select * from sysconstraints where tabid = 101; 4|c1_elements|jleffler|101|U| 101_4|en_US.819 5|c2_elements|jleffler|101|U| 101_5|en_US.819 6|c3_elements|jleffler|101|U| 101_6|en_US.819 7|n101_7|jleffler|101|N||en_US.819 8|c101_8|jleffler|101|C||en_US.819 9|n101_9|jleffler|101|N||en_US.819 10|n101_10|jleffler|101|N||en_US.819 11|n101_11|jleffler|101|N||en_US.819 12|n101_12|jleffler|101|N||en_US.819 13|c101_13|jleffler|101|C||en_US.819 SQL[2971]: select * from syscoldepend where tabid = 101; 7|101|1 8|101|1 9|101|2 10|101|3 11|101|4 12|101|5 13|101|5 SQL[2972]: select * from sysobjstate where tabid = 101; I|jleffler| 101_4|101|E C|jleffler|c1_elements|101|E I|jleffler| 101_5|101|E C|jleffler|c2_elements|101|E I|jleffler| 101_6|101|E C|jleffler|c3_elements|101|E C|jleffler|n101_7|101|E C|jleffler|c101_8|101|E C|jleffler|n101_9|101|D C|jleffler|n101_10|101|E C|jleffler|n101_11|101|E C|jleffler|n101_12|101|E C|jleffler|c101_13|101|E SQL[2973]: select * from elements where atomic_number = 113; 113||myth|234.2300|N SQL[2974]: set constraints n101_9 enabled; SQL -530: Check constraint (n101_9) failed. SQLSTATE: 23000 at /dev/stdin:5 SQL[2975]: delete elements where atomic_number = 113; SQL[2976]: set constraints n101_9 enabled; SQL[2977]: insert into elements values(113,null,'myth',234.23,'N'); SQL -391: Cannot insert a null into column (elements.symbol). SQLSTATE: 23000 at /dev/stdin:8 SQL[2978]: q; Black JL: Basically, this shows that constraint n101_9 is the non null constraint on symbol. With the constraint disabled, I was able to insert a row with atomic_number = 113 and a null symbol. I was not able to re-enable the constraint because of that row. Removing the row, I was able to enable the constraint and was not able to re-insert a row with a null sybol. This was tested on IDS 11.50.FC1 on Solaris 10. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.