How to Implement a Multiple-Column Auto-Increment Primary Key in MySQL?

Using Multiple-Column Auto-Increment Primary Key with Auto-Increment on Secondary Column

Maintaining data integrity in databases with shared structures can be crucial. As part of your strategy, you're considering using two columns as a joint primary key, one being a database identifier and the other a table key. However, you want to set the table key to auto-increment based on the database identifier.

For MySQL, you have options depending on the storage engine you're using. If you're using MyISAM, you can leverage its ability to specify AUTO_INCREMENT on a secondary column in a multiple-column index.

To understand how this works, consider the following example:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

In this table, the grp and id columns form the composite primary key. The id column is auto-incrementing, but its value calculation considers the prefix (in this case, the grp value).

Applying this concept to your scenario:

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;

Inserting sample data:

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

Querying the table:

SELECT * FROM mytable ORDER BY database_id,table_id;

This will return results grouped by database_id with auto-incremented table_id values for each group.

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