I have two tables, Threads and Posts and currently they both have id as auto-incremented integer which is primary key, so when a user adds a new thread, its id will be 1, then for a new post id will be 1 as well. And I want the id for that post to be 2, and for new post/thread 3 and so on.
I'm using Postgesql, thank you for any advice.
Asked
Active
Viewed 85 times
0
Jerry
- 1
-
2Don't do this. Threads and posts are different entities. Their primary keys should not be confused with each other. – Gordon Linoff Feb 24 '19 at 20:50
2 Answers
0
If i understood correct, you want to avoid the id 1 for the first new post, correct? the easiest way to do it is to just delete the first line manually from the table post and since it is auto-incremented integer it will not create 1
Thomas_krk
- 214
- 1
- 8
-
Not really. I want to have one auto-incrementing pull of id's for both threads and posts. So for the first entity (despite being a post or a thread) id will be 1, for second 2 and so on. It's like having one id column but for two tables. – Jerry Feb 24 '19 at 22:17
-
It dose not sound correct (for the future) to have unique id across two tables but one way to do it is instead of auto-incremented you can check the last ID value of both tables and add ID+1 to the table you want or you can check this link https://stackoverflow.com/questions/16961580/how-to-have-unique-ids-across-two-or-more-tables-in-mysql – Thomas_krk Feb 24 '19 at 22:26
0
I'd advise you to let the primary key be auto-incremented. In case, it's compulsory for your requirement, you can retrieve the maximum id of the other table, add 1 to it and insert this value in the id column of the current table, before adding any new row. Code for retrieving the maximum id:
select max(id) from threads
lenikhilsingh
- 553
- 2
- 7
- 20
-
id's are already auto-incrementing, but they are separate for threads and posts. It seems like your solution works only for adding posts, but when adding a thread I'll also have to check max id from posts AND threads, get it and increment. Just was wondering if there is any prebuilt way to do this in sql. – Jerry Feb 24 '19 at 22:23
-
No, there isn't any inbuilt method to do so in sql. You have to implement the same logic for both threads as well as posts. I wrote threads' example because i assumed you'd figure out how to do it for posts using my example. – lenikhilsingh Feb 27 '19 at 12:36