mysql two column primary key with auto-increment

Question

I have multiple databases with the same structure in which data is sometimes copied across. In order to maintain data integrity I am using two columns as the primary key. One is a database id, which links to a table with info about each database. The other is a table key. It is not unique because it may have multiple rows with this value being the same, but different values in the database_id column.

I am planning on making the two columns into a joint primary key. However I also want to set the table key to auto increment - but based on the database_id column.

EG, With this data:

table_id   database_id     other_columns 1          1 2          1 3          1 1          2 2          2 

If I am adding data that includes the dabase_id of 1 then I want table_id to be automatically set to 4. If the dabase_id is entered as 2 then I want table_id to be automatically set to 3. etc.

What is the best way of achieving this in MySql.

Answer 1

if you are using myisam

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

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;  INSERT INTO animals (grp,name) VALUES     ('mammal','dog'),('mammal','cat'),     ('bird','penguin'),('fish','lax'),('mammal','whale'),     ('bird','ostrich');  SELECT * FROM animals ORDER BY grp,id;  Which returns:  +--------+----+---------+ | grp    | id | name    | +--------+----+---------+ | fish   |  1 | lax     | | mammal |  1 | dog     | | mammal |  2 | cat     | | mammal |  3 | whale   | | bird   |  1 | penguin | | bird   |  2 | ostrich | +--------+----+---------+ 

For your example:

mysql> 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; Query OK, 0 rows affected (0.03 sec)  mysql> INSERT INTO mytable (database_id, other_column) VALUES     ->     (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo'); Query OK, 6 rows affected (0.00 sec) Records: 6  Duplicates: 0  Warnings: 0  mysql> 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          | +----------+-------------+--------------+ 6 rows in set (0.00 sec)