SQLite alter column to remove constraint

Question

I have a column with a NOT NULL constraint. I need to alter the database to remove the constraint. But when I try I get a duplicate column exception from android

db.execSQL("ALTER TABLE " + AnimalContract.DogEntry.TABLE_NAME
+ " ADD COLUMN " + AnimalContract.DogEntry.COLUMN_NAME+" TEXT DEFAULT NULL");

I also tried without DEFAULT

The constraint was UNIQUE NOT NULL

score 4 · Answer 1 · edited Feb 04 '20 at 19:17

There's no direct way to 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

Furthermore,

MODIFY COLUMN IN TABLE

You can not use the ALTER TABLE statement to modify a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.

Syntax

The syntax to MODIFY A COLUMN in a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2)
 SELECT column1, column2
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

Let's look at an example that shows how to modify a column in a SQLite table.

For example, if we had an employees table that had a column called last_name that was defined as a CHAR datatype:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name CHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to change the datatype of the last_name field to VARCHAR, we could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, hire_date)
  SELECT employee_id, last_name, first_name, hire_date
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the last_name field defined as a VARCHAR datatype. Then it will insert all of the data from the _employees_old table into the employees table.

SQLite alter column to remove constraint

1 Answers1