Sep 21, 2020 5:35 PM

Adding AUTO_INCREMENT for second column in MySQL/MariaDB

Adding AUTO_INCREMENT for second column in MySQL/MariaDB

In this article I will explain how to add AUTO_INCREMENT for the second column in MySQL/MariaDB. The AUTO_INCREMENT attribute can only be added for one column, which must be defined as the primary key. But you can implement the generation of sequence numbers for the second column using a trigger.

First, create a simple `users` table:

--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`second_id` INT UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
);

Let's create a trigger generateSecondId that will generate sequence numbers for the second column every time a record is inserted into the database:

DROP TRIGGER IF EXISTS generateSecondId;

DELIMITER $$
CREATE TRIGGER generateSecondId
BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
  SET @startId = 1000;
  SET NEW.`second_id` = (SELECT IFNULL(MAX(`second_id`), @startId) + 1 FROM `users`);
END $$
DELIMITER ;

You can set the initial value using the @startId variable (for example 1000).

Now, every time a record is inserted into the database, the declared trigger will run:

INSERT INTO `users` (`id`, `second_id`) VALUES (NULL, '');

SELECT * FROM `users`;

--
--	[output]
--
--	id	second_id
--	1	1001
--	2	1002
--	3	1003
--	4	1004
--	5	1005

Other articles