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

TIA,
Zev Berezin
sending to informix-list

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