Re: Constraints based on column values

not with a constraint , but with a unique function based index it is easy

create unique index gaga on t ( case role when 1 then id2 end )

( the implicit else case is null, so not indexed at all )

"DL" <dimitris_at_cs.umb.edu> schrieb im Newsbeitrag news:4bc2752b.0304300801.1588baf_at_posting.google.com...
> I would like to check if there is a standard way in
> Oracle to set the following type of uniq constraint.
> Assuming that we have a table with columns (ID1, ID2 and ROLE)
> where ROLE can have values (1 and 0) can we set up a constraint
> that allows only a single row to have ROLE=1 for a given
> ID2 value (but there could exist several records with ROLE=0 and
> and the same ID2 value)
>
> In theory to enforce this view a separate table should be
> created containing the ID1,ID2 values for ROLE=1, but in
> my environment they want to keep the info in a single table.
>
> Thanks,
>
> Dimitris.
Received on Wed Apr 30 2003 - 15:22:16 CDT