When creating a table in SQLite, you have the option of adding constraints to each column.

One such constraint is the DEFAULT constraint.

The DEFAULT constraint allows you to specify a value to be used in the event no value is supplied for that column when a new row is inserted.

If you don’t use a DEFAULT clause, then the default value for a column is NULL.

Possible Default Values

The explicit default value of a DEFAULT constraint can be any of the following:

• a string constant
• a blob constant
• a signed-number
• any constant expression enclosed in parentheses.
• CURRENT_TIME
• CURRENT_DATE
• CURRENT_TIMESTAMP
• NULL

Example

Here’s an example to demonstrate.

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName,
    Price DEFAULT 0.00
);

Here I added a DEFAULT constraint to the Price column. In this case, the default value is 0.00.

Now when I insert a new row without specifying a value for the Price column, the default value is used.

INSERT INTO Products ( ProductId, ProductName ) 
VALUES (1, 'Long Weight');

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Long Weight  0.0       

Explicit Values

Of course, the default value is only used when you don’t explicitly provide a value. If you do, then that value is used instead.

Here’s another example. This time I explicitly provide a value for that Price column.

INSERT INTO Products ( ProductId, ProductName, Price ) 
VALUES (2, 'Left-Handed Screwdriver', 19.99);

SELECT * FROM Products;

Result:

ProductId   ProductName                Price     
----------  -------------------------  ----------
1           Long Weight                0.0       
2           Left-Handed Screwdriver    19.99     

INSERT INTO Products ( ProductId, ProductName, Price ) 
VALUES (3, 'Elbow Grease', NULL);

SELECT * FROM Products;

ProductId   ProductName               Price     
----------  ------------------------  ----------
1           Long Weight               0.0       
2           Left-Handed Screwdriver   19.99     
3           Elbow Grease                        

Default Timestamp

In this example I create another table. This time I set the default value to the current timestamp.

CREATE TABLE Types( 
    TypeId INTEGER PRIMARY KEY, 
    Type,
    DateInserted DEFAULT CURRENT_TIMESTAMP
);

And now for some data.

INSERT INTO Types (Type) 
VALUES ( 'Hardware' );

SELECT * FROM Types;

Result:

TypeId      Type        DateInserted       
----------  ----------  -------------------
1           Hardware    2020-06-05 00:21:57

The timestamp is inserted as expected.

Auto-Incrementing Value as a Default Value

Notice that in the previous example I also didn’t provide a value for the TypeId column, but it automatically generated a default value anyway.

In this case, the default value wasn’t due to a DEFAULT constraint. It was due to the fact that the column is a primary key column that was created using INTEGER PRIMARY KEY. When you define a column using INTEGER PRIMARY KEY, the column automatically becomes an auto-increment column.

To demonstrate this more thoroughly, here’s what happens if I insert some more rows.

INSERT INTO Types (Type) 
VALUES 
  ( 'Software' ),
  ( 'Hardcopy' );

SELECT * FROM Types;

Result:

TypeId      Type        DateInserted       
----------  ----------  -------------------
1           Hardware    2020-06-05 00:21:57
2           Software    2020-06-05 00:22:14
3           Hardcopy    2020-06-05 00:22:14

For more information on auto-incrementing columns, see How AUTOINCREMENT works in SQLite, and How to Create an Auto-Incrementing Column in SQLite.

Expressions as a Default Value

You can also use a constant expression as the default value. To do this, the expression must be enclosed in parentheses.

Here’s an example.

CREATE TABLE Person( 
    PersonId INTEGER PRIMARY KEY, 
    Name,
    Entered DEFAULT (round(julianday('now'))),
    Deadline  DEFAULT (round(julianday('now')) + 10.5)
);

And now for some data.

INSERT INTO Person (Name) 
VALUES ( 'Fred' );

SELECT * FROM Person;

Result:

PersonId    Name        Entered     Deadline  
----------  ----------  ----------  ----------
1           Fred        2459006.0   2459016.5 

Note that the expression must be a constant expression. A constant expression is an expression that contains only constants.

If you try to use an expression that doesn’t contain only constants, you’ll get an error.

CREATE TABLE Person( 
    PersonId INTEGER PRIMARY KEY, 
    FirstName,
    LastName,
    UserName DEFAULT (FirstName || LastName)
);

Result:

Error: default value of column [UserName] is not constant