SQLite

STRICT Tables Tables were implemented in 3.37.1. The following text is now out of date but retained for historical reference.

SQLite strives to follow Postel's Rule which is to be very forgiving of unexpected or unusual inputs. But some users complain that this reduces portability with other database engines. For example, programs that are originally written using SQLite might stop working when moved to PostgreSQL since PostgreSQL is much less forgiving of dodgy SQL. So the suggestion has been raised to add a new pragma:

    PRAGMA strict=ON;

This page is intended to collect ideas for new error conditions that would be enforced in strict mode, assuming that strict mode is someday implemented.

1. Require that string literals always using single-quotes. Disable the logic (added for MySQL compatibility) that causes double-quoted text to be interpreted as string literals if the text cannot be resolved as an identifier.

2. Throw an error if a subquery used as an expression returns more than a single row. (The default behavior is that the result is taken from the first row and all subsequent rows are silently ignored.)

3. Enforce NOT NULL on all PRIMARY KEY columns

4. In an aggregate query, do not allow input columns that are not in the GROUP BY clause to be used outside of arguments to aggregate functions.

5. Throw an error on division by zero. (Default behavior is to return NULL.)

6. Throw an error in the date/time functions if the input parameters do not contain a well-formed data/time strings. (Default behavior is to return NULL.)

7. Foreign key constraints are enabled by default.

8. Require a datatype on every table column and enforce those types. Column types must be one of:

   -   INT, INTEGER
   -   REAL, DOUBLE
   -   TEXT, VARCHAR
   -   BLOB
   -   ANY
   

9. Disallow the use of keywords as identifiers.