SQLite | Tables | DEFAULT Constraint

Tags:

• SQLite
• Database

When a row omits a column value, SQLite normally stores NULL. A DEFAULT constraint lets you store a predefined value instead. This article explains how to use it.

What Is a DEFAULT Constraint?

A DEFAULT constraint supplies a value when an inserted row omits that column. The syntax is as follows.

CREATE TABLE table_name (column_name DEFAULT value, ...);

The default may be NULL, a number, or a string.

Create the following table. Both name and price have DEFAULT constraints.

create table product (id integer, name text default 'no name', price integer default 0);

sqlite> create table product (id integer, name text default 'no name', price integer default 0);
sqlite> 

First, insert rows that explicitly provide values for the columns. SQLite stores the supplied values.

insert into product values (1, 'PC', 75000);
insert into product values (4, 'Desk', 18000);

sqlite> insert into product values (1, 'PC', 75000);
sqlite> insert into product values (4, 'Desk', 18000);
sqlite> 

If an insert omits name, SQLite stores its default value, no name.

insert into product (id, price) values (6, 18000);

sqlite> insert into product (id, price) values (6, 18000);
sqlite> 
sqlite> .mode column
sqlite> .header on
sqlite> 
sqlite> select * from product;
id          name        price     
----------  ----------  ----------
4           Desk        18000     
1           PC          75000     
6           no name     18000     
sqlite> 

Likewise, if an insert omits price, SQLite stores its default value of 0.

insert into product (id, name) values (8, 'Chair');

sqlite> insert into product (id, name) values (8, 'Chair');
sqlite> 
sqlite> select * from product;
id          name        price     
----------  ----------  ----------
4           Desk        18000     
1           PC          75000     
6           no name     18000     
8           Chair       0         
sqlite> 

A DEFAULT constraint therefore stores the specified default instead of NULL when no value is supplied.

Using the Insertion Date as the Default

The following values can make the current date or time the column default when a row is inserted.

Value	Format
CURRENT_TIME	HH : MM : SS
CURRENT_DATE	YYYY-MM-DD
CURRENT_TIMESTAMP	YYYY-MM-DD HH : MM : SS
The time zone is UTC.

Create the following table with CURRENT_TIMESTAMP as the default for date_time.

create table user (id integer, name text, date_time default CURRENT_TIMESTAMP);

sqlite> create table user (id integer, name text, date_time default CURRENT_TIMESTAMP);
sqlite> 

Insert rows while omitting the date_time column.

insert into user (id, name) values (1, 'devkuma');
insert into user (id, name) values (3, 'kimkc');

sqlite> insert into user (id, name) values (1, 'devkuma');
sqlite> insert into user (id, name) values (3, 'kimkc');
sqlite> 
sqlite> select * from user;
id          name        date_time          
----------  ----------  -------------------
1           devkuma     2019-10-22 14:43:07
3           kimkc       2019-10-22 14:43:08
sqlite> 

The date_time column contains the date and time at which each row was inserted.