SQLite DROP DEFAULT Keyword

SQLite does not support ALTER TABLE or DROP statement to drop an existing DEFAULT constraint from a table. To achieve this, the following steps can be used:

The foreign key constraint should be checked off
Rename the table to some other name
Create a new table with same structure but DEFAULT constraint dropped
Copy the data from renamed table to new table
In the end, turn on the foreign key constraints

For example, consider a table called Employee which is created using following structure:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100) DEFAULT 'London',
  Age INT,
  Salary DECIMAL(18,2)
);

To drop DEFAULT constraint from City column of the Employee table, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

❮ SQLite Keywords