id_usuario
is PK , NN and AI
At the beginning, it did start from 1, but I deleted all the records and via my Java program I saved new ones, but the records did not start from 1, they started from the last number of the records that I had deleted.
Why don't the records start from number 1? They start from the number of the records that you had deleted before.
This is because that field is AUTO_INCREMENT. When a field is AUTO_INCREMENT, they start counting from the number 1 but nevertheless if you delete records the next AUTO_INCREMENT, even if you have deleted it, will be the next to the record you have deleted.
For example if you have the records
1, 2, 3, 4, 5
and you delete record number5
the AUTO_INCREMENT will start at number6
for the next insert.Explanation of why the AUTO_INCREMENT field works that way:
I have seen that in the other answers they tell you how to put the field records
AUTO_INCREMENT
back to1
. However, the databases save the id of the last record added (even if you delete them) because it can generate security and data inconsistency problems.I do not recommend at all that you change the IDs of a field
AUTO_INCREMENT
. In case you do it, always do it in a test environment and making sure that it cannot affect related tables. Never run it in a production environment.Why? Very easy. Fields
AUTO_INCREMENT
are normally used to assign ID's to the elements of your tables, which ARE UNIQUE . Imagine a table of users registering in your application with the id fieldAUTO_INCREMENT
, for example, as a forum typeStackoverflow
.Now imagine there are three users in the database and the user with ID 1 creates three posts. Each post is associated with that user 1, therefore, the name of the creator will appear.
Subsequently, you delete the three users from the table and put the value of the field back
AUTO_INCREMENT
with the value 1.Now you create a new user and, having modified your AUTO_INCREMENT id field, this new user is registered in the database with the number 1. Therefore, the three posts that the previous user has created with the ID 1, go to be related to the new user ID 1, which is not correct.
For this reason, I do not recommend you to ever re-initialize the value of an AUTO_INCREMENT field unless you are sure that it will not affect the rest of the tables in the database in terms of data consistency (in the previous example you would have a data inconsistency since erroneous data would be displayed for a user, to whom you have assigned an ID that had already been assigned previously and which was related to other tables).
You can do it in two ways:
ALTER TABLE table_name AUTO_INCREMENT=1
TRUNCATE TABLE table_name;
Internally, the database manager keeps the record of the last Id that was autoincremented, to start from 1 again it is with the instruction
ALTER TABLE tablename AUTO_INCREMENT = 1