What SQLite column name can be/cannot be?

sql

best-practices

database-design

sql-optimization

byAnton Shumikhin·Nov 15, 2024

SQLite column names need to follow the standard identifier semantics for optimal compatibility:

Initiate with a letter (a-z or A-Z) or an underscore (_).
Continue with alphanumeric characters (a-z, A-Z, 0-9) or underscores (_).
Use double quotes (" ") for reserved keywords or special characters.

Here's a pinpoint example of a clear table structure:

CREATE TABLE example (
    "Id" INTEGER,
    "Name" TEXT,
    "Age" INTEGER
);

Remember: The usage of quotes is optional unless the names include specific characters or represent reserved keywords.

Using special characters wisely

In certain situations, you might want to use special character in column names. Although SQLite is forgiving and accepts almost any UTF-8 character when placed within quotes:

/* Waiter asks: "Soup or salad?" Programmer: "Yes" */
CREATE TABLE quirky_names (
    "Employee#Id" INTEGER,
    "First Name" TEXT,
    "E-mail Address" TEXT
);

But, be warned, while SQLite tolerates these identifiers, they can make your SQL statements more error-prone and negatively impact maintainability.

An International Affair: UTF-8 Support

Thanks to UTF-8 encoding support in SQLite, you can use characters from various international scripts. Don't be shy to use your native language characters, but do so with caution as not all systems may support these characters:

/* Yes, SQLite speaks your language too! */
CREATE TABLE internacional (
    "Número" INTEGER,
    "名" TEXT,
    "Возраст" INTEGER
);

Adventures with '$' symbol in identifiers

SQLite has an intriguing tolerance for symbols like '$' in identifiers. Although there's no official documentation, identifiers such as $columnName may work but aren't recommendable due to unpredictable behavior and compatibility issues.

Making sense of compatibility and constraints

Remember about cross-platform compatibility

Inconsistent behavior might be experienced with column names, depending upon the operating system. Make it a point to test your database schema on all the anticipated platforms.

Emphasize readable and maintainable names

Sticking to familiar SQL naming conventions improves the legibility and maintainability of your codebase. Although SQLite extends a good amount of flexibility, it's advisable to keep names straightforward and devoid of complex characters.

The risk of reserved names and keywords

SQLite has a robust list of reserved keywords, using them unlabeled will result in syntax errors. It's a wise practice to know these keywords to avoid unintentional conflicts.

Why documentation is your best friend

The SQLite documentation outlines comprehensive guidelines on column names, the dos and don'ts. Stay up-to-date with this resource to truly master SQLite naming conventions.

explain-codes / Sql / What SQLite column name can be/cannot be?

Linked

Sql standard to escape column names?



How to get ROWID in SQLite?



Sqlite Query in non case sensitive alphabetical order



What is the difference between single and double quotes in SQL?



How to get a list of column names on Sqlite3 database?

