Inserting a row at the specific place in SQLite database

Question

I was creating the database in SQLite Manager & by mistake I forgot to mention a row.

Now, I want to add a row in the middle manually & below it the rest of the Auto-increment keys should be increased by automatically by 1 . I hope my problem is clear.

Thanks.

Answer 1

You shouldn't care about key values, just append your row at the end.

If you really need to do so, you could probably just update the keys with something like this. If you want to insert the new row at key 87

Make room for the key

update mytable
set key = key + 1
where key >= 87

Insert your row

insert into mytable ...

And finally update the key for the new row

update mytable
set key = 87
where key = NEW_ROW_KEY

Answer 2

I would just update IDs, incrementing them, then insert record setting ID manually:

CREATE TABLE cats (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR
);
INSERT INTO cats (name) VALUES ('John');
INSERT INTO cats (name) VALUES ('Mark');
SELECT * FROM cats;

| 1 | John |
| 2 | Mark |

UPDATE cats SET ID = ID + 1 WHERE ID >= 2; -- "2" is the ID of forgotten record.
SELECT * FROM cats;

| 1 | John |
| 3 | Mark |

INSERT INTO cats (id, name) VALUES (2, 'SlowCat'); -- "2" is the ID of forgotten record.
SELECT * FROM cats;

| 1 | John    |
| 2 | SlowCat |
| 3 | Mark    |

Next record, inserted using AUTOINCREMENT functionality, will have next-to-last ID (4 in our case).