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

MySql: Configuring Two-Column Primary Key with Auto-Increment Feature

Context and Problem Statement

In a scenario where multiple databases possess similar structures and data must be maintained across them, safeguarding data integrity is crucial. Employing a two-column primary key proves beneficial in this regard. One column, representing a database ID, establishes a connection to a table containing database-specific information. Another column, the table key, possesses the attribute of non-uniqueness but facilitates the distinguishing of rows through the database ID column.

The goal is to join the two columns as a primary key while simultaneously automating table key increments based on the database ID column.

Solution in MySql

For MySql, a solution exists to address this requirement:

1. Using MyISAM Storage Engine:

MySql permits specifying AUTO_INCREMENT on secondary columns within multiple-column indexes, under the MyISAM storage engine. This characteristic enables generating values automatically for the AUTO_INCREMENT column utilizing the formula: MAX(auto_increment_column) 1 WHERE prefix=given-prefix.

2. Sample Implementation:

To exemplify, consider the following schema:

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;

3. Result:

Inserting data into this table, with sample database IDs and other data, demonstrates the auto-increment behavior:

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

Retrieving the data reveals the automatic table key increment based on the database ID:

SELECT * FROM mytable ORDER BY database_id,table_id;

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

By utilizing the MyISAM storage engine and understanding the mechanics of auto-incrementing under multiple-column primary keys, this approach ensures both data integrity and simplified data management.

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