Skip to Main Content

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!

Complex Conditional Unique Constraint

376519Mar 17 2008 — edited Mar 19 2008

I am trying to implement a conditional unique constraint on this table.

 
UID   VALUEA   VALUEB   VALUEC   EFF_DATE   TRM_DATE
  2    105       105      32     3/15/07    3/21/09
  3    142        21      86      3/7/07    7/25/09
  5    105       105      32      2/1/07    3/10/07
  7    142        21      86      3/5/07    6/12/09

The constraint would block UID 7 but not 5.

The technique I am trying to use is to create a unique index based on a function. Much like this one:

 
CREATE UNIQUE INDEX ITEM_U01 ON ITEM
(CASE ITEM_TYPE WHEN 'BOX' THEN SHORT_NAME ELSE NULL END)

This ensures that no items of the type "BOX" have the same name.

ps.
What are the tags to keep my code formatted? I can not remember.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2008
Added on Mar 17 2008
19 comments
1,182 views