Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: ak_tiredofspam@yahoo.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: PRAGMA AUTONOMOUS_TRANSACTION in a trigger
Date: 4 Apr 2005 10:51:55 -0700
Organization: http://groups.google.com
Lines: 7
Message-ID: <1112637115.704627.200770@g14g2000cwa.googlegroups.com>
References: <aafea0a8.0504031956.438cdf3@posting.google.com>
   <1112595217.815019@yasure>
   <1112602134.610929.20420@f14g2000cwb.googlegroups.com>
   <1112628317.728082@yasure>
   <1112631257.730137.42420@g14g2000cwa.googlegroups.com>
NNTP-Posting-Host: 205.156.188.254
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1112637120 16961 127.0.0.1 (4 Apr 2005 17:52:00 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 4 Apr 2005 17:52:00 +0000 (UTC)
In-Reply-To: <1112631257.730137.42420@g14g2000cwa.googlegroups.com>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: g14g2000cwa.googlegroups.com; posting-host=205.156.188.254;
   posting-account=GxvDVQwAAACIzoQWbx3Wrb_b4uAQN9nX
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:117928

>> It only needs to be unique when jobtype = 'RES'. Other jobtypes are
allowed to have duplicates. <<

in this case I'd suggest a function based UNIQUE index on
(CASE WHEN jobtype = 'RES' THEN STREETNAME ELSE NULL END,
and so on for 3 other columns