tags:

views:

46

answers:

2

Hi,

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.

A: 

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
+1  A: 

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
Thank you. Exactly the answer I was looking for.
David
Just another follow up question. Would the parenthesis rule also apply to column names? For instance, SELECT * FROM myTable WHERE [column.name] = 1;
David
@David: I don't know definitely about SQLite, but in all other databases I have used you can use column names like that.
Guffa
@Guffa, thanks, I'll try it out.
David