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.