Rules for Specifying NuoDB Identifier Names

Database identifiers in NuoDB include names of schemas, tables, views, indexes, domains, columns, etc. An identifier is a name (e.g., a database name like bookstore), a name preceded by an optional qualifying prefix with a dot (e.g., bookstore.books), or a name preceded by two levels of qualifiers (e.g., bookstore.books.isbn).

Qualified Names

Column names may be qualified, prefixed by a table name, and table names may be qualified by a schema name. You can see more clearly what’s meant by this in the example below:

 SELECT book_id, quantity, title, author_name, country_name
 FROM books
 JOIN authors ON books.author_id = authors.author_id
 JOIN reference_data.countries ON authors.country_code = countries.country_code
 WHERE author_name LIKE '%Greene'
 OR author_name LIKE '%Waugh'
 OR author_name LIKE '%Calvino' ORDER BY title LIMIT 5;

 BOOK_ID  QUANTITY               TITLE                AUTHOR_NAME   COUNTRY_NAME
 -------- --------- -------------------------------- ------------- --------------

   1872       6     A Burnt Out Case                 Graham Greene United Kingdom
   4132       2     A Handful of Dust                Evelyn Waugh  United Kingdom
   4108       4     Brideshead Revisited             Evelyn Waugh  United Kingdom
   2893       8     If on a Winters Night a Traveler Italo Calvino Italy
   1301       3     Our Man in Havana                Graham Greene United Kingdom

Notice in the SQL statement here, in the first JOIN the specific author_id columns are named by prefixing their common names with their respective table names (e.g., books.author_id). Also notice that the second JOIN clause includes a table (i.e, countries) that is part of a different schema (i.e, reference_data) to get the name of the country, instead of the country code (see results of SQL statement).

Permitted Characters

Identifiers in NuoDB can contain alphanumeric characters, as well as certain special characters: underscore ('_'), dollar sign ('$'), ampersand ('&'), or a hash sign ('#').

Identifiers surrounded by backticks (`) or double quotes (") can contain any single-byte character.

 CREATE TABLE sales_&_marketing (id integer, emp_id integer, telephone_# integer);

In the example here, we’ve created a table for all employees who are members of the Sales & Marketing Department, assuming those people work together. Notice that the name of this table includes two underscores and one ampersand, and one column name includes an underscore and another column name uses an underscore and a hash. This style of naming tables and columns isn’t common, but it is allowed in NuoDB.

Case Sensitivity

Although you might name tables and other components of a database with lowercase letters, all identifiers are converted to uppercase by default. Nevertheless, if you want to preserve the case, you can do so by giving the identifier within double-quotes like so:

 CREATE TABLE "Sales_&_Marketing" ("id" integer, "emp_id" integer, "telephone" integer);

 SHOW TABLE sales_&_marketing;

    Tables named SALES_&_MARKETING

    Found table Sales_&_Marketing in schema BOOKSTORE

        Fields:
            id integer
            emp_id integer
            telephone integer

Notice in this example that the table name returned from SHOW TABLE is Sales_&_Marketing, as entered with the CREATE TABLE statement. Still, we’re able to access the table without having to use upper and lowercase letters; SQL statements (e.g., SHOW TABLE) are not case sensitive. Notice also that the case was preserved for the column names since we gave each of them within double-quotes.

Even though the case of identifiers are preserved when given within double quotes, querying a system table (e.g., SYSTEM.FIELDS) might not give expected results. This is because of the case-insensitive collation on the TABLENAME column in the SYSTEM.FIELDS table.

Delimiting Identifiers

There are three methods of delimiting—or quoting—NuoDB identifiers: you may use double quotes (i.e., "some_text") or backticks (i.e., `some_text`), or square brackets (i.e., [some_text]). Each method will allow you to use reserved words and built-in function names as identifiers, and they allow you to use certain special ASCII characters (see Permitted Characters above). You can preserve the case by using double-quotes and square-brackets, but not with backticks.

Below is an example in which these delimiters are used for giving database object names:

 CREATE TABLE [primary] ("Item_ID" INTEGER, `Description` string );

 SHOW TABLE "primary";

    Tables named primary

    Found table primary in schema test

        Fields:
            Item_ID integer
            DESCRIPTION string

In this example, we created a table with the name, primary. That’s a reserved word. To be able to use that name for the table, though, we gave the name within square brackets. That also caused it to preserve the lowercase letters. As for the column names, we used double quotes for the first one and backticks for the second one. However, backticks won’t preserve the case. The result was that the case was preserved for the first column, but not the second column.

For a complete list of reserved keywords and built-in functions for NuoDB, see SQL Keywords.

Character Limitations

All database identifiers are limited to 128 characters. Also, NuoDB supports only ASCII characters.

 CREATE SCHEMA "Dependent_Association_of_Mechanical_&_Electrical_Engineers_in_Conjunction_with_the_Premier_Engineering_Academy_Redundancy_School";

In this ridiculous example, we’ve created a schema for an employer who has the absurdly long name, The Dependent Association of Mechanical and Electrical Engineers in Conjunction with the Premier Engineering Academy Redundancy School, and who insists that we name the schema the same. Since that would be a few characters more than 128, they reluctantly agreed to eliminate the article (i.e., The) at the start and to change and to an ampersand to reduce the length to exactly the maximum number of characters allowed—but they would not suffer any more reduction or abbreviation.