Search code examples
sqlsqlitecreate-table

What is in the error in this SQLite statement?

I got this error: SQL error (near "(": syntax error)

create table IF NOT EXISTS demo
(
    code varchar(200) default substr(lower(hex(randomblob(32))),1,6) not null primary key
);

What is wrong with my code?

Solution

  • If you use DEFAULT with an expression instead of a literal value, it has to be enclosed in parentheses.

    DEFAULT (substr(...))

    From the documentation:

    The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes.