I am using Django and Django Rest Framework for the backend of my Student Management System. The database is going to be postgresql. I want to give a unique student number to each student that gets admission(let's call that STD_ID). I have searched around and all the answers suggest getting the highest value of the STD_ID column from the database and saving the new record by incrementing the highest value. That works fine but there is a problem.
The issue is let's say the last entered student has the STD_ID 100, now the next one will be 101 all good but what happens when I delete the student with STD_ID 100. Now the highest STD_ID will be 99 and the next student will be 100. It won't create a conflict in the table because it does not remember having STD_100 but in physical records, lets say student reports and all that which were issued to previous STD_ID 100 students will exist and the new student will also be issued reports and such in the same STD_ID.
So I guess you see the issue here. There are now two students with the same ID. One is no longer in the database but he used to be and one day it can cause problems.
What I want to do is have a way to assign STD_ID without this issue. One way I can think of is having a second table which stores the last student id issued so that the next student is saved with a unique id regardless of whether student has been deleted or not.
But, I am not sure how this system would handle multiple users. Let's say two users try to save a student record at the same time, they will get new unique numbers but for some reason one of the requests to save the record fails and now there is a gap in the STD_ID.
So, how is this handled in the industry because STD_ID does not seem like a big issue but just assume we are dealing with invoice numbers and a number is skipped in the sequence. Well, that would be a mess to sort out in internal records and auditors will get unnecessary suspicion. Any guidance will be much appreciated.
PS. I am still learning all this on my own and this is I think my first question on StackOverflow. So, I apologise for any blunders or mistakes, any advice on asking questions and suggestions will be highly appreciated. Thank you