How Can I Auto-Increment a Column in a Multi-Column Primary Key?

Using Auto-Increment on a Multi-Column Primary Key

Maintaining data integrity across databases can be challenging. By utilizing two columns as a composite primary key, you can ensure the uniqueness of each row. However, it's essential to consider how to manage the unique identification of rows when auto-incrementing one of the columns.

Using AUTO_INCREMENT in MyISAM

MyISAM storage engine allows you to specify AUTO_INCREMENT on a non-primary column in a multiple-column index. The auto-increment value is calculated as MAX(auto_increment_column) 1 where a given prefix is matched. This is especially useful when organizing data into ordered groups.

Example Usage:

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 example, id is auto-incremented based on the value of grp.

Applying to Your Example:

For your specific requirement, you can create the following table structure:

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 Data and Results:

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;

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

As you can see, table_id is auto-incremented for each unique database_id. This ensures both the uniqueness of each row and the ordered grouping of rows based on database_id.

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