This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:

    PRAGMA journal_mode = WAL
    PRAGMA foreign_keys = ON
    # Something non-null
    PRAGMA busy_timeout = 1000
    # This is fine for most applications, but see the manual
    PRAGMA synchronous = NORMAL
    # If you use it as a file format
    PRAGMA trusted_schema = OFF

Also use strict tables. https://www.sqlite.org/stricttables.html

While it has bad ergonomics, you can also use CHECK constraints. For example, using sqlite's built in date support, it's possible but awkward:

    CHECK (
      date(my_date_col) IS NOT NULL
      AND my_date_col = date(my_date_col)
    )

The price of compatibility could be a pragma.