MySQL Forums
Forum List  »  InnoDB

How can I look up names of columns involved in a dupe error 1062?
Posted by: Byron Young
Date: November 13, 2008 07:29PM

(I posted this into the Newbie forum before realizing there was a Constraints forum. Many apologies)

When I get a 1062 error telling me that some unique constraint failed in the previous UPDATE or INSERT statement my program just ran, I want my program to be able to look up the columns that had the duplicate data and give a useful error message to the user, like 'Another resource is using value X in the Y field'.

How can I get the names of the columns from the message 'ERROR 1062 (23000): Duplicate entry '1' for key 3' in mysql 5?

I checked the INFORMATION_SCHEMA tables COLUMNS, KEY_COLUMN_USAGE, and STATISTICS, but there is no indication of key number in those. I also tried SHOW INDEX, which looks like it might give me the key number if I group the rows returned by index name. But that seems like a hacky solutions and I have no idea if it works in all cases, or if it's just coincidental to how I have set up my table.

Is there a mysql-approved way of translating 'key 3' into the name of a constraint? Where does MySQL itself get that number?

Thanks
Byron

Options: ReplyQuote


Subject
Views
Written By
Posted
How can I look up names of columns involved in a dupe error 1062?
4437
November 13, 2008 07:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.