I’ve been working on a backend for a personal project and it uses sqlite3 with the mattn/go-sqlite3 driver. Basically there’s a table called tutors created with the following:
CREATE TABLE IF NOT EXISTS tutors(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
firstname VARCHAR NOT NULL,
lastname VARCHAR NOT NULL,
email VARCHAR NOT NULL
);
And so far I got the CREATE and READ functions working. I’ve now been working on the DELETE request of a tutor. As of now, it does deletes a tutor; but all the other tutors aren’t updating their id's. What can I do to update the id's of other tutors after deleting one?
Also, if there are any other general remarks on the project that you would give, please go ahead! I’m super open-minded and constantly looking to learn.
I’m just getting started with go, but I have working experience with SQLite. It would be unusual for your primary id field to change upon deleting a record. This has two implications:
Viewing your table, you will be able to see gaps where records have been deleted. That’s ok.
An ID number will never change for a record. If record number 5 is especially important to you now, you can always refer to record number 5 in the future, and know it’s the same record. If the primary key field data changed every time you add or delete data, you could never trust that the data in record number 5 is the same as the last time you looked at it.
Is there a reason that you want to shift the numbers in your id field around when you delete a row?
There’s no particular reason. I’m completely new to working with databases so I sort of assumed that records were supposed to update after a delete and didn’t realize that gaps could be a norm. Reading your first implication that gaps where records have been deleted is ok tells me that the primary keys don’t need to change This is good information for me. Thank you!