CODEWITHSUNDEEP
auto-incrementmysql
Eliezer
Eliezer

Reputation: 7347

Have more than one AutoIncrement Per Table

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    |
----------------------------------------------------

Upvotes: 1

Views: 5814

Answers (4)

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 1

James C
James C

Reputation: 14149

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.

Upvotes: 1

landoncz
landoncz

Reputation: 2017

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.

Upvotes: 1

Alex
Alex

Reputation: 7374

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

Upvotes: 2

Related Questions