sql - How to insert a new row into the table has unique id autoincrement?

Question

After creating the table with a unique ID autoincrement, I realize my table lack a row. But I don't know how to do this without compromising the order of other rows in the table!

TABLE flights
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  origin TEXT NOT NULL,
  destination TEXT NOT NULL,
  duration INTEGER NOT NULL

I want to insert a row: 2|Shanghai|Paris|760 into the table with id = 2.

1|New York|London|415
2|Istanbul|Tokyo|700
3|New York|Paris|435
4|Moscow|Paris|245
5|Lima|New York|455

Table I wished:

1|New York|London|415
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700
4|New York|Paris|435
5|Moscow|Paris|245
6|Lima|New York|455

Thanks for any advice to me!

question from:https://stackoverflow.com/questions/65888758/how-to-insert-a-new-row-into-the-table-has-unique-id-autoincrement

Answer 1

No way you can do this with auto-increment ID because IDS are not to order rows, but to identify the rows and assert it's the only row with that ID. If you want to, use a new specific column for this purpose, this way the IDs still the same and you can sort using anything as indexes.

CREATE TABLE flights (
  id INTEGER AUTO_INCREMENT,
  index INTEGER NOT NULL,
  origin TEXT NOT NULL,
  destination TEXT NOT NULL,
  duration INTEGER NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY unique_index (index)
);