alter table drop column syntax error using sqlite

sqlite primary-key alter-table
leba-lev picture leba-lev · Jun 7, 2011 · Viewed 13.6k times · Source

This is the schema of my table:

create table LPCG(ID integer primary key, PCG text, Desc text, test text);

I wish to drop the column "test", and hence use the command:

alter table LPCG drop column test;

This is the error message I get:

Error: near "drop": syntax error

Can someone please help me correct my error?

An additional question is: I understand that ID is the primary key attribute. Would I be able to drop that column? If not, is there a workaround which anyone has used?

Thanks in advance for any help.

Answer

Benoit picture Benoit · Jun 7, 2011

SQLite does not fully support ALTER TABLE statements. You can only rename table, or add columns.

If you want to drop a column, your best option is to create a new table without the column, and to drop the old table in order to rename the new one.

Related questions

Sqlite primary key on multiple columns

What is the syntax for specifying a primary key on more than 1 column in SQLITE ?

Read More
How to retrieve the last autoincremented ID from a SQLite table?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text). I have a table Users with columns username (primary key, text) and Hash. A message is sent by one Sender (user) to many recipients (user) and …

Read More
SQL - Inserting a row and returning primary key

I have a little witty problem. Say, I inserted a row with some data in a table where a primary key is present. How would one "SELECT" the primary key of the row one just inserted? I should have been …

Read More