What SQLite column name can be/cannot be?

Solution 1:

Can it have characters like '/'?

All examples are from SQlite 3.5.9 running on Linux.

If you surround the column name in double quotes, you can:

> CREATE TABLE test_forward ( /test_column INTEGER );
SQL error: near "/": syntax error
> CREATE TABLE test_forward ("/test_column" INTEGER );
> INSERT INTO test_forward("/test_column") VALUES (1);
> SELECT test_forward."/test_column" from test_forward;
1

That said, you probably shouldn't do this.

Solution 2:

The following answer is based on the SQLite source code, mostly relying on the file parse.y (input for the lemon parser).

TL;DR:

The allowed series of characters for column and table names in CREATE TABLE statements are

• '-escaped strings of any kind (even keywords)
• Identifiers, which means
  • ``` and "-escaped strings of any kind (even keywords)
  • a series of the MSB=1 8-bit ASCII characters or 7-bit ASCII characters with 1 in the following table that doesn't form a keyword:
• Keyword INDEXED because it's non-standard
• Keyword JOIN for reason that is unknown to me.

The allowed series of characters for result columns in a SELECT statement are

• Either a string or an identifier as described above
• All of the above if used as a column alias written after AS

Now to the exploration process itself

1. let's look at the syntax for CREATE TABLE columns

   // The name of a column or table can be any of the following:
   //
   %type nm {Token}
   nm(A) ::= id(X).         {A = X;}
   nm(A) ::= STRING(X).     {A = X;}
   nm(A) ::= JOIN_KW(X).    {A = X;}
   

2. digging deeper, we find out that

   // An IDENTIFIER can be a generic identifier, or one of several
   // keywords.  Any non-standard keyword can also be an identifier.
   //
   %type id {Token}
   id(A) ::= ID(X).         {A = X;}
   id(A) ::= INDEXED(X).    {A = X;}
   

   "Generic identifier" sounds unfamiliar. A quick look into tokenize.c however brings forth the definition

   /*
   ** The sqlite3KeywordCode function looks up an identifier to determine if
   ** it is a keyword.  If it is a keyword, the token code of that keyword is 
   ** returned.  If the input is not a keyword, TK_ID is returned.
   */
   
   /*
   ** If X is a character that can be used in an identifier then
   ** IdChar(X) will be true.  Otherwise it is false.
   **
   ** For ASCII, any character with the high-order bit set is
   ** allowed in an identifier.  For 7-bit characters, 
   ** sqlite3IsIdChar[X] must be 1.
   **
   ** Ticket #1066.  the SQL standard does not allow '$' in the
   ** middle of identfiers.  But many SQL implementations do. 
   ** SQLite will allow '$' in identifiers for compatibility.
   ** But the feature is undocumented.
   */
   

   For a full map of identifier characters, please consult the tokenize.c.

3. It is still unclear what are the available names for a result-column (i. e. the column name or alias assigned in the SELECT statement). parse.y is again helpful here.

   // An option "AS <id>" phrase that can follow one of the expressions that
   // define the result set, or one of the tables in the FROM clause.
   //
   %type as {Token}
   as(X) ::= AS nm(Y).    {X = Y;}
   as(X) ::= ids(Y).      {X = Y;}
   as(X) ::= .            {X.n = 0;}
   

Solution 3:

Except for placing "illegal" identifier names between double quotes "identifier#1", [ before and ] after works as well [identifire#2].

Example:

sqlite> create table a0.tt ([id#1] integer primary key, [id#2] text) without rowid;
sqlite> insert into tt values (1,'test for [x] id''s');
sqlite> select * from tt
   ...> ;
id#1|id#2
1|test for [x] id's

Solution 4:

Valid field names are subject to the same rules as valid Table names. Checked this with SQlite administrator.

1. Only Alphanumeric characters and underline are allowed
2. The field name must begin with an alpha character or underline

Stick to these and no escaping is needed and it may avoid future problems.