Re: constraint to allow multiple nulls

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: > > Hi All, > > > > 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. > > Art S. Kagel > sending to informix-list