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

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 07 May 2014 14:25:43 -0600

Vijay,

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Carlos Sierra 
*Sent:* Wednesday, May 07, 2014 8:32 AM
*To:* Mohamed Houri
*Cc:* ftaheny@xxxxxxxxx; oracle-l-freelists; vijaysehgal21@xxxxxxxxx
*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@xxxxxxxxx <mailto:mohamed.houri@xxxxxxxxx>> 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@xxxxxxxxx <mailto:ftaheny@xxxxxxxxx>>: 

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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2014