Search code examples
sqliteprimary-keyalter-table

alter table drop column syntax error using sqlite

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.

Solution

  • Up to version 3.35, SQLite did not support ALTER TABLE DROP COLUMN statements. You could only rename table, or add columns.

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

    As of 2021, ALTER TABLE support is still limited but includes dropping a column, under conditions.