I have an AUTO_INCREMENT on a column. Example: when I eliminate Maria, the AUTO_INCREMENT id continues at 4 and not at 3. Is there a way to not do that and take 3 again?
ID | NOMBRE
1 | JOSE
2 | LUIS
3 | MARIA (X Elimino a maria)
4 | TAVO
I have an AUTO_INCREMENT on a column. Example: when I eliminate Maria, the AUTO_INCREMENT id continues at 4 and not at 3. Is there a way to not do that and take 3 again?
ID | NOMBRE
1 | JOSE
2 | LUIS
3 | MARIA (X Elimino a maria)
4 | TAVO
As far as I know, there isn't. It is not recommended that there be. Why? For a very simple reason, many times the column
ID
is the one that is used as a foreign key to relate toTAVO
with other tables. If you try to hitTAVO
theID
extinctMARIA
one and there are relationships in say 3 or 10 or 100 tables in the database, just to have a nice order of 1,2,3... in your autoincrementing column, you'll have to undertake a difficult, dark and dangerous job that would go through:ID
;ID
fromTAVO
in the other 100 related tables and in each of the records in those tables where itTAVO
appears, changing theID 4
to theID 3
;Speaking of referential integrity , it's a concept that would be important to understand if you're going to work with databases. If the table you present is intended to be related to other tables, the correct thing to do would be to declare referential integrity rules indicating to the handler what should happen when a row is updated or deleted.
If those rules are correct, you
MARIA
would have aON DELETE CASCADE
, which means that deletingMARIA
su would deleteID
all tables where it is a foreign key, so that what are known as orphaned records are not recorded in those tables . Having erasedMARIA
those records that only had a sense of existence because they were related to her, she should disappear. Otherwise you would have what is known as data inconsistency .In my answer to the question What does CASCADE, SET NULL, RESTRICT, NO ACTION mean in PHPMyAdmin? I tried to explain in a graphic way, what happens with referential integrity . It is important to understand how it works if you are going to work with relational databases.