SQLite: Insert Into Identity Column

Question

I'm new to SQLite and I'm trying to run the following code:

using (var cn = new SQLiteConnection("Data Source=:memory:;Version=3;")) {
    cn.Open();

    var cmd = cn.CreateCommand();
    cmd.CommandText = "CREATE TABLE [ContentItems] ([Id] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](100) NOT NULL, CONSTRAINT [PK_ContentItems] PRIMARY KEY ([Id]))";
    cmd.ExecuteNonQuery();

    var cmd2 = cn.CreateCommand();
    cmd2.CommandText = "INSERT INTO [ContentItems] (Title) VALUES ('Test 1')";
    cmd2.ExecuteNonQuery();
}

But this gives the error:

Abort due to constraint violation ContentItems.Id may not be NULL

I've had a look through the documentation but based on my past SQL experience I cannot see why this doesn't work. I'd appreciate the help. Thanks

An explicit autoincrementing ID column usually isn't necessary in SQLite: http://www.sqlite.org/autoinc.html — reinierpost, Oct 08 '12 at 17:22
It is strange that you accepted an answer with invalid code: https://www.db-fiddle.com/f/tkKtGFcaWkLgWigm7YiaKC/0 — forpas, Jan 20 '23 at 16:11

Larry Lustig · Accepted Answer · 2023-01-20T19:20:46.730

5

SQLite creates what you're thinking of as an IDENTITY column using the AUTOINCREMENT keyword. In your example, the ID column is not created with AUTOINCREMENT and, since it is declared NOT NULL, you must supply a value.

The CREATE statement you're after is:

 CREATE TABLE [ContentItems] (
    [Id] INTEGER AUTOINCREMENT NOT NULL, 
    [Title] TEXT NOT NULL, 
    CONSTRAINT [PK_ContentItems] PRIMARY KEY ([Id])
 )

Note that SQLite uses the TEXT data type (although it will map anything containing "CHAR" to that datatype) and doesn't need or respect maximum field lengths.

edited Jan 20 '23 at 19:20

answered Oct 08 '12 at 16:45

Larry Lustig

49,320
14
110
160

1

Thanks that would explain why i couldn't find any documentation on IDENTITY. – nfplee Oct 08 '12 at 20:35
Please fix the provided SQL statement as you have two primary keys there... – Vladimir Arkhipov Jan 20 '23 at 13:46
The code in your answer does not even run: https://www.db-fiddle.com/f/tkKtGFcaWkLgWigm7YiaKC/0 – forpas Jan 20 '23 at 16:12

SQLite: Insert Into Identity Column

1 Answers1

Linked