I'm currently working on a small charity database for an education. The database holds information for sponsors and students and they all have an ID which is inputted with the auto increment feature so I don't have to worry about manually inputting the ID each time and making sure that it's the next number along.
The auto increment feature works fine however if I delete one of the records there's then a break in the numbers as shown below.
This would be fine and wouldn't bother me if it didn't cause seemingly unavoidable error messages. In this database I also have a function that would let the user update already existing data. The error message that appears is when you try to update some already existing data on a row where the auto increment ID isn't currently the highest ID in the table. The error message reads " Update to students failed: you must satisfy the constraint 'Students pkey' ". For example, in the screen shot below I'm only able to update information on the student with the ID 92 and if I try to update any of information in any of the other rows it comes up with the error message mentioned previously. Additionally, if I add another student that will then make it impossible to update the student with ID 92 as it's no longer the highest ID number.
I was wondering if someone could give me some advice or pointers because I really feel like I've hit a brick wall here and I've no idea what I can do to fix it.
Also, whenever I make a new student the ID given is always the next highest number that it's ever had. For example, if I had students with IDs from 81-90 but they no longest exist because I deleted them, the next student I would add would be given the ID 91 causing a massive break which is the reason for the massive break in the screen shots previously shown. Is there a way to fix this or is this just how it is? I won't be as bothered if I can't fix this because it doesn't seem to be causing me any actual problems but if there is a way to fix it I would like to.
It turns out that it's not whoever has the highest ID number it's whoever is the lowest entry in the list. For example, I just filtered the student ID column so it's highest to lowest meaning that student ID 1 is in the place of student ID 92 and student ID 1 is now the only student I can update information for.
Firstly, it is standard for ID in a SQL database table to auto-increment for every new row, irrespective of whether preceding rows have been deleted from a table. This is for a few reasons:
Consistency: auto-incrementing IDs are designed to be unique and sequential, providing a consistent and predictable way to uniquely identify each row in a table. If IDs were allowed to be reused after rows are deleted, it could lead to duplicate IDs or gaps in the sequence, which could result in data integrity issues or cause problems with applications that rely on the IDs for referencing and linking data.
Simplicity: Keeping the auto-incrementing ID consistent and always incrementing simplifies the implementation of the feature in the database engine. It avoids the need for complex logic to track and manage reused IDs or gaps in the sequence, which could add unnecessary overhead and complexity to the database engine's implementation.
Performance: The auto-incrementing ID is typically implemented using a simple counter or sequence, which can be efficiently managed by the database engine. Allowing reused IDs or gaps in the sequence would require additional logic and bookkeeping, which could impact performance and increase the overhead of managing the IDs.
You could configure an ID column to fill in the gaps or restart from the highest integer, but this is inadvisable. Alternatively, you could implement "soft-deletions", whereby you don't actually delete any rows with the DELETE statement, but instead use UPDATE to add a timestamp to a "deleted_at" column in the row you want to "delete". Then in all your SELECT queries, you would include WHERE deleted_at ISNULL such that soft-deleted rows never appear anywhere. To your app users, it's as if they were deleted entirely.
To diagnose the issue you're having with updating existing rows, it would be helpful if you could share the code of your UPDATE query. I'll be glad to take a look! The problem should not arise because your IDs are auto-incrementing - it's almost certainly something else.
The code is the exact same in the sponsor query (of course with the exception of "studentTable" being replaced with "sponsorTable" and all other appropriate changes made in that nature) and both tables behave in the same way. On the right under "event handlers" you can see the query being called.
Also just in case it's at all important I'll show you my "getStudents" query too as that's under the success event handlers at the bottom. I don't think this should be part of the problem as the delete and add queries also have this in the success event handles section and they work fine but I'll show you anyway just in case.
I appreciate the insight into the auto increment ID situation. Any further insight into this update records problem will also be a great help. Thank you in advance!
What happens if you use {{ studentTable.recordUpdates[0].std_student_id }} instead of {{ studentTable.selectedRow.data.std_student_id }} as the value you're filtering on?
The error you must satisfy the constraint 'Students pkey' usually indicates that there would be a duplicate value in a primary key column. Since the selected row of the table doesn't necessarily match the first entry of the recordUpdates array it could mean that the query is trying to update the wrong row, leading to duplicate ids:
