List:General Discussion« Previous MessageNext Message »
From:Jan Fabry Date:November 27 2009 12:48pm
Subject:Which unique key is hit with my insert?
View as plain text  
Hello,

When I have a table with (for example) three keys (one primary, auto- 
increment, and two uniques on a column, eg. 'code' and 'name'), how  
can I (efficiently) know which uniqueness constraint was violated when  
doing an insert?

You get an error "#1062 - Duplicate entry 'Value' for key 2", but how  
do I know key 2 is the key for the 'code' column and not the 'name'  
column?

We develop this application with multiple developers, and I want to  
prevent the day we don't add the constraints to a table in the same  
order, so the second key on my machine is the third key on another  
machine, and vice-versa.

Mapping to the exact column names involved is not really necessary,  
just to the key name is enough.

I know I can use something like

SHOW INDEXES FROM products_to_categories
WHERE Key_name = "PRIMARY"
AND Seq_in_index = '2';

But this is an extra query - is there a way to know it from the first  
query?


I also posted this question on Stack Overflow [
http://stackoverflow.com/questions/1486068/which-unique-key-is-hit-with-my-insert 
  ], but I want to check whether this is the best answer. If anything  
new comes up here, I will also put in on the Stack Overflow page.

Greetings,

Jan Fabry
Thread
Which unique key is hit with my insert?Jan Fabry27 Nov