Have more than one AutoIncrement Per Table

Question

Hey Everybody,
I'm trying to make a table that will have a bunch of ids referring to a user, and for each user there will be multiple posts that should also have their own ids. My question is how do I make the table so that when a record is inserted for a certain user, their post id should be incremented by 1. Meaning user 1 will have posts 1,2,3, and 4, user 2 will have posts 1,2,3,4,5,6, and 7, etc...

event_id is the primary key for the table, FK_user_ID is the foreign key that maps back to a user table, post_id is which number the post for that user is (which is my problem column), and post is the post

----------------------------------------------------
|  event_id  |  FK_user_ID  |  post_id  |   post   |
|      1     |       1      |      1    |   hey    |
|      2     |       1      |      2    | you too  |
|      3     |       1      |      3    |   ok     |
|      4     |       2      |      1    |   foo    |
|      5     |       2      |      2    |   bar    |
----------------------------------------------------

Can you show, in ASCII-art, what you want the table to look like (once data's been added)? I don't think I understand how the entries relate to the users' posts... =/ — David Thomas, May 05 '11 at 15:15
Do you really need two autoincrements here? I believe `user` aldreay has an autoincrement in its own table. — Quassnoi, May 05 '11 at 15:15
@David I will put that up later today @Quassnoi I don't actually mean an auto-increment, rather something that will act as one — Eliezer, May 05 '11 at 19:01

score 2 · Accepted Answer · answered May 05 '11 at 15:14

2

Would you not require a separate table to store the posts per user? Then you can link them via the user id and use joins to get the information out when required:

---------------------
| User_ID | Post_ID |
|    1    |    1    |
|    2    |    2    |
|    2    |    3    |
|    3    |    4    |
|    4    |    5    |
---------------------

etc etc

answered May 05 '11 at 15:14

Alex

7,320
1
18
31

I wanted to try and avoid making another table because I'm under the impression that less is more. However, I am a bit of a noob/hack at MySQL so I really don't know. This is just for a little pet project but if I ever get more into MySQL, is adding another table more efficient than whatever way (if possible) there is to do what I asked above? – Eliezer May 05 '11 at 19:01
It is, especially when you use foreign keys allow speedy indexing. Table separation and joins are certainly not going to a have a significant speed impact on something like this, and I've always found it better and easier to separate out information like this. – Alex May 06 '11 at 08:08

score 1 · Answer 2 · answered May 05 '11 at 15:15

1

You can only have one auto-increment per table. If this is a homework assignment, the "outside the box" way to do this may be to create separate tables for each user, instead of one table with multiple columns. Then, when using separate tables, you can use auto-increment for each table. Then you would simply create a view to join the tables together for easy reading.

answered May 05 '11 at 15:15

landoncz

1,997
14
15

It's not exactly homework, more of a little project I'm doing for myself. I am interested in doing it what would be considered the "right way" if such a thing exists. – Eliezer May 05 '11 at 19:04

score 1 · Answer 3 · answered May 05 '11 at 15:17

I'm afraid I don't think you can have more than one AUTO_INCREMENT column per table. You can have it on a secondary column in a compound index as described here but you can't have more than one.

I think that you need to create a separate user table, have the user id (user.id) as a foreign key reference to post.user_id.

Now you can create a PRIMARY KEY on the post of (user_id, post_sequence) and define post_sequence with AUTO_INCREMENT in its column spec.

score 1 · Answer 4 · answered May 05 '11 at 15:18

With MyISAM, this is possible:

CREATE TABLE posts (user_id INT NOT NULL, post_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (user_id, post_id)) ENGINE=MyISAM;

INSERT
INTO    posts
VALUES
(1, NULL),
(1, NULL),
(1, NULL),
(2, NULL),
(2, NULL);

results in:

1, 1
1, 2
1, 3
2, 1
2, 2

Have more than one AutoIncrement Per Table

4 Answers4

Linked