A bit of a quiz to see if you’re paying attention:
First, we’ll set up a couple tables with some auto increment fields:
CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id)); CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));
Next, we’ll insert a row to one table:
INSERT INTO t1 VALUES (NULL);
Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():
INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);
What happens here? Did the insert go ok, or did we get an error? What happens if we now issue
SELECT * FROM t2;
all the times i’ve tried such things it gave me the ‘duplicate key’ (i’m sure untill v5.0 MySQL behaves this way).
the only way i’ve achieved this was with:
CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, PRIMARY KEY (id,refT1)) ENGINE=MyISAM;
innodb does not support such things (like a composed primary key: auto_inc + plain_int)
which version are you running?
where’s the catch ? :-)
Comment by Augusto — August 22, 2006 @ 11:25 pm
Yes, if you create t2.id as a primary key, you’ll get the duplicate key error. That’s why the example uses a non-unique index.
The storage engine does not matter – this behavior is the same with MyISAM and InnoDB.
Tested in 4.1.21 and 5.0.22
Comment by snoyes — August 23, 2006 @ 2:42 am
[…] by his blog, A Little Noise. He offers two short gotchas with MySQL stuff this week: the first, a sneaky ORDER BY quiz; the second involving an auto_increment puzzle. Go and see if you need to click his “Show […]
Pingback by Log Buffer #7: A Carnival of the Vanities for DBAs — February 1, 2013 @ 9:55 am