SQLite table and column name requirements

Question

I'm wondering what constraints SQLite puts on table and column names when creating a table. The documentation for creating a table says that a table name can't begin with "sqlite_" but what other restrictions are there? Is there a formal definition anywhere of what is valid?

SQLite seems surprisingly accepting as long as the name is quoted. For example...

sqlite> create table 'name with spaces, punctuation & $pecial characters?'(x int);
sqlite> .tables
name with spaces, punctuation & $pecial characters?

Answer 1

If you use brackets or quotes you can use any name and there is no restriction :

create table [--This is a_valid.table+name!?] (x int);

But table names that don't have brackets around them should be any alphanumeric combination that doesn't start with a digit and does not contain any spaces.

You can use underline and $ but you can not use symbols like: + - ? ! * @ % ^ & # = / \ : " '

Answer 2

From the sqlite doc,

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.

So, double quoting the table name and you can use any chars. [tablename] can be used but not a standard SQL.