How to implement a composite primary key with an auto-increment column in MySQL?

Implementing Composite Primary Key with Auto Increment in MySQL

MySQL InnoDB tables support composite primary keys, enabling the identification of unique rows using multiple columns. However, it's important to note that only one column can be auto-incremented, and it must be specified as part of the primary key definition.

When attempting to create a composite primary key with one auto-increment column and two foreign keys, users may encounter an error stating "ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key".

To overcome this issue and implement the desired composite primary key, triggers or procedures are commonly recommended. However, for simplicity, an alternative approach is to use the following MySQL trigger:

<code class="sql">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 ;</code>

This trigger automatically calculates and assigns an incrementing 'sr_no' value for new rows that share the same 'app_id' and 'test_id' values. By implementing this trigger, you can achieve the desired composite primary key with an auto-increment column.

Remember, triggers require proper handling to ensure data integrity and avoid race conditions. It's recommended to thoroughly test and maintain triggers to prevent any unexpected behavior or performance bottlenecks.

The above is the detailed content of How to implement a composite primary key with an auto-increment column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!