Re: constraint to allow multiple nulls

Zev Berezin wrote: > Correct. Thank you. That is a clever way of handling it. > I was looking for a "pure" constraint to be handled without any > coding. It seems that it will be coded in 4GL. > > Thanks, > Zev Berezin > > From: "Art S. Kagel" <kagel@bloomberg.net> > To: informix-list@iiug.org > Subject: Re: constraint to allow multiple nulls > Date sent: Thu, 04 Aug 2005 12:44:33 -0400 > Send reply to: "Art S. Kagel" <kagel@bloomberg.net> > >>Zev Berezin wrote: >>> I have a column that can only have one 'Y' value >>>and multiple NULL values. An unique constraint allows one null value >>>and a PK allows none. Is there any constraint that would >>>enforce this? >>> >>>Tech Spec: IDS 7.31.FD7 >> >>So to expand: You have a table with a CHAR column that is either 'Y' or NULL >>and only one row can contain 'Y' at a time. Correct? >> >>So, you need a check constraint to restrict the possible non-null values to >>'Y' only and INSERT and UPDATE triggers to validate that if the current row >>is being inserted/updated to contain 'Y' that there are no other rows >>already containing 'Y'. The update trigger can do the check in an AFTER >>clause since the UPDATE could be updating the current 'Y' to not 'Y' and >>some other row to 'Y' in the same statement. One of the DBMS out there has a UNIQUE UNLESS NULL index or constraint mechanism. That is: -- dubious syntax; I hope the meaning is clear enough CREATE TABLE x(y INTEGER UNIQUE UNLESS NULL); INSERT INTO x VALUES(1); -- OK INSERT INTO x VALUES(NULL); -- OK INSERT INTO x VALUES(NULL); -- Also OK INSERT INTO x VALUES(1); -- Fails on constraint violation. No, IDS does not support any variant of this. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/