5

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 ?

Gea-Suan Lin
  • 375
  • 1
  • 2
  • 6

2 Answers2

9

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!

Philᵀᴹ
  • 31,762
  • 10
  • 83
  • 107
  • 1
    I have a template table and I want to copy its schema, so I need to use CREATE TABLE AS. – Gea-Suan Lin Apr 28 '13 at 13:53
  • 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. – Philᵀᴹ Apr 28 '13 at 13:55
  • At no point today did I even consider that a post about SQLite would make me laugh. @Philᵀᴹ, you win the day (eleven years later). – Rethunk Mar 20 '24 at 19:04
1

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;
tzot
  • 131
  • 5