A STRICT table rejects values that can’t be converted losslessly to the column’s declared type.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneLite; // SQLITE_CONSTRAINT_DATATYPE const int datatype = 3091; await using var db = await Database.OpenAsync(":memory:"); await db.ExecuteAsync($""" CREATE TABLE stock(qty INTEGER) STRICT """); foreach (var input in new[] { "many", "12" }) { try { await db.ExecuteAsync($""" INSERT INTO stock VALUES({input}) """); } catch (OneLiteException ex) when (ex.ExtendedCode == datatype) { Console.WriteLine($"Rejected: {input}"); } } var value = await db.ScalarAsync<string>($""" SELECT qty || ' (' || typeof(qty) || ')' FROM stock """); Console.WriteLine(value);
OneLite binds interpolated values like {value} as SQL parameters rather than joining them into the SQL text; ExecuteAsync, ScalarAsync, and QueryAsync require the $ prefix even when there are no values to bind.
Prints the rejected input, then the value and its stored type.
Rejected: many 12 (integer)
Unzip the example, then dotnet run.
The full program
using RecursiveAI.OneLite; const int constraintDatatype = 3091; // SQLITE_CONSTRAINT_DATATYPE await using var db = await Database.OpenAsync(":memory:"); await db.ExecuteAsync($"CREATE TABLE stock(qty INTEGER) STRICT"); foreach (var input in new[] { "many", "12", "1.5" }) { try { await db.ExecuteAsync($"INSERT INTO stock(qty) VALUES({input})"); } catch (OneLiteException ex) when (ex.ExtendedCode == constraintDatatype) { Console.WriteLine($"Rejected: {input}"); } } var rows = await db.QueryAsync<string>($""" SELECT qty || ' (' || typeof(qty) || ')' FROM stock ORDER BY rowid """); foreach (var row in rows) Console.WriteLine(row);
Rejected: many Rejected: 1.5 12 (integer)
The full program adds a third string, "1.5", to show that even numeric text can fail when it can’t be stored as an integer without losing information.
A failed conversion throws OneLiteException with extended code 3091 (SQLITE_CONSTRAINT_DATATYPE). The program catches that code and lets other errors propagate.
Variations
Preserve the incoming type. Replace the table declaration in the full program with this one. ANY in a STRICT table keeps the three inputs as text, including "12".
await db.ExecuteAsync($"CREATE TABLE stock(qty ANY) STRICT");many (text) 12 (text) 1.5 (text)
Related recipes
The short answer
Put STRICT after the closing parenthesis of CREATE TABLE, and give each column a supported type. For an INTEGER column, OneLite accepts the C# string "12" because SQLite’s type rules can convert it to an integer. It rejects "many" because there is no integer equivalent. Use typeof(qty) to see what was actually stored.
Strictness belongs to the table, not the database or the C# connection. Another table in the same database can still use ordinary SQLite typing. If you already have a table you want to make strict, plan to recreate it with the new declaration and copy the rows, checking for incompatible values before replacing the old table. See SQLite’s table rebuild procedure.
What does STRICT mode do to SQLite type affinity?
Type affinity is the preference for a column to store values of a particular type. In an ordinary INTEGER column, SQLite tries to convert numeric text, but it can keep nonnumeric text as text. In a STRICT table, SQLite still tries the conversion, then rejects values that don’t fit. The SQLite STRICT table reference calls this a lossless conversion. It means STRICT checks what can be stored, not whether the application supplied a C# int to begin with.
Type checks are separate from rules about which values make sense. If the stock quantity can’t be negative, add CHECK (qty >= 0). If it must be supplied, add NOT NULL; STRICT on its own doesn’t make an ordinary column required.
What data types can SQLite STRICT tables use?
The allowed declarations are INT, INTEGER, REAL, TEXT, BLOB, and ANY. For example, use TEXT rather than VARCHAR(40): strict tables don’t accept VARCHAR as a declared type, and a length rule is a separate constraint.
Use ANY for a field that genuinely needs mixed types. The variation keeps "12" distinct from the number 12, which matters if a text value carries leading zeros. Use TEXT instead when every stored value should be text. If another application opens the same database with SQLite, it should use SQLite 3.37.0 or later to recognize and enforce the STRICT declaration.