Menu
I am creating application that uses MYSQL database in C#. I want to delete row and update autoincremented value of id in table. For example, I have table with two columns: id and station, and table is station list. Something like this
id station
1 pt1
2 pt2
3 pt3
If i delete second row, after deleting the table looks something like this:
id station
1 pt1
3 pt3
Is there any way that I update id of table, for this example that id in third row instead value 3 have value 2? Thanks in advance!
995
What will be the auto increment ID of delete and truncate? Using TRUNCATE TABLE Statement. The TRUNCATE TABLE statement in MySQL completely deletes the table's data without removing a table's structure and always resets the auto-increment column value to zero.
You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.
You can use a pair of statements: DROP TABLE and CREATE TABLE to reset the auto-increment column. Note that this method delete all data from the table permanently. Like the TRUNCATE TABLE statement, those statements drop the table and recreate it, therefore, the value of the auto-increment is reset to zero.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
An autoincrement column, by definition, should not be changed manually.
What happen if some other tables use this ID (3) as foreign key to refer to that record in this table? That table should be changed accordingly.
(Think about it, in your example is simple, but what happen if you delete ID = 2 in a table where the max(ID) is 100000? How many updates in the main table and in the referring tables?)
And in the end there is no real problem if you have gaps in your numbering.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
