Translating with DrWatson… this can take a few seconds the first time.
This is a genuine, complex translation. DrWatson protects commands, error codes, and log output while naturally translating the surrounding text. It’s translated once and saved.
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/
We use strictly necessary cookies to make this site work. With your
consent we’d also use optional cookies for analytics and marketing. You can accept all,
reject all, or choose. Read our Cookie Policy.