We finish this chapter on SQLite's implementation of the SQL language by looking at features of the ANSI SQL92 standard that are not currently supported by SQLite.
Although the CREATE TABLE syntax permits an optional CHECK clause to be present, the CHECK constraint is not enforced.
The keywords FOREIGN KEY are allowable in a CREATE TABLE statement; however, this currently has no effect.
Subqueries must return a static data set, and they may not refer to variables in the outer queryalso known as correlated subqueries.
All triggers are currently FOR EACH ROW, even if FOR EACH STATEMENT is specified.
Views are read-only, even when they select only from one table. However, an INSTEAD OF trigger can fire on an attempted INSERT, UPDATE, or DELETE to a view and deal with the transaction in the desired manner.
INSTEAD OF triggers are allowed only on views, not on tables.
Recursive triggerstriggers that trigger themselvesare not supported.
The ALTER TABLE statement is not present; instead a table must be dropped and re-created with the new schema.
Transactions cannot be nested.
count(DISTINCT column-name) cannot be used. However, this can be achieved by selecting a count() from a subselect of the desired table that uses the DISTINCT keyword.
All outer joins must be written as LEFT OUTER JOIN. RIGHT OUTER JOIN and FULL OUTER JOIN are not recognized.
The GRANT and REVOKE commands are meaningless in SQLitethe only permissions applicable are those on the database file itself.