ALTER COLUMN in sqlite

Question

How do I alter column in sqlite  This is in Postgresql  ALTER TABLE books book ALTER COLUMN publication date DROP NOT NULL    I believe there is no ALTER COLUMN in sqlite at all  only ALTER TABLE is supported   Any idea  Thanks

User · Answer

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table. But you can alter table column datatype or other property by the following steps.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT

For more detail you can refer the link.

User · Answer

While it is true that the is no ALTER COLUMN  if you only want to rename the column  drop the NOT NULL constraint  or change the data type  you can use the following set of dangerous commands   PRAGMA writable schema   1  UPDATE SQLITE MASTER SET SQL    CREATE TABLE BOOKS   title TEXT NOT NULL  publication date TEXT   WHERE NAME    BOOKS   PRAGMA writable schema   0    You will need to either close and reopen your connection or vacuum the database to reload the changes into the schema   For example   Y   gt    sqlite3 booktest     SQLite version 3 7 4   Enter   help  for instructions   Enter SQL statements terminated with a       sqlite gt    create table BOOKS   title TEXT NOT NULL  publication date TEXT NOT  NULL       sqlite gt    insert into BOOKS VALUES   NULLTEST  null       Error  BOOKS publication date may not be NULL   sqlite gt    PRAGMA writable schema   1      sqlite gt    UPDATE SQLITE MASTER SET SQL    CREATE TABLE BOOKS   title TEXT NOT  NULL  publication date TEXT   WHERE NAME    BOOKS       sqlite gt    PRAGMA writable schema   0      sqlite gt     q      Y   gt    sqlite3 booktest     SQLite version 3 7 4   Enter   help  for instructions   Enter SQL statements terminated with a       sqlite gt    insert into BOOKS VALUES   NULLTEST  null       sqlite gt     q       REFERENCES FOLLOW     pragma writable schema When this pragma is on  the SQLITE MASTER tables in which database can be changed using ordinary UPDATE  INSERT  and DELETE statements  Warning  misuse of this pragma can easily result in a corrupt database file    alter table  From http   www sqlite org lang altertable html  SQLite supports a limited subset of ALTER TABLE  The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table  It is not possible to rename a column  remove a column  or add or remove constraints from a table

User · Answer

There s no ALTER COLUMN in sqlite   I believe your only option is to    Rename the table to a temporary name Create a new table without the NOT NULL constraint Copy the content of the old table to the new one Remove the old table   This other Stackoverflow answer explains the process in details

[sqlite] ALTER COLUMN in sqlite

Examples related to sqlite