Hacker News Reader

Comment by msbarnett | Hacker News Reader

Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.

View on HN

> led to me storing strings in an integer column

Recent SQLite versions have STRICT mode where they forbid that.

https://www.sqlite.org/stricttables.html

> or too-long data in a too-short column

You can use a constraint for that:

    CREATE TABLE test ( 
        name TEXT NOT NULL CHECK(length(name) <= 20)
    ) STRICT;

Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.

Reply on news.ycombinator.com