How to use CREATE TABLE ... AS with Primary Key in SQLite

Question

From SQLite documentation for CREATE TABLE http://www.sqlite.org/lang_createtable.html:

A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind.

So is there any general way to create table with primary key & other index information ?

Answer 1

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
 PHIL INTEGER PRIMARY KEY,
 SUCKS INTEGER NOT NULL
);
INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!

• Gea-Suan Lin: I have a template table and I want to copy its schema, so I need to use CREATE TABLE AS.
• Philᵀᴹ: You can't do it with CTAS. If you want PKs etc, you'll have to pre-create, then insert. It's trivial to get the DDL for an existing table.
• Rethunk: At no point today did I even consider that a post about SQLite would make me laugh. @PhilTM, you win the day (eleven years later).

Answer 2

The sqlite_master table holds all info on tables and indexes of the database. Assuming the existing table is called oldtable, you can retrieve the table definition from sqlite_master as:

SELECT sql
FROM sqlite_master
WHERE type = 'table' AND name = 'oldtable';

In the retrieved sql field, replace the oldtable with newtable and run the SQL statement against the connection.

Then you can retrieve the indexes by:

SELECT name, sql
FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'oldtable';

Given that the index names should be unique in the database, mangle the name field to something not used, substitute *mangled_name* for name and newtable for oldtable in the sql text and run the updated SQL statement of each index.

Then you can just run:

INSERT INTO newtable SELECT * FROM oldtable;