0

I'd like to have separate auto increments for records which are related to different records in other database tables.

For example imagine car manufacturer. There are lots of car manufacturers and there are also lots of cars. I want to auto increment car serial number using mysql auto increment for each manufacturer starting from 0. Is it even possible in my sql?

For example:

Manufacturers:

  • Skoda
  • Volvo
  • Hyundai

now take a look at serial No:

Cars:

  • name: skoda_car1, serial No: 1, manufacturer: Skoda
  • name: skoda_car2, serial No: 2, manufacturer: Skoda
  • name: skoda_car3, serial No: 3, manufacturer: Skoda
  • name: volvo_car1, serial No: 1, manufacturer: Volvo
  • name: volvo_car2, serial No: 2, manufacturer: Volvo
  • name: hyund_car1, serial No: 1, manufacturer: Hyundai

database tables:

CREATE TABLE `manufacturer` (
  `idmanufacturer` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idmanufacturer`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC));

CREATE TABLE `cars` (
  `idcars` INT NOT NULL AUTO_INCREMENT,
  `id_manufacturer` INT NOT NULL,
  `serial_id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idcars`),
  UNIQUE INDEX `id_manufacturer_UNIQUE` (`id_manufacturer` ASC),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  CONSTRAINT `FK_idmanufacturer`
  FOREIGN KEY (`id_manufacturer`) REFERENCES `manufacturer` (`idmanufacturer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
Benas
  • 2,106
  • 2
  • 39
  • 66
  • 3
    What you want makes zero sense. You're oblivious to the job that `auto_increment` does. Its job is not to produce numbers to your liking or to satisfy people's OCD when it comes to numbers. The task of `auto_increment` is to produce **unique numbers** in highly **concurrent environment**. Create another column, call it `unique_id` or something like that and perform your number-logic there. If you tamper in any way with `auto_increment`, you can expect absolute horrific issues. Just forget that `auto_increment` even exists, that's the best thing you can ever do, and let it do its job. – N.B. Mar 15 '18 at 13:26
  • @N.B. well, if you use myisam and the auto increment field is the 2nd field of a multi-column primary key, then this is exactly the behaviour you get. Unfortunately, this does not apply to innodb. – Shadow Mar 15 '18 at 14:41
  • @Shadow if MyISAM, then all these nice foreign key constraints are garbage also :) and I know you're aware of that, so discussing MyISAM won't get OP anywhere. – N.B. Mar 15 '18 at 14:50

0 Answers0