CODEWITHSUNDEEP
sqlite
CppLearner
CppLearner

Reputation: 17040

ALTER COLUMN in sqlite

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!

Upvotes: 113

Views: 164198

Answers (7)

Beefster
Beefster

Reputation: 789

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.

Upvotes: 0

Timothy Gonzalez
Timothy Gonzalez

Reputation: 770

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

Upvotes: 3

tanmoy
tanmoy

Reputation: 1438

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:

Upvotes: 0

Brian Alex Sepulveda Nuñez
Brian Alex Sepulveda Nuñez

Reputation: 79

  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!

Upvotes: 7

Noah
Noah

Reputation: 15340

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

Upvotes: 69

Rajesh
Rajesh

Reputation: 784

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.

Upvotes: 35

Alex Jasmin
Alex Jasmin

Reputation: 39516

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

Upvotes: 144

Related Questions