What are valid table names in SQLite?
In SQLite, valid table names must:
- Use alphanumeric characters (A-Z, a-z, 0-9) and underscores.
- Never start with a digit nor use reserved keywords.
- Wrap special characters or spaces in double quotes
"table name"or brackets[table name].
Examples:
customers(simple)customer_data(with underscore)"Customer Data"or[Customer Data](spaces included)- Side step:
123customers,select,create(keywords or starting with digits)
Pushing the boundaries
SQLite allows table names to be created using almost any character combination when wrapped in double quotes or brackets. This opens the door for creative table names like "123abc.txt" or even something more daring like [This should-be a_valid.table+name!?]. Despite this flexibility, apply discretion for maintainable database design.
Keywords as table names
While it seems like a scene from a horror movie, even reserved keywords can be table names if they're quoted. Yes, you read it right, "[select]" could be a table. This approach is unusual and can cause more harm than good. Keep your sanity intact and avoid such practices.
Special characters: a mixed bag
Accepted characters like dashes - or periods . can be used in table names such as "customer-service" or "sales.records". But remember, with great power comes great responsibility. Periods can be interpreted as schema-name separators leading to unexpected results or errors.
Unwelcome guests: "sqlite_" prefix
Ensure your table names do not commence with "sqlite_". These names are reserved by SQLite for internal processes. Ignoring this advice, you're playing with fire risking conflict with existing or future SQLite features.
Numeric literals and other exceptions
While SQLite offers immense flexibility with table names, it does impose certain restrictions. Let's uncover these hidden SQLites (pun intended!):
Case sensitivity blues
In SQLite, quoted identifiers are case-sensitive. That is, "customer" and "Customer" are separate tables. Efficiency vs. Accuracy — a classic dilemma to opt for ease of access or stick with original case.
The digital debacle
Unquoted numeric literals do not qualify as table names in SQLite. For instance, 1234 would be deemed as a numeric literal, not a table name. To use numbers, they must be wrapped in quotes. However, they can't lead when blended with alpha-characters: "1234_test" is acceptable, whereas 1234test is illegal. Numbers — always wanting to be number one, but not here!
Unexpected guests: special characters
While special characters can be a part of table names, they might come with hidden costs:
- Potential confusion: Punctuation can complicate your SQL queries due to ambiguity between standard SQL syntax and arbitrary table names.
- Conflicts with naming conventions: Excessive usage of this allowance can disrupt the harmony within your database schema — it’s not a concert, your queries can do without the rockstar symbols!
Alias in double quotes
If you're a risk-taker who uses periods in table names, level it up with an alias for easier reading:
Was this article helpful?