Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unique constraint with conditions?

723980Sep 23 2009 — edited Sep 23 2009
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!
This post has been answered by 465815 on Sep 23 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2009
Added on Sep 23 2009
#trigger
4 comments
251 views