SQLite | SQLite Basic Knowledge | Notes on Keywords Defined in SQLite
SQLite defines many keywords. These keywords cannot normally be used as table names, column names, and similar identifiers, but you can use them if you write them in an appropriate form. This page explains points to note about keywords defined in SQLite and lists those keywords.
How to Use Keywords as Identifiers
SQLite defines many keywords. The list is shown later, but words such as TABLE and SELECT are keywords. Keywords are also called reserved words.
Table names and database names are called identifiers. Identifiers can be specified by combining letters, numbers, and similar characters. For example, names such as booktable and name are valid. However, SQLite keywords cannot be used directly as identifiers. For example, an error occurs if you try to use select as a table name.
create table select(id, name);
sqlite> create table select(id, name);
Error: near "select": syntax error
sqlite>
It is better to avoid using keywords as table names, database names, and similar identifiers whenever possible. However, if you need to use a keyword as an identifier for some reason, use one of the following four forms.
'keyword'
"keyword"
[keyword]
`keyword`
When a keyword is enclosed in single quotes ('), it is treated as a string value. If a single-quoted string value is written where an identifier is expected, SQLite treats it as an identifier, so you can specify it as follows.
create table 'select'(id, name);
sqlite> create table 'select'(id, name);
sqlite>
When a keyword is enclosed in double quotes ("), square brackets ([]), or backticks (`), it is treated as an identifier. Also, if you write an identifier enclosed in double quotes where a string should be written, it is treated as a string.
create table "select"(id, name);
sqlite> create table "select"(id, name);
Error: table "select" already exists
sqlite> drop table 'select';
sqlite> create table "select"(id, name);
sqlite>
Square brackets are the style used by Access and SQL Server, while backticks are the style used by MySQL. These two styles are provided for compatibility with those databases, so in general, use single quotes or double quotes.
Keyword List
The following are the keywords defined by SQLite. This list is published on the official site below.
| ABORT | ACTION | ADD | AFTER | ALL |
|---|---|---|---|---|
| ALTER | ANALYZE | AND | AS | ASC |
| ATTACH | AUTOINCREMENT | BEFORE | BEGIN | BETWEEN |
| BY | CASCADE | CASE | CAST | CHECK |
| COLLATE | COLUMN | COMMIT | CONFLICT | CONSTRAINT |
| CREATE | CROSS | CURRENT | CURRENT_DATE | CURRENT_TIME |
| CURRENT_TIMESTAMP | DATABASE | DEFAULT | DEFERRABLE | DEFERRED |
| DELETE | DESC | DETACH | DISTINCT | DO |
| DROP | EACH | ELSE | END | ESCAPE |
| EXCEPT | EXCLUSIVE | EXISTS | EXPLAIN | FAIL |
| FILTER | FOLLOWING | FOR | FOREIGN | FROM |
| FULL | GLOB | GROUP | HAVING | IF |
| IGNORE | IMMEDIATE | IN | INDEX | INDEXED |
| INITIALLY | INNER | INSERT | INSTEAD | INTERSECT |
| INTO | IS | ISNULL | JOIN | KEY |
| LEFT | LIKE | LIMIT | MATCH | NATURAL |
| NO | NOT | NOTHING | NOTNULL | NULL |
| OF | OFFSET | ON | OR | ORDER |
| OUTER | OVER | PARTITION | PLAN | PRAGMA |
| PRECEDING | PRIMARY | QUERY | RAISE | RANGE RECURSIVE |
| REFERENCES | REGEXP | REINDEX | RELEASE | RENAME |
| REPLACE | RESTRICT | RIGHT | ROLLBACK | ROW |
| ROWS | SAVEPOINT | SELECT | SET | TABLE |
| TEMP | TEMPORARY | THEN | TO | TRANSACTION |
| TRIGGER | UNBOUNDED | UNION | UNIQUE | UPDATE |
| USING | VACUUM | VALUES | VIEW | VIRTUAL |
| WHEN | WHERE | WINDOW | WITH | WITHOUT |
Some of these keywords can be used as identifiers even if they are not enclosed in single quotes, double quotes, or similar delimiters. However, using keywords directly as identifiers makes them harder to understand, so it is not recommended.
If an unknown error occurs when creating a table or similar object, check whether the table name or column name is a keyword.