On Nov 12, 2007 6:42 PM, Lev Lvovsky <[EMAIL PROTECTED]> wrote: > >> We have tables in our database that, in addition to primary key >> constraints also have unique() constraints of several columns in the >> table: >> >> CREATE TABLE Test ( >> COL1 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, >> COL2 VARCHAR(10) NOT NULL, >> COL3 VARCHAR(10) NOT NULL, >> UNIQUE(COL2, COL3); >> ); >> >> There are two insert scenarios which would cause a DUPLICATE KEY >> error - one which contained a pre-existing COL1 value, and another >> which contained a pre-existing COL2,COL3 value. Is there any way to >> differentiate between which KEY, 'PRIMARY', or 'UNIQUE' (as listed by >> the 'show create table Test' in the mysql client) was actually >> violated in the last insert? >> >> This is specifically for use with the "ON DUPLICATE KEY UPDATE" >> clause. Normally we use behavior this to produce a no-op upon adds >> of identical records, however this can cause problems in the case >> that the KEY that was DUPLICATE was in fact the UNIQUE() key, and not >> the PRIMARY key. Knowing which of these triggered the DUPLICATE key >> error would be helpful in determining what to do next - is this >> information stored anyplace?
> On Nov 12, 2007, at 1:27 PM, Michael Dykman wrote: > > > When I last researched the question, maybe 6 months ago, the sad truth > > was (and liely still is): no, there is no way to distinguish between > > your various unique keys when the duplicate key contraint is raised.. > > > > - michael dykman > > Thanks for your reply Michael. > > In our code, we're running this via a stored procedure (for security) > - is the only choice at this point, to do a SELECT, to check whether > the record exists, and subsequently INSERT only if we return no rows? > > That seems like the wrong way to do it, but I'll definitely use it if > it's my only choice. I had a similar issue in a fairly sophisticated migration application.. in that case I was <fortunate> enough to have data available in my procedure that allowed me to disambiguate logically without another hit to the database.. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
