CODEWITHSUNDEEP
.netsqlitesystem.data.sqlite
tapok
tapok

Reputation: 15

Unknown error in CREATE statement when using column constraint

I'm trying to create SQLite table using System.Data.SQLite library under .NET Framework 4.7.2 with statement

CREATE TABLE IF NOT EXISTS account_accessibility (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL DEFAULT 'lk3' CHECK (name IN ('lk3', 'api', 'err')),
    created TEXT NOT NULL DEFAULT datetime('now'),
    notified INTEGER NOT NULL DEFAULT 0 CHECK (notified IN (0, 1)),
    closed TEXT,
    id_sm TEXT
 );

and getting an error:

SQL logic error near "(": syntax error

How to fix this proplem? SQLite version: 3.32.1.

Upvotes: 0

Views: 51

Answers (1)

shmnff
shmnff

Reputation: 739

You should use DEFAULT operator with parentheses when using with expression:

CREATE TABLE IF NOT EXISTS account_accessibility (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL DEFAULT 'lk3' CHECK (name IN ('lk3', 'api', 'err')),
    created TEXT NOT NULL DEFAULT (datetime('now')),
    notified INTEGER NOT NULL DEFAULT 0 CHECK (notified IN (0, 1)),
    closed TEXT,
    id_sm TEXT
);

See documentation.

Upvotes: 1

Related Questions