How to Achieve Auto Increment in a Composite Key in MySQL?

Defining Composite Key with Auto Increment in MySQL: A Challenge

Creating a composite key with an auto-incrementing column can present a challenge in MySQL. This is particularly true for InnoDB tables, which impose certain restrictions.

Scenario and Error:

Consider the following table schema:

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);

An attempt to execute this query may result in the following error:

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

Goal:

The desired behavior is for the sr_no column to increment automatically for unique combinations of app_id and test_id.

Limitations and Solutions:

InnoDB tables allow only a single auto-increment column. Therefore, the desired behavior cannot be achieved directly through the schema definition.

Alternatives:

One solution to overcome this limitation is to use a trigger or procedure to manually increment the sr_no column based on the criteria. For example, the following trigger can be used:

DELIMITER $$

CREATE TRIGGER xxx BEFORE INSERT ON issue_log
FOR EACH ROW BEGIN
    SET NEW.sr_no = (
       SELECT IFNULL(MAX(sr_no), 0) + 1
       FROM issue_log
       WHERE app_id  = NEW.app_id
         AND test_id = NEW.test_id
    );
END $$

DELIMITER ;

Other Considerations:

• Triggers and procedures can add overhead to the database.
• Alternative database engines, such as MyISAM, may support auto-incrementing across multiple columns within a composite key.

The above is the detailed content of How to Achieve Auto Increment in a Composite Key in MySQL?. For more information, please follow other related articles on the PHP Chinese website!