Database Engine: SQLite
Error Code: N/A
Error Message: Error: near "index": syntax error
The following query fails and the table is not created
create table members (index integer primary key, name text not null);
Many syntax errors can be spotted by adopting a better style of writing queries. Though in this case the error is caused by the use of a "keyword" that has special meaning. IMHO it is much better to avoid using keywords as identifiers (it can make things confusing) but if it must be,
enclosing them in single quotes will tell the database it's an identifier (double quotes signify string literals)
eg. The following query will create the table
CREATE TABLE 'members' (
'index' INTEGER PRIMARY KEY
, 'name' TEXT NOT NULL
:d'oh: I must have been thinking of something else - or more likely not thinking :blush:
See next post
If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:
'keyword' A keyword in single quotes is a string literal.
"keyword" A keyword in double-quotes is an identifier.
[keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.
CREATE TABLE "members" (
"index" INTEGER PRIMARY KEY
, "name" TEXT NOT NULL