SQLite complains about null values in a column lacking a NOT NULL constraint

Question

I'm getting a curious error when inserting a record into a SQLite table. This is from the logs of a customer:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: 'NOT NULL constraint failed: Events.details'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at SqliteDemo.LogEvent(...) in ...

The pertinent bit of diagnostics is obviously this:

SQLite Error 19: 'NOT NULL constraint failed: Events.details'

…which seems clear enough, except for one thing: The details column lacks a NOT NULL constraint:

CREATE TABLE IF NOT EXISTS Events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name STRING NOT NULL,
    time INTEGER NOT NULL,
    code INTEGER NOT NULL,
    details STRING,
    message STRING NOT NULL,
    status INTEGER NOT NULL )

I don't know the values that failed to insert, so an obvious next step would be to improve my diagnostics and wait for it to happen again. Or I could just add null guards to make sure it never happens again and forget about the whole thing.

But I'm curious:

• Is it possible that SQLite's diagnostics are in error, and that it is one of the NOT NULL columns that is the actual culprit?
• Why don't my unit tests have a problem with null detail values?
• Can this be related to SQLite's dynamic approach to typing?
• …or something completely different?

Any ideas?

Environment

• Targeting .NET Framework 4.8 on Windows 10
• Using Microsoft.Data.Sqlite 5.0.14
  (not System.Data.SQLite – though I'm unsure whether the distinction is relevant).

Code

I can't post our production code here. Instead, here is a complete, stand-alone C# program that I believe closely parallels the production code in all pertinent aspects.

using System;
using Microsoft.Data.Sqlite;

class SqliteDemo
{
    static void Main()
    {
        SqliteDemo demo = new SqliteDemo();
        demo.LogEvent("name 1", DateTime.Now, 42, "details", "message 1", 0);
        demo.LogEvent("name 2", DateTime.Now, 42, null, "message 2", 0); //*** Works...
    }

    readonly string connectionString = new SqliteConnectionStringBuilder
    {
        DataSource = "Test.db",
        Mode = SqliteOpenMode.ReadWriteCreate,
    }.ConnectionString;

    const string CreateTableSql =
        @"CREATE TABLE IF NOT EXISTS Events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name STRING NOT NULL,
            time INTEGER NOT NULL,
            code INTEGER NOT NULL,
            details STRING,
            message STRING NOT NULL,
            status INTEGER NOT NULL
        )";

    int LogEvent(string name, DateTime time,
            int code, string details, string message, short status)
    {
        using (SqliteConnection connection = CreateOpenConnection())
        {
            using (SqliteCommand command = CreateCommand(connection,
                "INSERT INTO Events(name, time, code, details, message, status) " +
                "VALUES(@name, @time, @code, @details, @message, @status)"))
            {
                AddParameter(command, "name", name);
                AddParameter(command, "time", time);
                AddParameter(command, "code", code);
                AddParameter(command, "details", details);
                AddParameter(command, "message", message);
                AddParameter(command, "status", status);

                return command.ExecuteNonQuery(); //*** SOMETIMES throws
            }
        }
    }

    static void AddParameter(SqliteCommand command, string name, object value)
    {
        command.Parameters.Add(new SqliteParameter(name, value ?? DBNull.Value));
    }

    static SqliteCommand CreateCommand(SqliteConnection connection, string sql)
    {
        SqliteCommand command = connection.CreateCommand();
        command.CommandText = sql;
        return command;
    }

    SqliteConnection CreateOpenConnection()
    {
        SqliteConnection connection = new SqliteConnection(connectionString);
        connection.Open();
        CreateTableIfNecessary(connection);
        return connection;
    }

    void CreateTableIfNecessary(SqliteConnection connection)
    {
        using (SqliteCommand command = CreateCommand(connection, CreateTableSql))
        {
            command.ExecuteNonQuery();
        }
    }
}

Answer 1

From your code, it looks like you are passing message to your procedure, and in one case its value is null. But in a create table script you have a constraint message STRING NOT NULL which throws an error on insert (as null is not allowed to message column). Try to put empty string instead of null and see if the same error occurs.