CODEWITHSUNDEEP
sqloracle-databaseconstraintsunique
BnJ
BnJ

Reputation: 1044

Check if a value appears only once in a column

I want to create a table for managing versions of parameters...

In this table there is a column of type char that lets me know what version I have to use :

create table PARAMETERS_VERSION (
                ID number not null,
                VERSION number not null,
                DATE_START date not null,
                DATE_END date check(DATE_START <= DATE_END) not null
                ISUSED char(1) check(ISUSED in ('Y','N')) not null,
                constraint PARAMETERS_VERSION_VERSION_PK primary key (ID),
                constraint PARAMETERS_VERSION_VERSION_UK unique (ISUSED)
);

How to define a unique constraint on the column ISUSED to have only a single row with the value 'Y' (and the others with 'N') ?

By the way, is my check constraint on DATE_END is correct ?

Upvotes: 0

Views: 381

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Oracle doesn't quite support partial or filtered indexes. Instead, you can use a functional index with some cleverness:

create unique index idx_parametersversion_isused
    on parameters_version(case when is_used = 'Y' then -1 else id end);

That is, when is_used has any value other than Y, then the primary key is used. When it is Y, then a constant is used, so two values will conflict.

Upvotes: 3

Related Questions