Tell me more ×
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.
up vote 0 down vote favorite

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

1 Answer

up vote 4 down vote

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 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 at 13:55

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.