Unique constraint with defined value

Question

given the following sample table structure is there a way to add to a unique constraint to insure uniqueness for (GUID, 'Y') combination?

Application logic - update by guid generates a new version with same guid but new luid; and previous goes inactive('Y'->'N')

GUID - external id
LUID - internal id

 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

Kibitzer

Answer 1

You can create a unique function-based index and leverage 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) );

Etan