Re: conditional unique index to restrict duplicates of a particular set

Coming to this thread late, my apologies...

I'd suggest that the problem be resolved by the addition of another column, perhaps a DATE/TIMESTAMP column named TSTAMP, to the present unique-key combination of columns, populated using a BEFORE INSERT trigger with the value of TO_DATE('01-JAN-2000','DD-MON-YYYY') or TO_TIMESTAMP('01-JAN-2000','DD-MON-YYYY') if the FEED_TYPE IN ('I','E'), otherwise if FEED_TYPE = 'P' then populate the column with SYSDATE or SYSTIMESTAMP, which ever is appropriate. Then, create a unique constraint for the previous set of columns plus the new TSTAMP column.

When something gets too complicated, then you're starting from the wrong place, and it's time to step back and start from the basics.

Hope this helps...

-Tim

On 5/7/2014 11:46 AM, Cunningham, Mike wrote:
>
> Hi, would the scalability issue be due to transaction management? I
> think the unique rule could be broken if using a trigger since two
> transactions from different sessions can't see each others data until
> after a commit. Session B could have checked for uniqueness prior to
> session A committing its data. If session A commits, then session B
> commits (the same data) the unique rule would be broken. Am I missing
> something in my reasoning?
>
> *Michael Cunningham*
> /Senior Database Administrator/
> /The Doctors' Company/
> 707.226.0221 - desk
> 707.337.0184 - cell
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Carlos Sierra
> *Sent:* Wednesday, May 07, 2014 8:32 AM
> *To:* Mohamed Houri
> *Cc:* ftaheny_at_gmail.com; oracle-l-freelists; vijaysehgal21_at_gmail.com
> *Subject:* Re: conditional unique index to restrict duplicates of a
> particular set
>
> Mohamed,
>
> Would you please elaborate why an INSERT/UPDATE trigger using a
> non-unique index on those 4 columns would not scale? The trigger would
> do an index lookup using the 4 columns when the set of values passed
> is expected to be unique and raise an error if there exists such set.
> This method would be clean, and the index could be used by other queries.
>
> Cheers — Carlos
>
> On May 7, 2014, at 9:50 AM, Mohamed Houri <mohamed.houri_at_gmail.com
> <mailto:mohamed.houri_at_gmail.com>> wrote:
>
>
>
> I am wondering how you will be able to enforce uniqueness via a
> trigger in a multi-user concurent application? It will not scale. You
> need either to use your unique function based index or, if in 11gR1
> and up create a virtual column and place a unique constraint (unique
> index) on this virtual column
>
> Best regards
>
> Mohamed Houri
>
> 2014-05-07 15:13 GMT+02:00 Fergal Taheny <ftaheny_at_gmail.com
> <mailto:ftaheny_at_gmail.com>>:
>
> Hi Carlos,
>
> Won't you face a mutating table error if you try to use a trigger for
> this.
>
> Regards,
> Fergal
>
>
>
> --
> Bien Respectueusement
> Mohamed Houri
>
> *Confidentiality Notice*: This message and any attachments hereto may
> contain confidential and privileged communications or information
> and/or attorney client communications or work-product protected by
> law. The information contained herein is transmitted for the sole use
> of the intended recipient(s). If you are not the intended recipient or
> designated agent of the recipient of such information, you are hereby
> notified that any use, dissemination, copying or retention of this
> e-mail or the information contained herein is strictly prohibited and
> may subject you to penalties under federal and/or state law. If you
> received this e-mail in error, please notify the sender immediately
> and permanently delete this e-mail.
>

--
http://www.freelists.org/webpage/oracle-l