Is it possible to determine what key trigger the "duplicate key" error?

user_5960843 0
Viewed 127

I have a table with 1 primary key and 2 indexes. I use the "on duplicate key" statement in my stored procedure to update the existing rows when needed. However, the issue is that the even on duplicate key is triggered regardless what key is going to be duplicated.

I'd like to understand how can I make the difference in my stored procedure to update only when the duplicate is due to the primary key.

Typically, error below on primary key, OK to update:

#1062 - Duplicate entry '4' for key 'pp_member.PRIMARY'

Error below on Index, no update, throw exception instead.

#1062 - Duplicate entry 'aaa@aaa.com' for key 'pp_member.member_email_UNIQUE'

duplicates mysql sql
2 Answers

If you are doing this in a stored procedure, you can generate errors for the other unique keys. For instance, as pseudo-code:

if (exists (select 1 from t where t.email = v_email))
then
    -- return meaningful error here
end;
insert into t ( . . . )
    values ( . . . )
    on duplicate key update . . . ;

EDIT:

Actually, you can figure out where the error occurs. However, it might not be worth the effort.

You can attempt the insert without the on duplicate key part. Then, if there is an error, you can use exception handling to parse the error. If the error is caused by the primary key, then perform an update. Otherwise do whatever you might want.

That said, this gets a bit complicated in a multi-threaded environment. Or if you are trying to insert more than one row at a time.

user_11440350

In fact I was looking to use the details provided by GET DIAGNOSTICS combined with a HANDLER.

However, the information I need are set "always empty" in the diagnostic area as per the documentation... which is not easy to find actually.

Anyway, topic closed. I have to use something else.

user_59608430
Related