What causes duplicate PKs in MySQL?

Question

I encountered this quite a few times so far, but still don't understand it (my MySQL internals skills are equal to none).

I know it's probably a PEBKAC but trying to replicate the behavior manually ends up with an error (autoincrement).

CREATE TABLE `foo_bar` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(12) unsigned DEFAULT NULL,
  `order_id` int(12) unsigned DEFAULT NULL,
  `email_address` varchar(50) DEFAULT NULL,
  `mobile_number` varchar(20) DEFAULT NULL,
  `message` longtext NOT NULL,
  `message_received` int(12) unsigned DEFAULT NULL,
  `failed_to_send` tinyint(1) unsigned DEFAULT NULL,
  `fraudulent_activity` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8;

I find this hard to believe. Primary keys, by definition are unique and shouldn't even allow NULL values. If MySQL allows this, I doubt that anybody would consider MySQL for anything serious. — Icarus, Mar 12 '12 at 20:14
I'm assuming the table has multiple PKs. If a table has two PKs then it will create a string of the two column values, joined. And then the uniqueness is based on that value. So if you had a PK on `x` and `y` you could have `x -> 1, y -> 1` and `x -> 1, y -> 2` but it will fail if you try `x -> 1, y -> 1` again (same goes for `y`). — Marshall, Mar 12 '12 at 20:17
@Icarus is there maybe a way to force the query to execute regardless of errors? Maybe ARCHIVE dbs are different from the other (as they don't seem to have actual indexes)? — Kemo, Mar 12 '12 at 20:25
@Kemo then the issue is not really that MySQL is allowing duplicate PKs, the issue is that the database you are looking at doesn't have a primary key defined, perhaps because, as you mentioned, it's used for archiving purposes and it doesn't need this constraint. — Icarus, Mar 12 '12 at 20:30
@kemo: Can you edit your question by adding the table's definition? — ypercubeᵀᴹ, Mar 12 '12 at 20:31
@Icarus yeah but still the field is autoincremented and trying to abuse it manually fails.. I'll try to google it up some more and post any solutions here. — Kemo, Mar 12 '12 at 20:33
`AUTO_INCREMENT` does not necessarily mean a `UNIQUE` or `PRIMARY` key. Does the table have a Primary or Unique Key? — ypercubeᵀᴹ, Mar 12 '12 at 20:34
@marshall: What you are referring to, are not called mutilple PKs. But compound (or sometimes compoiste) PKs. A table can have only one PK. — ypercubeᵀᴹ, Mar 12 '12 at 20:37
@Kemo: So, `SELECT id, COUNT(*) FROM foo_bar GROUP BY id HAVING COUNT(*)>1` return rows? This is strange indeed. — ypercubeᵀᴹ, Mar 12 '12 at 20:39
@ypercube not only strange but I would have to "see to believe," as my blind uncle used to say... — Icarus, Mar 12 '12 at 20:43
@ypercube yes, there was one field that could be counted twice before I hit REPAIR TABLE. Another strange thing is that it never produced any errors except after dumping and trying to import it from that dump elsewhere. — Kemo, Mar 12 '12 at 20:46
Not sure how Archive engine works and how this happened. But I guess you can answer (and accept) that Repair Table solves the issue. — ypercubeᵀᴹ, Mar 12 '12 at 20:51
Are you sure the problem was not in importing? (the target table is different, has more indexes than the `id` Primary key)? — ypercubeᵀᴹ, Mar 12 '12 at 20:55
@ypercube right. I was trying to explain it in a way that is easy to understand. Hence why I explained how it creates that compound. And not all UIs make it clear that it's a compound, when it looks like you can assign a PK to multiple columns. — Marshall, Mar 12 '12 at 21:11

score 0 · Answer 1 · answered Mar 12 '12 at 20:22

When your program inserts a row in the database, it should provide NULL as the value for auto-incremented field:

CREATE TABLE  `customers` (
    `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` VARCHAR( 128 ) NOT NULL
) ENGINE = MYISAM ;


INSERT INTO  `customers` ( `id` , `name` )
VALUES ( NULL ,  'Customer 1' ), ( NULL ,  'Customer 2' );

If you try to insert a specific value in id field, MySQL will give an error:

SQL query: 

    INSERT INTO  `customers` ( `id` , `name` )
    VALUES ( '1',  'Customer 3' );

MySQL said: 
#1062 - Duplicate entry '1' for key 'PRIMARY'

Thanks but I already said that trying to replicate the behavior manually ends up with an error :) — Kemo, Mar 12 '12 at 20:27

score 0 · Accepted Answer · answered Mar 13 '12 at 07:34

0

Although the answer to "what caused this" didn't come up, REPAIR TABLE fixes the problem.

Answering this so I can close the question.

answered Mar 13 '12 at 07:34

Kemo

6,942
3
32
39

What causes duplicate PKs in MySQL?

2 Answers2