0

I have users table and posts table. A user has many posts.

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(10));

CREATE TABLE posts (id INT, user_id INT, text TEXT, PRIMARY KEY(id, user_id));

My question is, How do I create an automatically increment mechanism to increment the id of a user's post but scoped to the user (the user_id) only.

For example,

user 1 has 2 posts, the first post has id 1, the second post has id 2.

user 2 has 3 posts, the first post has id 1, the second post has id 2, the third post has id 3.

The rows of the posts table will look like this.

SELECT * FROM posts; user_id id text 1 1 'Post one from user 1' 2 1 'Post one from user 2' 2 2 'Post two from user 2' 2 3 'Post three from user 2' 1 2 'Post two from user 1'

I know this can be done by implementing it on the application level on two trips to the database, such as querying the last post id of a particular user and creating the post based on that. But this can introduce collision in a multi-user environment.

Christian Sakai
  • 929
  • 3
  • 9
  • 25

1 Answers1

0

I might not be understanding your question properly, but you can increment the id for the post when doing the insert pretty easily by using a subquery in your insert.

INSERT INTO posts (user_id, id, text) VALUES
(userIdVar, (SELECT max(id) + 1 FROM posts WHERE user_id = userIdVar), postText);
BShaps
  • 1,344
  • 7
  • 17
  • Thank you!. I think this should work! I'm not aware that this is possible. Do I need to wrap this in a transaction? Or these are atomic already? – Christian Sakai Apr 24 '18 at 18:42
  • Unsolved for now, since I think this query is not atomic. If two processes access the post row at the same time this will create race condition. – Christian Sakai Apr 24 '18 at 20:36
  • How is it possible that a user can post two messages at the exact same time? If you're concerned about races, you can use `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` although that can make your transactions more expensive. – BShaps Apr 24 '18 at 20:55
  • @ChristianSakai also `SELECT max(id) + 1 FROM posts WHERE user_id = userIdVar` won't see uncommited transactions so it's possible that you get the same id numbers.. iam not sure `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` will fix this or not. – Raymond Nijland Apr 24 '18 at 21:17
  • @BShaps There will be several admins that tried to do the same thing like the user (i.e., create, edit, delete, etc). Sorry it wasn't clear in my original problem statement. – Christian Sakai Apr 24 '18 at 22:46
  • @BShaps @Raymond Nijland I searched for other SO thread, and some answer do say `TRANSACTION ISOLATION LEVEL SERIALIZABLE` https://stackoverflow.com/questions/15750301/how-to-guarantee-atomic-sql-inserts-with-subqueries – Christian Sakai Apr 24 '18 at 22:48