Set default value of an integer column in SQLite
DatabaseSqliteDatabase Problem Overview
I am creating an SQLite database.
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " ("
+ KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ KEY_NAME + " TEXT NOT NULL, "
+ KEY_WORKED + " INTEGER, "
+ KEY_NOTE + " INTEGER);");
Is it possible to set the default value of KEY_NOTE
(which is an integer) for every row created to be 0
(zero)? If so, what should be the correct code?
Database Solutions
Solution 1 - Database
Use the SQLite keyword default
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " ("
+ KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ KEY_NAME + " TEXT NOT NULL, "
+ KEY_WORKED + " INTEGER, "
+ KEY_NOTE + " INTEGER DEFAULT 0);");
This link is useful: http://www.sqlite.org/lang_createtable.html
Solution 2 - Database
A column with default value:
CREATE TABLE <TableName>(
...
<ColumnName> <Type> DEFAULT <DefaultValue>
...
)
<DefaultValue>
is a placeholder for a:
- value literal
(
expression)
Examples:
Count INTEGER DEFAULT 0,
LastSeen TEXT DEFAULT (datetime('now'))
Solution 3 - Database
It happens that I'm just starting to learn coding and I needed something similar as you have just asked in SQLite (I´m using [SQLiteStudio] (3.1.1)).
It happens that you must define the column's 'Constraint' as 'Not Null' then entering your desired definition using 'Default' 'Constraint' or it will not work (I don't know if this is an SQLite or the program requirment).
Here is the code I used:
CREATE TABLE <MY_TABLE> (
<MY_TABLE_KEY> INTEGER UNIQUE
PRIMARY KEY,
<MY_TABLE_SERIAL> TEXT DEFAULT (<MY_VALUE>)
NOT NULL
<THE_REST_COLUMNS>
);