How to Auto-Increment a Non-First Column in a MySQL Joint Primary Key?

Auto-Increment Using Joint Primary Key in MySQL

MySQL allows for the creation of tables with multiple primary keys, but it doesn't natively support auto-incrementing on non-first primary columns. To address this, consider the following approach for creating a joint primary key with auto-increment based on a specific column:

MyISAM Engine

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column within a multiple-column index. This means it's possible to auto-increment on the table_id column based on the database_id value.

CREATE TABLE mytable (
    table_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    database_id MEDIUMINT NOT NULL,
    other_column CHAR(30) NOT NULL,
    PRIMARY KEY (database_id, table_id)
) ENGINE=MyISAM;

Example

Using your sample data, you can achieve the desired behavior as follows:

INSERT INTO mytable (database_id, other_column) VALUES
    (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo');

SELECT * FROM mytable ORDER BY database_id, table_id;

This will generate table IDs based on the corresponding database IDs:

+----------+-------------+--------------+
| table_id | database_id | other_column |
+----------+-------------+--------------+
|        1 |           1 | Foo          |
|        2 |           1 | Bar          |
|        3 |           1 | Bam          |
|        1 |           2 | Baz          |
|        2 |           2 | Zam          |
|        1 |           3 | Zoo          |
+----------+-------------+--------------+

The above is the detailed content of How to Auto-Increment a Non-First Column in a MySQL Joint Primary Key?. For more information, please follow other related articles on the PHP Chinese website!