constraint to allow multiple nulls

Posted in 2005

Topics: Triggers, Constraints & Referential Integrity, Versions, Editions & End-of-Life

Zev Berezin — Wed, 03 Aug 2005 17:17:34 -0400 — source: Usenet: comp.databases.informix

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

↪ replying to Zev Berezin

Art S. Kagel — Thu, 04 Aug 2005 12:44:33 -0400 — source: Usenet: comp.databases.informix

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

Glossary

IDS (Informix Dynamic Server)