SQLite STRICT tables enforce stored types with explicit flexibility

A column declaration can describe an intention without enforcing the representation an application expects. SQLite 3.37.0 gives developers an explicit per-table choice about that boundary. The November 27 release introduces STRICT tables, which enforce supported declared types. It also checks certain newly added column constraints against existing rows. Both changes make a schema declaration a stronger statement about the data already present or about to be stored.

The distinction is more precise than a switch that rejects every conversion. The release's tests accept numeric text in integer columns when the value converts successfully. They reject text that cannot become the required number. An application gets a constraint on the stored representation without having to supply every value in exactly the representation the engine ultimately stores.

I value SQLite's flexibility, but some tables represent application contracts that should reject incompatible values at the database boundary. An opt-in table declaration fits that need. It allows the choice to follow the data model instead of making one policy mandatory for every table in a database.

The declaration has a restricted vocabulary

The STRICT table tests at the release tag establish the basic declaration rules. Every column needs a type. A declaration with no type fails, including when combined with WITHOUT ROWID. Unsupported type names fail as well. The tests reject DATE and TEXT(50), so familiar SQL-looking declarations are not automatically valid STRICT types.

The supported vocabulary comprises INT, INTEGER, REAL, TEXT, BLOB, and ANY, as demonstrated across the release's strict-table tests. That has consequences for schema generators. A generator that emits VARCHAR or a length-qualified text type cannot simply append STRICT and assume the result is accepted. The new mode has a specific grammar for declared types.

CREATE TABLE measurement (
  id INTEGER PRIMARY KEY,
  station TEXT NOT NULL,
  reading REAL
) STRICT;

This illustrative table separates two decisions. The type declaration constrains a stored value's kind, while NOT NULL separately requires a value for station. The tests allow NULL in typed columns without a not-null constraint. Calling a table strict therefore does not imply that every field becomes mandatory.

The release also adds PRAGMA table_list. A test reads its strict field for a newly created STRICT table and expects 1. Tools can therefore inspect the chosen table policy instead of trying to infer it from how a sample of rows happens to look. That matters when a database contains tables with different policies.

The tests also reject adding an untyped column or a VARCHAR column to an existing STRICT table. Its policy therefore continues to govern later schema additions, rather than applying only to the original CREATE TABLE statement.

Coercion still has a defined role

One test inserts integer values, numeric strings, whole-valued floating-point numbers, and nulls into integer columns. It expects integer results for the successfully converted numeric values. A separate insert of 1.2 fails because the nonintegral real value cannot be stored as an integer under this policy. Text such as xyz fails too.

The practical distinction is between a convertible input and an incompatible stored value. Numeric text can originate in a form or a serialized request. STRICT does not require the application to reject that representation merely because it arrived as text. It requires SQLite to produce an acceptable stored value or reject the write.

CREATE TABLE quantity(value INT) STRICT;
INSERT INTO quantity VALUES ('12');
SELECT typeof(value), value FROM quantity;

The result has the integer storage type and the value 12, following the numeric-text case in the tagged tests. Replacing the inserted text with twelve would produce an error. Replacing it with the real value 1.2 would also fail. These outcomes describe a type-enforcement boundary, not a general-purpose interpretation of arbitrary textual quantities.

Text columns have conversions of their own. The tests insert numbers into a STRICT text column and expect textual values. Blob input fails for that column. A STRICT blob column accepts a blob and null but rejects ordinary text. Applications that distinguish binary payloads from their textual encodings therefore receive a concrete database check instead of relying on a column name to communicate the difference.

The real-column tests accept integer inputs and numeric text, producing real values. Incompatible text and blob values fail. Taken together, these cases give a more useful model than assuming STRICT eliminates SQLite's conversion behavior. Conversion remains part of accepting a value, and enforcement determines whether the resulting representation is valid for the declaration.

ANY keeps mixed values explicit

The second strict-table test file creates a STRICT table with an integer column and an ANY column. It inserts integer, real, text, blob, and null values into ANY, then expects an integrity check to succeed. Mixed values are therefore possible inside a STRICT table when the schema declares that choice.

This is useful for a table whose stable fields have known types while one payload field legitimately holds several kinds of value. The application can enforce an integer field without pretending that every payload has the same representation. The permission is local to the declared column and remains visible in the schema.

I prefer that explicit exception to weakening an entire table's contract for one flexible field. It tells a reader where interpretation belongs in application code. It also avoids treating every unexpected value as evidence that the database should accept unrestricted types everywhere.

The presence of ANY does not define domain meaning by itself. A value can have an allowed storage type and still be invalid for the application's purpose. A numeric reading can be out of the accepted range, or a textual identifier can violate an external format. Type enforcement addresses representation. Additional constraints or application rules still define those domain conditions.

Integrity checks understand the table policy

The second test file deliberately arranges for data written through a non-strict declaration to be inspected under a strict declaration. This is test machinery, including direct schema manipulation, not a migration procedure to imitate. It gives the suite a way to create incompatible values that ordinary STRICT writes should refuse.

quick_check then reports values that violate the expected integer, text, real, or blob type. It also distinguishes allowed nulls from nulls in not-null columns. These expectations show that enforcement is not confined to accepting an individual insert. Database checking can assess stored values against the policy as well.

That distinction is useful when discussing what a table declaration guarantees. A checked write and an integrity scan operate at different times. The former rejects an incompatible attempted change. The latter examines what is already stored. Neither justifies changing schema text manually to evade the normal rules, and the test's deliberate setup should not be confused with a supported shortcut for converting an existing table.

New constraints examine old rows

The same release changes ALTER TABLE ADD COLUMN when the new column contains a check constraint, or is generated with a not-null constraint. The release notes say the operation validates existing rows and proceeds only when none violate the new constraints. An alteration no longer expresses only how later writes should behave in these cases.

The ADD COLUMN tests in the release make this visible with a check that refers to an existing column. Adding a column with CHECK(a!=1) fails when a stored row already contains a=1. A corresponding check that excludes a value absent from the table succeeds. The relevant fact is the existing data, even though the new column itself is not the expression's main input.

Another case adds a generated column based on an existing field and declares the generated value not null. A preexisting null input causes the new constraint to fail. The suite repeats related cases for temporary and attached tables. These tests prevent an overly narrow reading in which validation applies only to a newly created empty table.

For migration authors, this means the proposed declaration and the current rows jointly determine whether the alteration is valid. The engine can reject an inconsistent new contract during the schema operation. That is stronger than letting a migration appear to establish a constraint while retaining rows that already contradict it.

STRICT and the new alteration checks address related but separate responsibilities. STRICT governs the permitted representations of values in a chosen table. ADD COLUMN validation applies specified new constraints to existing rows. Together they let applications state more of their storage assumptions in SQLite, with errors at the point where a write or schema change would violate those assumptions.