CODEWITHSUNDEEP
mysql
art12345
art12345

Reputation: 59

id not auto incremented and duplicate records

This is my table tb_currency_minimum_amount enter image description here

When I am inserting a record using 

INSERT IGNORE INTO tb_currency_minimum_amount ( id, currency_id, payment_method, minimum_amount) VALUES (NULL, 1, 16, 0.02)

again and again it creates a new entry

rows with id 32,33,34 are same

It i assigning it id 32 not 27 and ignoring duplicates

2:enter image description here

Upvotes: 0

Views: 357

Answers (3)

axiac
axiac

Reputation: 72177

The purpose of an AUTO_INCREMENT column is to ensure unique identifiers for the rows in the table.

It is just an implementation detail that it uses consecutive integer values. It is not a requirement for these values to be consecutive. Your code must not rely on the values being consecutive.

The value of the AUTO_INCREMENT is increased by each INSERT statement when a value for that column is not provided, no matter if the updated value is used (the query creates a new row) or not (it fails because of a duplicate key constraint or it updates an existing row because of ON DUPLICATE KEY).

Upvotes: 2

Noob
Noob

Reputation: 752

Auto increment is count every record with +1. But it does not check of other columns are unique or duplicates. In your id column which is auto increment I do not see any duplicates.

Could you elaborate your purpose what you trying to archive.

Upvotes: 0

Lea Krause
Lea Krause

Reputation: 432

It is adding assigning it id 32 not 27 and ignoring duplicates

If i understand your question correctly, you should use the SQL SELECT DISTINCT Statement.

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

The SELECT DISTINCT statement is used to return only distinct (different) values.

Upvotes: -1

Related Questions