how to reset an incremental Id number value when a row is deleted in a SQL db

Getting Started Newbie Questions
#1

Hello everybody,

I’ve create a table in SQL to save object name, position, etc

every row is an object and the first column is the id (that is INCREMENTAL)…

the save process works and I’m able to show the table correctly,

now I’ve insert a button in every rows that let you delete the row and it works

but when I try to create a new object the id number is always the one after the last.

Exemple:

id  |   objectName  |

1          object1

2          object2

3          object3

4          object4

what I’d like to see is that after delete the row number 3, the id numeber of the row number 4 become 3…

how can I do? Thank you 

#2

Hi - you can’t do that in most databases, unfortunately, although there are good reasons for this. ID numbers are usually set up to be completely unique and, uhm, what’s the right word… Immutable? The idea is that you need a way to uniquely and completely foolproofly a database entry. If you would allow an entry with id number 3 to have a certain value, and then later on insert another entry with this same id number it could lead to uncertainties.

But is there really a problem? When you do a query on the database, you get a nice ordered list as a result, without “gaps”. If needed you can sort by date created, if you add a column for date created.

If this still doesn’t work for you, you need to update the database table everytime you delete an entry, with your own code, to set the values correctly in a sort of ID column.

But the “real” basic ID column is impossible to change in most databases, in order to be able to completely uniquely address a row entry.

#3

I think I’d like to add to this in that it helps to understand the basic concept of MVC or Model-View-Controller. It can be some confusing terms, but to simplify things, the “Model” is the database or database table. It’s your data, how it’s defined. Then you have the view, or how the data is displayed. The controller is the glue in the middle.

How it’s important here is if you want to display sequential numbers, you do that as part of the view. Let’s say you’re using a widget.newTableView() to show the data, when you insert the rows into the tableView you can give that row a number that’s not tied to the database’s unique ID. It could be something like:

View No Database ID No Description 1 1 Record #1 2 2 Record #2 3 4 Record #4 (3 was deleted) 4 7 Record #7, but 4th in the view

The database ID’s don’t need to map 1:1 to the numbers you might show in the view if you don’t want it to appear to have gaps.

Rob

#4

I’m speaking as a MySQL/Maria developer here and currently have no experience with databases in Corona, so apologies if this turns out not to be supported by whatever dB engine you’re using, but the following for example would reset the auto_inc to 10 ready for the next insert:

ALTER TABLE tblName AUTO_INCREMENT = 10;

You could therefore technically do this after every delete op, but if deleting a record from the middle of a table you’d need to then shift all the other records down one before the reset, which is an unnecessary strain. Additionally the purpose of ID’s like this is for building relations between tables, so if you mess with them you’re potentially breaking those relations. I.e. if you have a table of names and a table of addresses, the names table might reference the ID of an address in the addresses table so if you shift those records you’re changing which address is attached to those names.

Very bad idea to mess with auto_inc fields in this way. They’re indexed internally anyway so there’s a solutely zero performance gain in closing those gaps.

#5

Hi - you can’t do that in most databases, unfortunately, although there are good reasons for this. ID numbers are usually set up to be completely unique and, uhm, what’s the right word… Immutable? The idea is that you need a way to uniquely and completely foolproofly a database entry. If you would allow an entry with id number 3 to have a certain value, and then later on insert another entry with this same id number it could lead to uncertainties.

But is there really a problem? When you do a query on the database, you get a nice ordered list as a result, without “gaps”. If needed you can sort by date created, if you add a column for date created.

If this still doesn’t work for you, you need to update the database table everytime you delete an entry, with your own code, to set the values correctly in a sort of ID column.

But the “real” basic ID column is impossible to change in most databases, in order to be able to completely uniquely address a row entry.

#6

I think I’d like to add to this in that it helps to understand the basic concept of MVC or Model-View-Controller. It can be some confusing terms, but to simplify things, the “Model” is the database or database table. It’s your data, how it’s defined. Then you have the view, or how the data is displayed. The controller is the glue in the middle.

How it’s important here is if you want to display sequential numbers, you do that as part of the view. Let’s say you’re using a widget.newTableView() to show the data, when you insert the rows into the tableView you can give that row a number that’s not tied to the database’s unique ID. It could be something like:

View No Database ID No Description 1 1 Record #1 2 2 Record #2 3 4 Record #4 (3 was deleted) 4 7 Record #7, but 4th in the view

The database ID’s don’t need to map 1:1 to the numbers you might show in the view if you don’t want it to appear to have gaps.

Rob

#7

I’m speaking as a MySQL/Maria developer here and currently have no experience with databases in Corona, so apologies if this turns out not to be supported by whatever dB engine you’re using, but the following for example would reset the auto_inc to 10 ready for the next insert:

ALTER TABLE tblName AUTO_INCREMENT = 10;

You could therefore technically do this after every delete op, but if deleting a record from the middle of a table you’d need to then shift all the other records down one before the reset, which is an unnecessary strain. Additionally the purpose of ID’s like this is for building relations between tables, so if you mess with them you’re potentially breaking those relations. I.e. if you have a table of names and a table of addresses, the names table might reference the ID of an address in the addresses table so if you shift those records you’re changing which address is attached to those names.

Very bad idea to mess with auto_inc fields in this way. They’re indexed internally anyway so there’s a solutely zero performance gain in closing those gaps.