AUTO INCREMENT On A MySQL Secondary Column

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 TABLEMyDevices(
DeviceIdint PRIMARY KEY NOT NULL AUTO_INCREMENT,
DeviceNamevarchar(10) NOT NULL,
CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);


Executing this gives us the table MyDevices

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

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 TABLEMyDevices(
CustomerIdint NOT NULL,
DeviceIdint NOT NULL PRIMARY KEY AUTO_INCREMENT,
DeviceNamevarchar(10) NOT NULL,
CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAtdatetime 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;

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 TABLEMyDevices(
CustomerIdint NOT NULL,
DeviceIdint NOT NULL AUTO_INCREMENT,
DeviceNamevarchar(10) NOT NULL,
CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAtdatetime 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;

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..