user1032531 user1032531 - 5 months ago 20
SQL Question

How to implement an auto_increment composite primary key with MySQL InnoDB?

I have a table which has a composite primary key made up of one non-auto_increment column and one auto_increment column. The auto_increment column needs to increment individually for each of the non-auto_increment column values (more on this later). The storage engine is InnoDB. I don't wish to lock the table because of performance concerns. After inserting a value, a means to retrieve the last auto_increment value must be available.

The below script works at first, but the last INSERT results in id, checkingaccounts_id is

3, 2
, but
1, 2
is desired. This is what I meant by
The auto_increment column needs to increment individually for each of the non-auto_increment column values


Trigger and Stored Procedures are both acceptable, and so is a PHP/PDO application solution which somehow mimics the MySQL auto_increment behavior.

mysql> EXPLAIN checkingaccounts;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | varchar(45) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN checks;
+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| checkingaccounts_id | int(11) | NO | PRI | NULL | |
| data | varchar(45) | YES | | NULL | |
+---------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO checkingaccounts(id, data) VALUES(0,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checkingaccounts(id, data) VALUES(0,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checkingaccounts;
+----+------+
| id | data |
+----+------+
| 1 | bla |
| 2 | bla |
+----+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,1,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,1,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checks;
+----+---------------------+------+
| id | checkingaccounts_id | data |
+----+---------------------+------+
| 1 | 1 | bla |
| 2 | 1 | bla |
+----+---------------------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,2,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checks;
+----+---------------------+------+
| id | checkingaccounts_id | data |
+----+---------------------+------+
| 1 | 1 | bla |
| 2 | 1 | bla |
| 3 | 2 | bla |
+----+---------------------+------+
3 rows in set (0.00 sec)

mysql>

CSK CSK
Answer

remove the auto_increment feature, try store procedure

CREATE PROCEDURE insertChecks (IN AccID int(9),IN data varchar(50))
BEGIN
    DECLARE cid INT DEFAULT 1;
    select (count(*) + 1) into cid from checks where checkingaccounts_id = AccID;

    INSERT INTO checks(id,checkingaccounts_id,data) VALUES(cid,AccID,data);
END

And

call insertChecks(1,'bla');
call insertChecks(1,'bla');
call insertChecks(2,'bla');

solution 2:

CREATE PROCEDURE insertChecks (IN AccID int(9),IN data varchar(50))
BEGIN
    INSERT INTO checks(id,checkingaccounts_id,data) select (count(*) + 1) , AccID, data from checks where checkingaccounts_id = AccID;
END