---
title: "Enforce SQLite column types with a STRICT table in C#"
url: https://recursiveai.org/onelite/cookbook/enforce-types-with-sqlite-strict-table/
description: "Create a SQLite STRICT table in C# with OneLite. See why an INTEGER column rejects nonnumeric text but stores a numeric string as an integer."
version: "1.0.0"
---

# Enforce SQLite column types with a STRICT table in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/enforce-types-with-sqlite-strict-table/enforce-types-with-sqlite-strict-table.zip)

A `STRICT` table rejects values that can’t be converted losslessly to the column’s declared type.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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.

```text
Rejected: many
12 (integer)
```

Unzip [the example](https://recursiveai.org/onelite/cookbook/enforce-types-with-sqlite-strict-table/enforce-types-with-sqlite-strict-table.zip), then `dotnet run`.

## The full program

```csharp
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);
```

```text
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"`.

```csharp
await db.ExecuteAsync($"CREATE TABLE stock(qty ANY) STRICT");
```

```text
many (text)
12 (text)
1.5 (text)
```

## Related recipes

- [Create a SQLite database in C#](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)
- [Run SQLite transactions in C#](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)

## 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](https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes).

## 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](https://www.sqlite.org/stricttables.html) 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.
