I thought auto_increment prevents duplicates entries?

Question

I am getting duplicate entry error for key 'primary' when trying to insert values and I can't get past it. I added auto_increment to the integer part of the composite key (term_taxonomy_id). Isn't auto_increment supposed to resolve duplicate entries in these situations by incrementing the error-causing record on the fly?

+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| object_id        | varchar(50)         | NO   | PRI | NULL    |                |
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_order       | int(11)             | NO   |     | 0       |                |
+------------------+---------------------+------+-----+---------+----------------+

Auto increment usually only sets a value if you don't explicitly provide one. In this case, it looks like you have explicitly told it to use a specific value. — Moshe Katz, Nov 15 '22 at 23:12
no it just auto increment it. but you should seed it to the largest id `ALTER TABLE xxx AUTO_INCREMENT = 1000; ` — IT goldman, Nov 15 '22 at 23:12

score 2 · Answer 1 · answered Nov 15 '22 at 23:26

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html says:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

So the auto-increment column generates a new, unique value only if you insert NULL or 0. If you specify a nonzero integer value, you override the auto-increment, and MySQL trusts that you are inserting the value you want. But that means you take responsibility for ensuring the values you insert are unique.

I thought auto_increment prevents duplicates entries?

1 Answers1