Constraints based on column values

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 - 11:01:30 CDT