114

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!

Improve this question
2
  • you ask for an ALTER COLUMN syntax, but you don't say what you want to do. That makes me think that this is too broad. ALTER COLUMN could do a lot, are you looking to drop the not null constraint like in the pg example? Commented Sep 16, 2018 at 11:04
  • if your used intellj db tools, when you change the colum it would generate the commands for your sqlite. Commented Aug 14, 2019 at 1:34

8 Answers 8

Reset to default
145

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This answer encounters additional challenges when there are foreign key constraints in other tables that rely on the table being modified.
While technically true, there are tools to do this that are much easier. See my response.
This answer exactly describes INCORRECT way to modify table schema in SQLite. Specifically, one should rename the new temporary table that was created, not original one. See “7. Making Other Kinds Of Table Schema Changes” for detailed process in the documentation sqlite.org/lang_altertable.html
69

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:\> **sqlite3 booktest**  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> **create table BOOKS ( title TEXT NOT NULL, publication_date TEXT NOT 
NULL);**  
sqlite> **insert into BOOKS VALUES ("NULLTEST",null);**  
Error: BOOKS.publication_date may not be NULL  
sqlite> **PRAGMA writable_schema = 1;**  
sqlite> **UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT 
NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';**  
sqlite> **PRAGMA writable_schema = 0;**  
sqlite> **.q**  

Y:\> **sqlite3 booktest**  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> **insert into BOOKS VALUES ("NULLTEST",null);**  
sqlite> **.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.

ALTER TABLE SYNTAX

Improve this answer

1 Comment

This method worked for me, although to avoid situations in which the columns might be in a different order (i.e. from a previous ADD COLUMN command), I used: UPDATE SQLITE_MASTER SET SQL = replace(SQL, '[MyColumn] integer NOT NULL', '[MyColumn] integer NULL') WHERE NAME = 'MyTable'. Also, be careful not to run this as part of a transaction - it may prevent some of the earlier transaction commands from running.
35

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.

  1. BEGIN TRANSACTION;
  2. CREATE TEMPORARY TABLE t1_backup(a,b);
  3. INSERT INTO t1_backup SELECT a,b FROM t1;
  4. DROP TABLE t1;
  5. CREATE TABLE t1(a,b);
  6. INSERT INTO t1 SELECT a,b FROM t1_backup;
  7. DROP TABLE t1_backup;
  8. COMMIT

For more detail you can refer the link.

Improve this answer

2 Comments

To rename table: ALTER TABLE table1 RENAME TO table2;
Don't forget indexes. Run .schema to generate create statement with indexes.
7

  1. CREATE TABLE temp_Table(x,y[,etc]);

  2. INSERT INTO temp_Table SELECT * FROM Table;

  3. DROP TABLE Table;

  4. ALTER TABLE temp_Table RENAME TO Table;

Thanks for helping me to find a definitive method!

Improve this answer

Comments

5

I suggest you use DB Browser for SQLite. You can easily change constraints with it. For example in DB Browser for SQLite you can go to Database Structure -> Right Click your table -> Click Modify Table -> and uncheck the NN (Not Null) column then click ok.

enter image description here

Improve this answer

1 Comment

The actual answer. Thank you! With removing "NN" you usually set "default NULL"; is there also an option to do this ?
0

ALTER COLUMN does not exist in SQLite.

Only Supported alter operations:

  • Alter Table Name
  • Alter Table Column Name
  • Add New Column
  • Drop Column

Alex Jasmin's answer shows possible way

Reference:

Improve this answer

Comments

0

If all you're doing is changing the type of the column, then you often won't need to alter the column because SQLite is dynamically typed and you can put data other than the type of the column; column types are more of a suggestion than anything else. There are some quirks that come with the column's type (coercion rules) that you may need to work around, but the database will happily accept data of the "wrong" type.

In all practicality, you're not generally going to be changing a column's type all that drastically (in typical databases, you would often only be changing the size of the type- for instance VARCHAR(16) -> VARCHAR(32) - not the category of type), so the only thing that is going to be a struggle to work around is NOT NULL constraints- in which case you can use one of the other helpful answers here in order to achieve what you're looking for.

Improve this answer

Comments

0

Trying to fix the answer marked as correct in this question:

This answer did not work for me because renaming the table directly instead of creating a new table gave me problems related to foreign key constraints, the next solution worked the best for me:

  • First deactivate foreign keys temporally to avoid problems while handling the structure change PRAGMA foreign_keys = OFF;.

  • Then create a new table with a different name from the table that you want to alter and with the column change that you want to make CREATE TABLE new_books_book (...);.

  • Then move all the data from the old table to the new one, in my case INSERT INTO new_books_book (a,b,c) SELECT a, b, c FROM books_book; kinda operation worked for me, you may need to adapt the data to fit the new table structure.

  • Then delete the old table DROP TABLE books_book;.

  • Then rename the new table to the name of the old one ALTER TABLE new_books_book RENAME TO books_book;.

  • Finally, enable foreign keys again to make everything work as usual PRAGMA foreign_keys = ON;.

Reference:

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.