Forum Stats

  • 3,839,996 Users
  • 2,262,557 Discussions


Unique constraint with conditions?

723980 Member Posts: 10
edited Sep 23, 2009 6:46AM in SQL & PL/SQL
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:

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!

Best Answer


  • 465815
    465815 Member Posts: 544
    Answer ✓
    try this:
    create unique index a1 on tab(case when STATUS = 'Y' then SERIAL_NUMBER else null end);
  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    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)
    Dom Brooks
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    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
    723980 Member Posts: 10
    Thanks guys, worked like a charm...
This discussion has been closed.