Menu
A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression: The expression "CAST(4.0 AS INT)" returns an integer 4, whereas "CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.
The INTEGER values in SQLite are stored in either 1, 2, 3, 4, 6, or 8 bytes of storage depending on the value of the number.
Based on the SQL standard, PRIMARY KEY should always imply NOT NULL . However, SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.
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
A column with default value:
CREATE TABLE <TableName>(
...
<ColumnName> <Type> DEFAULT <DefaultValue>
...
)
<DefaultValue> is a placeholder for a:
( expression )
Examples:
Count INTEGER DEFAULT 0,
LastSeen TEXT DEFAULT (datetime('now'))
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>
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With