Naming Conventions

Each database, table, column, index, trigger, or view has a name by which it is identified and almost always the name is supplied by the developer. The rules governing how a valid identifier is formed in SQLite are set out in the next few sections.

Valid Characters

An identifier name must begin with a letter or the underscore character, which may be followed by a number of alphanumeric characters or underscores. No other characters may be present. These identifier names are valid:

• mytable

• my_field

• xyz123

• a

However, the following are not valid identifiers:

• my table

• my-field

• 123xyz

You can use other characters in identifiers if they are enclosed in double quotes (or square brackets), for example:

 sqlite> CREATE TABLE "123 456"("hello-world", " "); 

Name Length

SQLite does not have a fixed upper limit on the length of an identifier name, so any name that you find manageable to work with is suitable.

Reserved Keywords

Care must be taken when using SQLite keywords as identifier names. As a general rule of thumb you should try to avoid using any keywords from the SQL language as identifiers, although if you really want to do so, they can be used providing they are enclosed in square brackets.

For instance the following statement will work just fine, but this should not be mimicked on a real database for the sake of your own sanity.

 sqlite> CREATE TABLE [TABLE] (    ...>   [SELECT],    ...>   [INTEGER] INTEGER,    ...>   [FROM],    ...>   [TABLE]    ...> ); 

Case Sensitivity

For the most part, case sensitivity in SQLite is off. Table names and column names can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same database object name can be used interchangeably.

SQL commands are always shown in this book with the keywords in uppercase for clarity; however, this is not a requirement.

Note

The CREATE TABLE, CREATE VIEW, CREATE INDEX, and CREATE TRIGGER statements all store the exact way in which they were typed to the database so that the command used to create a database object can be retrieved by querying the sqlite_master table. Therefore it is always a good idea to format your CREATE statements clearly, so they can be referred to easily in the future.