title: SQLite complains about null values in a column lacking a NOT NULL constraint
body: <p>I'm getting a curious error when inserting a record into a SQLite table.
  This is from the logs of a customer:</p>
  <pre class="lang-rb prettyprint-override"><code>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 ...
  </code></pre>
  <p>The pertinent bit of diagnostics is obviously this:</p>
  <pre><code>SQLite Error 19: 'NOT NULL constraint failed: Events.details'
  </code></pre>
  <p>…which seems clear enough, except for one thing: The <code>details</code> column lacks a <code>NOT NULL</code> constraint:</p>
  <pre class="lang-sql prettyprint-override"><code>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 )
  </code></pre>
  <p>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.</p>
  <p>But I'm curious:</p>
  <ul>
  <li>Is it possible that SQLite's diagnostics are in error, and that it is one of the
  <code>NOT NULL</code> columns that is the actual culprit?</li>
  <li>Why don't my unit tests have a problem with null <code>detail</code> values?</li>
  <li>Can this be related to SQLite's dynamic approach to typing?</li>
  <li>…or something completely different?</li>
  </ul>
  <p>Any ideas?</p>
  <h3>Environment</h3>
  <ul>
  <li>Targeting .NET Framework 4.8 on Windows 10</li>
  <li>Using <code>Microsoft.Data.Sqlite</code> 5.0.14 <br />
  (not <code>System.Data.SQLite</code> – though I'm unsure whether the distinction is relevant).</li>
  </ul>
  <h3>Code</h3>
  <p>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.</p>
  <pre class="lang-cs prettyprint-override"><code>using System;
  using Microsoft.Data.Sqlite;
  
  class SqliteDemo
  {
      static void Main()
      {
          SqliteDemo demo = new SqliteDemo();
          demo.LogEvent(&quot;name 1&quot;, DateTime.Now, 42, &quot;details&quot;, &quot;message 1&quot;, 0);
          demo.LogEvent(&quot;name 2&quot;, DateTime.Now, 42, null, &quot;message 2&quot;, 0); //*** Works...
      }
  
      readonly string connectionString = new SqliteConnectionStringBuilder
      {
          DataSource = &quot;Test.db&quot;,
          Mode = SqliteOpenMode.ReadWriteCreate,
      }.ConnectionString;
  
      const string CreateTableSql =
          @&quot;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
          )&quot;;
  
      int LogEvent(string name, DateTime time,
              int code, string details, string message, short status)
      {
          using (SqliteConnection connection = CreateOpenConnection())
          {
              using (SqliteCommand command = CreateCommand(connection,
                  &quot;INSERT INTO Events(name, time, code, details, message, status) &quot; +
                  &quot;VALUES(@name, @time, @code, @details, @message, @status)&quot;))
              {
                  AddParameter(command, &quot;name&quot;, name);
                  AddParameter(command, &quot;time&quot;, time);
                  AddParameter(command, &quot;code&quot;, code);
                  AddParameter(command, &quot;details&quot;, details);
                  AddParameter(command, &quot;message&quot;, message);
                  AddParameter(command, &quot;status&quot;, 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();
          }
      }
  }
  </code></pre>