How can I turn the rowid column into a regular column?

Question

I create a table and insert a row :

CREATE TABLE people (first_name text NOT NULL, last_name text NOT NULL );
INSERT INTO people (first_name, last_name) VALUES ('John', 'Doe');

I execute:

SELECT rowid as ID, first_name, last_name FROM people

and get three fields as expected. However, I want to turn the automatic rowid column into a regular column so the IDs of the rows do not change as they are removed, etc. How can I do this?

I tried :

ALTER TABLE people ADD people_id INTEGER;
ALTER TABLE people ADD PRIMARY KEY (people_id);

The first statement succeeds, but the second one gives me an error:

near "PRIMARY": syntax error: ALTER TABLE people ADD PRIMARY

If I had created the table as:

CREATE TABLE people (people_id INTEGER PRIMARY KEY, first_name text NOT NULL, last_name text NOT NULL );

I would have, from the start, had the kind of table I want.

It is important the rowid value is maintained in the people_id column. For example, John Doe has rowid 47 in people. After the transformation people_id for John Doe needs to be 47.

I can copy the data from one table to another, but wonder if there is a better way.

score 2 · Answer 1 · answered Oct 14 '18 at 17:47

You'd have to create a new table, copy everything over, drop the old table, and rename the new one:

CREATE TABLE new_people(people_id INTEGER PRIMARY KEY
                      , first_name TEXT NOT NULL,
                      , last_name TEXT NOT NULL);
INSERT INTO new_people(first_name, last_name) SELECT first_name, last_name FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;

score 2 · Accepted Answer · answered Oct 14 '18 at 19:14

Shawn's answer is sound (and I've upvoted it), but I'll offer a variation on that which I think is slightly neater in that the new table does not end up in the schema with quotes:

TRANSACTION;
ALTER TABLE people RENAME TO old_people;
CREATE TABLE people(people_id INTEGER PRIMARY KEY
                  , first_name TEXT NOT NULL,
                  , last_name TEXT NOT NULL);
INSERT INTO people(people_id, first_name, last_name)
            SELECT rowid, first_name, last_name FROM old_people;
DROP TABLE old_people;
COMMIT;
VACUUM;

score 0 · Answer 3 · answered Oct 14 '18 at 20:41

As long as the actual storage format of the table does not change, you can use an extremely dangerous backdoor to change the table definition:

PRAGMA writable_schema = on;

UPDATE sqlite_master
SET sql = 'CREATE TABLE people(people_id INTEGER PRIMARY KEY, first_name text NOT NULL, last_name text NOT NULL )'
WHERE type = 'table'
  AND name = 'people';

-- and now re-open the database file

score -1 · Answer 4 · answered Sep 25 '22 at 16:41

-1

This creates a new column and then set it equal to the rowid:

ALTER TABLE mytable ADD COLUMN id INTEGER;
UPDATE mytable SET id=rowid;

answered Sep 25 '22 at 16:41

robertspierre

3,218
2
31
46

How can I turn the rowid column into a regular column?

4 Answers4