Mimicking autoincrement primary key using PHP

[NotionCommotion]

I am trying to implement a composite primary key with MySQL InnoDB where one of the keys is incremental relative to the other.  For instance, the below table (PS.  How can I make it look better?) shows id1 as the incrementing column and ad2 as the non-incrementing column.

 

SQL's auto_increment won't work as it produces a single series and not an individual series relative to the other key.

 

I've looked into stored procedures and triggers, however, I don't believe they can solve the problem.  They can atomically insert an incrementing id, however, there appears to be no way for PHP to retrieve the new ID and be guaranteed that it is correct due to concurrency/consistency issues.

 

Can this be solved with brute force PHP?

 

id1	id2	Data
1	1	bla
2	1	bla
3	1	bla
1	2	bla
2	2	bla
4	1	bla
1	3	bla
3	2	bla

[requinix]

SQL's auto_increment won't work as it produces a single series and not an individual series relative to the other key.

Actually it does. Make the primary key be (id2,id1) and put auto_increment on id1.

 

Oh wait, you said InnoDB. And I assume moving that table to MyISAM isn't an option.

 

NEVERMIND.

 

...Maybe a trigger? Or a separate MyISAM table just to generate those IDs?

Edited July 7, 2016 by requinix

[benanamen]

I for one don't understand what you're trying to do. What is the overall task at hand that you think this is the answer for??

[Muddy_Funster]

this is bad.  why are you doing what you are doing?

 

IMHO You need to change your DB model.

 

That said: yes, if you know given conditions of this mock key system then you could code the values into the app level and push them into the DB.

If I knew what those constraints were I'd help you, but, thankfully, I don't.

 

Seriously though: fix the back-end now and save yourself much pain in the future.

[NotionCommotion]

this is bad.  why are you doing what you are doing?

 

IMHO You need to change your DB model.

 

That said: yes, if you know given conditions of this mock key system then you could code the values into the app level and push them into the DB.

If I knew what those constraints were I'd help you, but, thankfully, I don't.

 

Seriously though: fix the back-end now and save yourself much pain in the future.

 

Yes, I understand your concerns, but it seems to be proper sometimes.  For instance, let's say you had table accounts and table checks.  checks would then have a primary key which contained both the accounts primary key plus an auto-incrementing primary key.  Why is this bad?

 

EDIT.  the checks auto-incrementing key effectively becomes a natural key which can bring performance benefits.

Edited July 7, 2016 by NotionCommotion

[ginerjm]

The two auto-incrementing keys are completely independent. Don't see what your concern is.

 

Personally, I avoid AI keys like the plague.

[NotionCommotion]

Actually it does. Make the primary key be (id2,id1) and put auto_increment on id1.

 

Oh wait, you said InnoDB. And I assume moving that table to MyISAM isn't an option.

 

NEVERMIND.

 

...Maybe a trigger? Or a separate MyISAM table just to generate those IDs?

 

Hey, you just stole my solution of using a separate MyISAM table to generate the IDs!  I actually started to reply to your post to say thank you for giving me the clue when you brought up MyISAM, and see you spelled it out.  For my case, there are many more queries than inserts, so I think it is acceptable.

 

Trigger won't return the inserted key.  Stored procedures could, but not 100% guaranteed.

 

Thanks!

[NotionCommotion]

Okay, this question really belongs in the MySQL forum, but it is just a carry on from above.

 

I've always thought MyISAM supported composite primary keys, but have never tried it.  Well, I tried it, and it doesn't work.  What am I doing wrong?

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| accounts       |
| checks         |
+----------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN accounts;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.01 sec)

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

mysql> SHOW CREATE TABLE accounts;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| accounts | CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE checks;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                               |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| checks | CREATE TABLE `checks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accounts_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`accounts_id`),
  KEY `fk_checks_accounts_idx` (`accounts_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO accounts(id) VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO accounts(id) VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO accounts(id) VALUES(0);
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO checks(accounts_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(accounts_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(accounts_id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(accounts_id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(accounts_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(accounts_id) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checks;
+----+-------------+
| id | accounts_id |
+----+-------------+
|  1 |           1 |
|  2 |           1 |
|  3 |           2 |
|  4 |           2 |
|  5 |           1 |
|  6 |           3 |
+----+-------------+
6 rows in set (0.00 sec)

mysql>

[requinix]

I really don't like them but a stored procedure might be best. A few lines to lock the table, calculate the IDs, insert, and return the ID(s)... then a few more to handle potential errors.

 

The separate table can do it, but either you move the IDs into it and always have to do a JOIN (or use a view), or you copy them and thus store the IDs in multiple places in the database.

 

Okay, this question really belongs in the MySQL forum, but it is just a carry on from above.

Huh. I hadn't noticed this was in PHP. Moving...

 

I've always thought MyISAM supported composite primary keys, but have never tried it.  Well, I tried it, and it doesn't work.  What am I doing wrong?

The column that should auto-increment goes last in the primary key. So (accounts_id, id). Edited July 7, 2016 by requinix

[NotionCommotion]

Thanks requinix,  Right now I have the stored in multiple places and am using a 1-to-1 fk.  Had me stumped with the order of the keys.

 

Maybe go back to a stored procedure.  I've never attempted to lock a table before, but suppose it is time to learn. 

[Jacques1]

So you want to spend hours on an obscure stored procedure, table locks and who-knows-what so that you can have a cumbersome composite key instead of simple sequential IDs?

 

Sorry, but none of this makes any sense whatsoever. I understand you find those “natural keys” pretty, but that doesn't mean you should suspend all rationality. A bad idea is a bad idea.