[SOLVED] How can I turn the rowid column into a regular column?

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

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.

Solution

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;