Unique setpoint limitation

Question

Given the following structure of the sample table, is there a way to add to the unique constraint to ensure that the combination is unique (GUID, 'Y')?

Application logic - updating guid creates a new version with the same guid but new luid ; and the previous one is inactive ('Y' → 'N')

GUID - external identifier
LUID - internal identifier

  create table id_active( "GUID" RAW(16) NOT NULL, "LUID" RAW(16) NOT NULL, "IS_ACTIVE" char(1) NOT NULL CHECK ( "IS_ACTIVE" IN ('Y', 'N')), PRIMARY KEY ("GUID", "LUID"), --unique constraint goes here 

Answer 1

You can create a unique index based on functions and use the fact that Oracle does not index NULL values ​​in b-tree indexes.

 CREATE UNIQUE INDEX one_active_guid ON table_name( (CASE WHEN is_active = 'Y' THEN guid ELSE null END) );