How create unique constraint on table based on two column value-CodePudding

Select
      1 as TYPE_ID,
      0 as STATUS
from
     dual  --TEST_TBL
union all
Select
      1 as TYPE_ID,
      0 as STATUS
from
     dual --TEST_TBL
     union all
Select
      1 as TYPE_ID,
      1 as STATUS
from
     dual  --TEST_TBL

Status = 1 is Active, 0 is Inactive

Not allow two row TYPE_ID = 1 and STATUS = 1 or TYPE_ID = 2 and STATUS = 1

CodePudding user response：

You can use a unique function-based index:

create unique index test_table_ix
on test_table (case when status = 1 then type_id end)

Only non-null values are included in an index, so the case expression evaluates to null for status 0 - meaning those rows aren't indexed - and the type_id for status 1 - so those are. That means 1,0 isn't indexed, but 1,1 is - and then has to be unique.

fiddle