Unique constraint with conditions?

723980

Hi all!

I have the following scenario... I need column SERIAL_NUMBER to be UNIQUE constrained but only when column STATUS = 'Y'. SERIAL_NUMBER can be non-unique when STATUS has any other value or is null. So I could en with this:

SERIAL_NUMBER | STATUS
--------------------------------------
123456789 N
123456789 N
123456789 Y

but a new row with 123456789 and 'Y' should not be allowed.

Can this be done with a UNIQUE constraint or do I have to program it in a Trigger? I've been toying with the UNIQUE constraint for a while, but neither DECODE nor WHERE clauses seem to be accepted...

Thanks in advance!

465815

try this:

create unique index a1 on tab(case when STATUS = 'Y' then SERIAL_NUMBER else null end);

Amiel

465815

try this:

create unique index a1 on tab(case when STATUS = 'Y' then SERIAL_NUMBER else null end);

Amiel

Dom Brooks

You need a unique index which has entries for serial_number only when the status is Y. If the status != 'Y' then the expression is null and there is no index entry. E.g.

CREATE UNIQUE INDEX <index_name> ON <your_table> (CASE WHEN status = 'Y' THEN serial_number ELSE NULL END)

Boneist

A function based index will do what you want:

SQL> create table test (col1 number, status varchar2(1));

Table created.

SQL> create unique index fred on test (decode(status, 'Y', col1), decode(status, 'Y', status));

Index created.

SQL> insert into test values (1, 'Y');

1 row created.

SQL> insert into test values (1, 'N');

1 row created.

SQL> insert into test values (1, 'Y');

insert into test values (1, 'Y')
Error at line 9
ORA-00001: unique constraint (BROIL_FEED_RATES.FRED) violated

SQL> insert into test values (1, NULL);

1 row created.

SQL> insert into test values (1, 'N');

1 row created.

723980

Thanks guys, worked like a charm...