AUTO INCREMENT On A MySQL Secondary Column
Tags: mysql, autoincrement, tables, myisam
Published: 2021-12-03 20:54:33

## MySQL AUTO_INCREMENT 

MySQL provides us with AUTO_INCREMENT attribute that allows us to create a column that contains a sequence of numbers (1, 2, 3, and so on).  AUTO_INCREMENT attribute is generally used to generate a  unique number that act as a primary key in a table.

Let see a simple example.  Here we have a table Mydevices that stores different devices we have such as laptop, computer, cellphone, tablet and so on.

The MySQL statement to create the table Device is given below

`CREATE TABLE `MyDevices` (
  `DeviceId`   int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `DeviceName` varchar(10) NOT NULL,
  `CreatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
  `

Executing this gives us the table MyDevices

![The table MyDevices](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qdh1qxcap3qc6sjdfw3p.JPG)

Lets Add a few rows, to see that the sequencing is working as we want.

`Insert into mydevices(DeviceName) VALUES ('Laptop');
Insert into mydevices(DeviceName) VALUES ('Computer');
Insert into mydevices(DeviceName) VALUES ('Phone');
Insert into mydevices(DeviceName) VALUES ('Tablet');
`
If we do a query on our table we should find that MySQL have added a nice Sequences number to our DeviceId column


![The Sequence number generated for the Data inserted in the MyDevices table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k3sn685pbbe9roxr62np.JPG)

Now comes the twist.  Lets add one more column, say CustomerId in the above table.  Lets drop the Devices table and recreate it as follows;

`
DROP TABLE MyDevices;
CREATE TABLE `MyDevices` (
  `CustomerId` int NOT NULL,   
  `DeviceId`   int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `DeviceName` varchar(10) NOT NULL,
  `CreatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
`

Lets Add a few rows, to see that the sequences are working as we want.

`Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Laptop');
Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Computer');
Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Phone');
Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Tablet');
Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Laptop');
Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Printer');
`
Lets see how the data looks like now;

![The same Sequence number for all customers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wnii04yzhqbyk4yjk7kq.JPG)

As you will see, the auto increment feature keeps on adding the sequences number for each row as we insert.  (1,2,3,4,5,6)

But what if this is not the behavior we want.. What if we want the sequence number to increase per customer.  That is each customer has its own sequence numbers for the devices.

So Customer 1, has sequences 1,2,3... and so on
while Customer 2 devices gets a sequence number again from 1,2,3.. and so forth.

There are many ways to do this such as using stored procedures.  But let us see today a very easy method to achieve this, by defining the table in a certain way.

## ENGINE= MyISAM 

The default engine storage for MySQL relational database management system  prior to versions prior to 5.5 was MyISAM. It is based on the older ISAM code, but it has many useful extensions.

If we define tables either as MyISAM or BDB, it gives the ability to specify AUTO_INCREMENT on a secondary column in a multiple-column index. 

For such tables, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. 

This is useful when you want to put data into ordered groups.  Lets adapt this to the above example of MyDevices table.

Lets create the table now as a MyISAM table

`CREATE TABLE `MyDevices` (
  `CustomerId` int NOT NULL,
  `DeviceId`   int NOT NULL AUTO_INCREMENT,
  `DeviceName` varchar(10) NOT NULL,
  `CreatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`CustomerId`, `DeviceId`)
  ) ENGINE= MyISAM;`

Lets now insert data to this table (we will add a few more inserts that before);

`Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Laptop');
Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Computer');
Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Phone');
Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Tablet');
Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Laptop');
Insert into mydevices(CustomerId, DeviceName) VALUES (3,'Printer');
Insert into mydevices(CustomerId, DeviceName) VALUES (4,'Printer');
Insert into mydevices(CustomerId, DeviceName) VALUES (4,'Laptop');
`

Lets see what we got into our tables;

![The table with separate Sequences for each customer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ezca4ir3rboh5o76blx5.JPG)

There you go, we got now separate sequences for each of the customers. 

QUIZ : In the above table what sequence do you think will customer 3 have for DeviceId if we inserted this SQL statemwent;

`Insert into mydevices(CustomerId, DeviceName) VALUES (3,'Fax');
`

Answer : 2  ( as the last sequence generated for customer 3 was '1', the next number it assigned will be 1+1 = 2


Hope you enjoyed this article..