85

What are the combination of characters for a table name in SQLite to be valid? Are all combinations of alphanumerics (A-Z, a-z and 0-9) constitute a valid name? 

Ex. CREATE TABLE 123abc(...);

What about a combination of alphanumerics with dashes "-" and periods ".", is that valid as well?

Ex. CREATE TABLE 123abc.txt(...);
Ex. CREATE TABLE 123abc-ABC.txt(...);

Thank you.

David
  • 14,205
  • 20
  • 97
  • 144

5 Answers5

103

I haven't found a reference for it, but table names that are valid without using brackets around them should be any alphanumeric combination that doesn't start with a digit:

abc123 - valid
123abc - not valid
abc_123 - valid
_123abc - valid
abc-abc - not valid (looks like an expression)
abc.abc - not valid (looks like a database.table notation)

With brackets you should be able to use pretty much anything as a table name:

[This should-be a_valid.table+name!?]
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Just another follow up question. Would the parenthesis rule also apply to column names? For instance, SELECT * FROM myTable WHERE [column.name] = 1; – David Sep 13 '10 at 22:38
  • @David: I don't know definitely about SQLite, but in all other databases I have used you can use column names like that. – Guffa Sep 14 '10 at 05:12
  • 1
    aren't brackets some sort of quoting? e.g. "abc-abc" also should work. – dashesy Dec 20 '14 at 19:04
  • 1
    @dashesy: Yes, that should work. What you can use for quoting depends on the database and its settings. – Guffa Dec 21 '14 at 12:10
  • 3
    I find that the name 'transaction' can't be a valid table name in sqlite3: sqlite> create table transaction (id integer primary key); Error: near "transaction": syntax error – Kedar Mhaswade Apr 02 '15 at 11:33
  • 1
    @KedarMhaswade: Yes, there are reserved keywords that you can't use as identifiers without quoting them, as they are recognised as the keyword instead of as an identifier. – Guffa Apr 02 '15 at 11:42
  • Apparently, "Index" is not a valid column name in SQLite3, either. – Krythic Feb 19 '18 at 07:04
  • Seems like the square brackets (as well as double-quotes) don't do the trick anymore in SQLite3. I'm trying to do this with hyphenated table name in PHP+PDO+SQLite3, and I'm getting `SQLSTATE[HY000]: General error: 1 near "-": syntax error`. – Mike Shiyan Mar 20 '18 at 08:54
34

All of these are allowed, but you may have to quote them in "".

sqlite> CREATE TABLE "123abc"(col);
sqlite> CREATE TABLE "123abc.txt"(col);
sqlite> CREATE TABLE "123abc-ABC.txt"(col);
sqlite> select tbl_name from sqlite_master;
123abc
123abc.txt
123abc-ABC.txt

In general, though, you should stick to the alphabet.

Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • 3
    Double-quotes are the standard quoting mechanism, not square brackets - https://www.sqlite.org/lang_keywords.html – Neil Mar 16 '15 at 21:59
  • Right, SQLite complained while dealing with a tutorial that had 'create table transaction ...' to model a 'bank transaction' and that was because 'transaction' is a keyword 'which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object' -- from your link! – Kedar Mhaswade Apr 02 '15 at 11:36
6

From SQLite documentation on CREATE TABLE, the only names forbidden are those that begin with sqlite_ :

Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".

Community
  • 1
  • 1
Wirawan Purwanto
  • 3,613
  • 3
  • 28
  • 28
6

Per Clemens on the sqlite-users mailing list:

Everything is allowed, except names beginning with "sqlite_".

CREATE TABLE "TABLE"("#!@""'☺\", "");

You can use keywords ("TABLE"), special characters (""#!@""'☺\"), and even the empty string ("").

eric.mcgregor
  • 3,507
  • 2
  • 16
  • 16
0

If you use periods in the name you will have issues with your SQL Queries. So I would say avoid those.

makeshyft_tom
  • 174
  • 9