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.,
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.,
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).
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.
Although you might name tables and other components of a database with lower-case 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 lower-case 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.,
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 lower-case 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.
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.