check constraints

[Laurie Gustin]

Is there an easy to to create a constraint on one column, based on the
value in another column Both fields are in the same table. Specifically,
I have two columns and they are mutually exclusive.

If col1 is null then col2 is not null or if col1 is not null then col2 is
null
and both columns cannot be null

I was hoping to do this with a check constraint - if not, I may have to use
a trigger.

Any help is appreciated :)

Laurie

IDS 11.7FC7 and IDS 11.5FC9

[Art Kagel]

You will need both an insert trigger on the table and an update trigger on
those two columns.

Just a suggestion, since these columns are mutually exclusive (exactly one
can contain a value), if their types are compatible, have you considered
making them a single NOT NULL column with a single character flag
indicating which value is in there? Much simpler to implement. On select
you can simulate two columns with:

select ... CASE WHEN flagcol = 0 THEN NULL ELSE valuecol END AS first_val,
CASE WHEN flagcol = 1 THEN NULL ELSE valuecol END AS second_val, ...

This can be saved as a VIEW with the tablename that users expect while
under the hood, the simpler structure is what is in the actual table
underlying the VIEW which has a different name.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

Post by Laurie Gustin
Is there an easy to to create a constraint on one column, based on the
value in another column Both fields are in the same table. Specifically,
I have two columns and they are mutually exclusive.
If col1 is null then col2 is not null or if col1 is not null then col2
is null
and both columns cannot be null
I was hoping to do this with a check constraint - if not, I may have to
use a trigger.
Any help is appreciated :)
Laurie
IDS 11.7FC7 and IDS 11.5FC9
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list