Jump to content

Mimicking autoincrement primary key using PHP


NotionCommotion

Recommended Posts

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
Link to comment
Share on other sites

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 by requinix
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 checkschecks 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 by NotionCommotion
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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 by requinix
Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.