Post reply

Determine Which Record Failed a Constraint

  • October 14, 2010 at 8:40 pm

    #228062

    Hope someone can help,

    I have an Update statement in a stored procedure that updates all records in a particular table.

    Some of the fields in the table have Check Cocnstraints defined.

    When I execute the Update, it fails with an error

    "The UPDATE statement conflicted with the CHECK constraint "CK_Constraint_Name"

    Is there any way to determine which record failed the Check Constraint?

    For example, is there a system function or similar which will return the Primary Key value or the like, of the record that caused the failure.

    Any suggestions are appreciated.

    Doug

  • October 14, 2010 at 9:45 pm

    #1237289

    Are you familiar with the TRY - CATCH construction? If not the following link will be most helpful.

    http://technet.microsoft.com/en-us/library/ms179296.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • October 14, 2010 at 11:17 pm

    #1237302

    Thanks Ron

    Firstly, I wouldnt consider myself an SQL expert, but yes, I am familiar with the TRY...CATCH construct.

    I may be missing something, but l dont think it will help me in this case.

    Its not trapping the error that is the issue, its determining which record caused the error.

    If my UPDATE statement updates all the records in a table (which it does) and the table for arguement sake contains over 10,000 records, how can I determine the record that caused the error? i.e. the specific record which failed the check constraint?

    Am I still missing something?

Viewing 3 posts - 1 through 2 (of 2 total)

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