MySQL - how to add unique INT value without using AUTO_INCREMENT?

Question

I have a MySQL table. I must be able to add unique INT values for speed which is not an AUTO_INCREMENT column.

CREATE TABLE ki
(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
  , comp_id INT(10) UNSIGNED NOT NULL
  , speed INT(4) UNSIGNED NOT NULL DEFAULT 0
  , position INT(4) UNSIGNED NOT NULL DEFAULT 0

  , PRIMARY KEY (id)

  , UNIQUE INDEX (comp_id, speed, position)
  , INDEX (comp_id)

  , FOREIGN KEY (comp_id)
      REFERENCES competitions (id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;

I want to insert new rows.

INSERT INTO ki (comp_id, speed, position) VALUES (1, 1, 0)
INSERT INTO ki (comp_id, speed, position) VALUES (1, 2, 0)
INSERT INTO ki (comp_id, speed, position) VALUES (1, 3, 0)
INSERT INTO ki (comp_id, speed, position) VALUES (1, 3, 0) -- error
INSERT INTO ki (comp_id, speed, position) VALUES (2, 1, 0)
INSERT INTO ki (comp_id, speed, position) VALUES (2, 3, 0)
INSERT INTO ki (comp_id, speed, position) VALUES (2, 2, 0)

With each comp_id the values of speed begin from 1. The value of speed should always be comp_id's biggest speed value + 1.

In case (1, 3, 0) already exists, the insert should be (1, max(speed) where comp_id = 1, 0). In other words, (1, 4, 0).

I do not want to modify the existing rows.

How could I do this on my SQL query? As told, speed must be unique and this has to work if there are several inserts exactly at the same time.

Would the following way work and quarantee an unique speed value (values starting from 1) if there are several inserts at the same time?

INSERT INTO ki (comp_id, speed, position) 
VALUES (
   1, 
   COALESCE((SELECT MAX(ki2.speed)  
     FROM ki AS ki2 
     WHERE ki2.comp_id = 1 
   ), 1) + 1,
   0
)

Possible duplicate of [SQL Server - How to insert a record and make sure it is unique](https://stackoverflow.com/questions/267804/sql-server-how-to-insert-a-record-and-make-sure-it-is-unique) — Martin, Aug 10 '17 at 14:05
@xms why is this question different? You're using numbers instead of strings but the concept is the same, you want a unique value without using the MySQL increment sytem. — Martin, Aug 10 '17 at 15:34
Also see: https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql . This is a basic problem that has been solved before. Read and research about it. It took me a minute to find this post; it more closely fits what you want. — Martin, Aug 10 '17 at 15:36
Is `position` relevant to the question? If not, please remove it from `UNIQUE` and other places. — Rick James, Aug 16 '17 at 03:38
Another approach: http://mysql.rjweb.org/doc.php/myisam2innodb#index_issues — Rick James, Aug 16 '17 at 03:41

score 1 · Answer 1 · answered Aug 07 '17 at 23:08

1

Use INSERT INTO ... SELECT statement:

INSERT INTO ki(comp_id, position, speed)
  SELECT 1, 0, max(speed)+1
  FROM ki

Beware! This has at least two pitfalls:

Inserting first row needs special handling of null values
More importantly, this can lead to race conditions, so you'd better use AUTO_INCREMENT for that which could lead to having gaps while insertion fails

While 1. pitfall is easy to deal with by wrapping it with coalesce(max(speed),0)+1, second pitfall is a real danger and normally you'd need to take this into account, or just create UNIQUE INDEX on speed column and have racing queries fail on any attempt but first with the same value.

answered Aug 07 '17 at 23:08

Kamil Gosciminski

16,547
8
49
72

Are you 100% sure that `SELECT`...`max(speed)+1` will give me an unique value? – xms Aug 07 '17 at 23:56
No, as mentioned in my answer. – Kamil Gosciminski Aug 08 '17 at 09:15
I've mentioned this as well in my answer. – Kamil Gosciminski Aug 08 '17 at 22:24
Sorry, but I cannot write a query which does the job. – xms Aug 08 '17 at 22:33

score 0 · Answer 2 · answered Aug 10 '17 at 14:20

I am assuming you can't use autooincrement because you already have one of those?If speed must be unique, then why isn't it the PK on the table and get rid of the autoincrement column and autoincrement Speed instead?

However, if Speed should have meaning and be defined on insert then you have a different problem. In this case you define a unique index on speed and let it fail on insert if the value is already there. Or you write a stored proc to insert if the speed is not there and update the record if it is. Which you choose is a function of the meaning of the data and the business rules.

MySQL - how to add unique INT value without using AUTO_INCREMENT?

2 Answers2