CODEWITHSUNDEEP
sqlsqlite
David
David

Reputation: 14414

What are valid table names in SQLite?

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.

Upvotes: 87

Views: 62298

Answers (5)

Guffa
Guffa

Reputation: 700910

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 quoted names you should be able to use pretty much anything as a table name:

"This should-be a_valid.table+name!?"

SQLite also supports quoting in SQL Server and MySQL style:

[This should-be a_valid.table+name!?]

`This should-be a_valid.table+name!?`

Upvotes: 105

makeshyft_tom
makeshyft_tom

Reputation: 174

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

Upvotes: 0

Wirawan Purwanto
Wirawan Purwanto

Reputation: 4043

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_".

Upvotes: 9

eric.mcgregor
eric.mcgregor

Reputation: 3665

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 ("").

Upvotes: 6

Matthew Flaschen
Matthew Flaschen

Reputation: 285077

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.

Upvotes: 37

Related Questions