Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

2 Answers 2

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!

share|improve this answer
    
I have a template table and I want to copy its schema, so I need to use CREATE TABLE AS. –  gslin 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

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.