sqlsqlite

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.


Solution

  • 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)
    abc$abc -- valid (undocumented, not SQL, SQL Server portability?) ¹
    

    With quoted names you should be able to use pretty much anything as a table name, and you should always quote such names (tables, columns, triggers, views ...) to be robust and well-prepared for occasional keyword changes: ²

    "This should-be a_valid.table+name!?" -- (double-quotes, always an 
                                          --  identifier, never a keyword)
    

    SQLite also supports quoting in SQL Server and MySQL style for portability reasons: ²

    [This should-be a_valid.table+name!?] -- MS Access, SQL Server (square brackets)
    
    `This should-be a_valid.table+name!?` -- MySQL (grave accents '`' ASCII code 96)
    

    ¹ cf. What is [...] $ [...] in SQL Server?
    ² cf. SQLite Keywords