CODEWITHSUNDEEP
sqliteado.net
user34537
user34537

Reputation:

Sqlite setting the default value in create table

I wrote something like

create table if not exists QuickTest (
id integer primary key NOT NULL,
a TEXT DEFAULT @0,
b TEXT,
c TEXT);

I get an error on @0. Is there a way to insert parameters here or do i need to hardcode the value in? I typically like using parameters when setting values.

Upvotes: 5

Views: 19727

Answers (3)

chandan
chandan

Reputation: 2418

CREATE TABLE test (id INTEGER DEFAULT -1, name VARCHAR DEFAULT na)

Above sql will create a table test with two colums id and name While executing insert for this table if no values are specified for id and name then it will insert -1 for id column and na will be inserted for name column

Upvotes: 1

tuinstoel
tuinstoel

Reputation: 7306

You can't use parameters in a ddl (data definition language) statement, only in dml (data manipulation language) statement and select statements are parameters allowed.

Upvotes: 5

pierrotlefou
pierrotlefou

Reputation: 40721

You have to use string constant or NULL.

http://www.sqlite.org/syntaxdiagrams.html#column-constraint

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

Upvotes: 7

Related Questions