SQLite - DEFAULT

The SQLite DEFAULT constraint is used to set the default value for a column. The column takes default value when a new record is inserted without specifying any value.

SQLite DEFAULT constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which DEFAULT constraint is applied on column City.

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

The DEFAULT constraint can also be used to insert system values, by using functions like CURRENT_TIMESTAMP:

CREATE TABLE Orders (
  OrderID INT NOT NULL,
  OrderQuantity INT NOT NULL,
  OrderPrice DECIMAL(18,2),
  OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

SQLite DEFAULT constraint with ALTER TABLE

SQLite does not support ALTER TABLE statement to add DEFAULT constraint to the existing table. However this can be achieved using following steps:

• 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 added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce DEFAULT constraint on City column of the existing table Employee, 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) DEFAULT 'London',
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

DROP DEFAULT constraint

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, to drop DEFAULT constraint from City column of the existing table Employee, 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;