mysql id auto increment not working

[tantony]

I deleted ID 2 from mySQL db, but then I have the following to AUTO INCREMENT

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="Users"
)

mycursor = mydb.cursor()
SQL = "ALTER TABLE users MODIFY id INTEGER NOT NULL AUTO_INCREMENT"
mycursor.execute(SQL)
mydb.commit()
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

But when I do this, it displays the database, but its not AUTO INCREMENT, ing. the ID goes from 1-3, it skips 2.

SQL = "SELECT * FROM users"

Output:
('Peter', 'Lowstreet 4', 1)
('Hannah', 'Mountain 21', 3)
('Michael', 'Valley 345', 4)
('Sandy', 'Ocean blvd 2', 5)

[deanhystad]

From the documentation: https://www.sqlite.org/autoinc.html

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

As you can see, the purpose of AUTOINCREMENT is to prevent reusing a key value, even for keys that are no longer in the table.

[tantony]

ok, I thought AUTO_INCREMENT will 'auto increment'. Is that because I deleted the record by id, and not something like a name?

So there's no way to reset so it will be 1,2,3,4,5?

[tantony]

Because I deleted it by name, WHERE name = 'Michael', and commit.

But now its deleted 4,

Output:
('Peter', 'Lowstreet 4', 1)
('Hannah', 'Mountain 21', 3)
('Sandy', 'Ocean blvd 2', 5)

[deanhystad]

Why do you want to reassign id's when you delete a record? What is id supposed to represent? If you reassign id each time you modify the database it makes id meaningless.

[tantony]

I'm learning mySQL, how do I reassign all the id keys so they're in order?

[deanhystad]

Reassigning key values so they are sequential is not something anyone would do. The purpose of a key is to be a unique value that can be used to reference a paricular row, not to be a counter.

[tantony]

ok thank you

[deanhystad]

Everything might make more sense if you don't think of a table as something that has rows and columns. A table is a collection of records where each record has the same fields (row = record, column = field). The order of the records in the table is not meant to have any meaning.

[tantony]

Thank you again, makes sense