ANSI SQL Commands and Features Not Supported

The SQL language implemented by SQLite is fairly comprehensive; however, a few commands and features of the ANSI-92 specification are not available.

ALTER TABLE

SQLite does not allow the schema of a table to be changed, so the ALTER TABLE command is not supported.

To add, remove, or modify columns in a table, you must drop the table and re-create it with the revised schema. The usual way to do this is with a temporary table to hold the data from the old table while it is being re-created.

COUNT(DISTINCT column- name )

The DISTINCT keyword cannot appear inside a COUNT function. Instead you must use a nested subquery.

SELECT COUNT(DISTINCT mycol) FROM mytable;

becomes

SELECT COUNT(*) FROM (
  SELECT DISTINCT mycol FROM mytable
);

GRANT and REVOKE

Because access to SQLite databases takes place at the filesystem level, the only permissions that can be applied are those available to the underlying operating system. Therefore the GRANT and REVOKE commands are meaningless for SQLite.

INSERT, UPDATE, and DELETE on Views

SQLite does not allow write actions to be performed directly on a view, even if there is only one base table in the view. However, a trigger can be created on a view using the INSTEAD OF syntax, in which you can perform the appropriate INSERT , UPDATE , or DELETE on the underlying table(s).

RIGHT OUTER JOIN

LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN . Therefore the table order in your queries must allow outer joins to be performed in this direction.

CHECK and FOREIGN KEY Constraints

Although the SQL syntax allows CHECK and FOREIGN KEY clauses to be included, they are ignored. They may be implemented in a future version.

Trigger Limitations

SQLite does not support the FOR EACH STATEMENT type of trigger, or INSTEAD OF TRiggers on tables. INSTEAD OF TRiggers can only be used on views.

Nested Transactions

Only one transaction can be active at a time. The name argument to BEGIN TRANSACTION is ignored.

Variable Subqueries

SQLite evaluates subqueries only once and therefore they cannot refer to variables in the main queryalso known as correlated subqueries.