which duplicate key was hit on last insert? 2007-11-12 - By Lev Lvovsky
Back
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?
thanks,
-lev
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
