How to generate unique and sequential Id values

Question

We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it:

CREATE TABLE `Task` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  CONSTRAINT `task_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) 
  REFERENCES `Vendor` (`vendor_id`))
  ENGINE=InnoDB AUTO_INCREMENT=1 
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We have multiple Vendors, and we want that the id values should be sequential starting from 1 for each Vendor.

Example:

• Vendor 1: ids: 1, 2, 3

• Vendor 2: ids : 1, 2, 3

One of the solutions that clicks me is to, whenever a Task is added, we find out the max(id) for that vendor and then increment it by 1 and assign it to the corresponding row, e.g.:

INSERT INTO `Task` (`id`, `vendor_Id`, `name) 
VALUES ((select max(id) + 1 from Task where vendor_Id = 1), '1', 'Test');

But I get an error message with that approach:

You can't specify target table 'Task' for update in FROM clause

From MySQL docs:

You cannot update a table and select from the same table in a subquery.

Questions

• How do I go about solving the problem with this approach?

• Are there other options?

Answer 1

A simple rearrangement:

INSERT INTO `Task`
       (`id`, `vendor_Id`, `name`) 
    SELECT MAX(id) + 1, 123, 'Test'
        FROM Task
        WHERE vendor_Id = 123;

(I changed the vendor_id to make it clearer.)

Answer 2

The simplest solution is to alter the table engine to MyISAM and use (vendor_id, id) as a composite primary key. If engine altering is not safe the best solution is not to use auto_increment but simple INT updated from the trigger. PS. The table structure do not contain PK at all... - akina

Use either a trigger or stored procedure. And add a PK. - topshot

Answer 3

You can do it like this:

INSERT INTO `Task` 
    (`id`, `vendor_Id`, `name) 
VALUES 
    (null, '1', 'Test');

If a column has AUTO INCREMENT and you insert null into that field, MySQL will automatically insert the next higher number. Here is an example:

CREATE TABLE `t2` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `k1` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
;

then run this:

insert into t2
(id,k1)
values
(null, 'test'),
(null, 'test2');

You should see:

1   test
2   test2