Post reply

SQL server Unique Constraint violation message

  • May 16, 2006 at 2:43 am

    #117567

    We have a Bloomberg trade feed that comes in and updates a SQL table, with a unique constraint to stop duplicates. On violation you get the following message "Violation of UNIQUE KEY constraint 'CustIDunique'. Cannot insert duplicate key in object 'Customers'. The statement has been terminated."

    The feed will then retry for 30 secs. This just slows everything down, especially if we need to role back trades. Is there a way to suppress the violation message so it just goes onto the next trade.

  • May 16, 2006 at 3:55 am

    #638085

    You should have the feed not trying to insert already existing data.

    It can be done in several different ways, but the common factor is to modify the feeds insert statement.

    /Kenneth

  • May 16, 2006 at 4:26 am

    #638093

    How can I modify the insert statement as it's coming through a Bloomberg gateway trade feed.

  • May 16, 2006 at 5:17 am

    #638103

    If you cannot modify the insert statement from the feed because it is not under your code control, you can have an interface table in which the feed inserts the values and this table will not have that unique constraint.  Then, have a process that takes this data, does a insert into..<main_table>...select ...from <interface_table> where not exists (....)

    to select only the unique value records...there are other ways as well to write such an insert statement.

  • May 16, 2006 at 5:42 am

    #638108

    Thanks. Will look into.

  • May 16, 2006 at 6:33 am

    #638120

    To stop duplicates in Unique constraint turn it into an index and use the IGNORE_DUP_KEY, it is what it was created for.  Try the links below for detail.  Hope this helps.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueindex.asp

     

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueconstraint.asp

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • May 18, 2006 at 8:12 am

    #638738

    I'd also suggest going back to Bloomberg. As a vendor of yours (who I'm sure you're paying very handsomely), they shoud not subject your DB to any kind of integrity issues.


    Terry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply