SQLite add Primary Key

I created a table in Sqlite by using the CREATE TABLE AS syntax to create a table based on a SELECT statement. Now this table has no primary key but I would like to add one.

Executing ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...) gives a syntax error "near PRIMARY"

Is there a way to add a primary key either during table creation or afterwards in Sqlite?

By "during creation" I mean during creation with CREATE TABLE AS.

You can't modify SQLite tables in any significant way after they have been created. The accepted suggested solution is to create a new table with the correct requirements and copy your data into it, then drop the old table.

here is the official documentation about this: http://sqlite.org/faq.html#q11

As long as you are using CREATE TABLE, if you are creating the primary key on a single field, you can use:

CREATE TABLE mytable (
field1 TEXT,
field2 INTEGER PRIMARY KEY,
field3 BLOB,
);

With CREATE TABLE, you can also always use the following approach to create a primary key on one or multiple fields:

CREATE TABLE mytable (
field1 TEXT,
field2 INTEGER,
field3 BLOB,
PRIMARY KEY (field2, field1)
);

Reference: http://www.sqlite.org/lang_createtable.html

This answer does not address table alteration.

I tried to add the primary key afterwards by changing the sqlite_master table directly. This trick seems to work. It is a hack solution of course.

In short: create a regular (unique) index on the table, then make the schema writable and change the name of the index to the form reserved by sqlite to identify a primary key index, (i.e. sqlite_autoindex_XXX_1, where XXX is the table name) and set the sql string to NULL. At last change the table definition itself. One pittfal: sqlite does not see the index name change until the database is reopened. This seems like a bug, but not a severe one (even without reopening the database, you can still use it).

Suppose the table looks like:

CREATE TABLE tab1(i INTEGER, j INTEGER, t TEXT);

Then I did the following:

BEGIN;
CREATE INDEX pk_tab1 ON tab1(i,j);
pragma writable_schema=1;
UPDATE sqlite_master SET name='sqlite_autoindex_tab1_1',sql=null WHERE name='pk_tab1';
UPDATE sqlite_master SET sql='CREATE TABLE tab1(i integer,j integer,t text,primary key(i,j))' WHERE name='tab1';
COMMIT;

Some tests (in sqlite shell):

sqlite> explain query plan select * from tab1 order by i,j;
0|0|0|SCAN TABLE tab1 USING INDEX sqlite_autoindex_tab1_1
sqlite> drop index sqlite_autoindex_tab1_1;
Error: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped