Search code examples
oracleunique-constraint

Unique constraint where only one row can be 'Yes' (but based on values in other columns)

I have a database for storing logo images. There can be several different types of logo images. There are also multiple systems which isolate these logos from each other.

The rule is that there can only be one active logo of each type in each system.

I'm trying to make a constraint for this. Here's what I have right now:

alter table logo_t
add constraint logo_active_uk
unique (
  SystemId,
  LogoType,
  Active
) using index tablespace t_indexes;

This is close, but the problem is that it only allows one logo to be inactive at any time. So basically, I need to ignore any inactive logos (active = 'No') in the constraint.

It seems like this should be possible, but I'm not really sure how to fix this.

Solution

  • I found the solution!

    create unique index logo_active_uk
on logo_t(
  (case when Active = 'Yes' then SystemId || LogoType || Active else null end)
) tablespace t_indexes;