I have a case where I need to enforce uniqueness on a combination of columns but only when a flag is set (ACTIVE_FLAG=1). The table also holds history and may have multiple inactive records mixed in but I don't want to keep those from being added to the table.
This can be done by exploiting the fact that null index expressions are not indexed. DECODE and function based index to the rescue!