You are here: SQL Development > Rules for Specifying NuoDB Identifier Names

Rules for Specifying NuoDB Identifier Names

Introduction

Database identifiers in NuoDB include names of schemas, tables, views, indexes, domains, columns, etc. An identifier is one of the following:

This page describes more information regarding the naming of NuoDB identifiers.

Qualified Names

Database tables can be qualified by an optional schema name. Database columns can be qualified by an optional table name, which can be qualified by an optional schema name, as in schema.table.column.

Permitted Characters

Identifiers in NuoDB can contain alphanumeric characters plus the following special characters:

_ $ & #

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

Case Sensitivity

All identifiers are converted to uppercase by default. Case sensitivity can be preserved by surrounding the identifier name in double quotes(") as in:

CREATE TABLE tbl1 (x integer);
CREATE TABLE "CamelTable" (x INTEGER);
SHOW TABLES
  
    Tables in schema USER
        TBL1
        CamelTable

If using double-quotes for an identifier, it is necessary to always use double-quotes for that identifier.

Note: Querying System Tables - Even though we preserve case of identifiers with double quotes, querying a table like SYSTEM.FIELDS might not give expected results. This is because of the case-insensitive collation on the TABLENAME column in the SYSTEM.FIELDS table.

Note: Database names are not the same as database identifiers.

Delimiting Identifiers

There are two useful methods of delimiting (or quoting) NuoDB identifiers - using double quotes (") and using back-ticks (`).

Delimiting Method Behavior
Back-tick Allows use of reserved words and built-in function names
Allows use of special ASCII characters
Double Quotes Allows use of reserved words and built-in function names
Allows use of special ASCII characters
Preserves case

In addition, for SQL Server compatibility, it is also possible to delimit identifiers using square brackets ([]). Delimiting an identifier with square brackets causes the same behavior as delimiting the identifier with double quotes.

Delimiting Method Behavior
Square Brackets Allows use of reserved words and built-in function names
Allows use of special ASCII characters
Preserves case

Some examples of delimiting database object names:

USE SCHEMA1;

/* Back-tick: */
CREATE TABLE `ab\`(field1 INTEGER);
SHOW TABLES;

    Tables in schema SCHEMA1
        AB\

/* Double Quotes */
CREATE TABLE table_nocase(field1 INTEGER);                                                                                                   
CREATE TABLE "table_UPPER"(field1 INTEGER);                                                                                                  
CREATE TABLE "table_lower"(field1 INTEGER);                                                                                                  
show tables;
 
    Tables in schema SCHEMA1
        table_lower
        TABLE_NOCASE
        table_UPPER
 

/* Brackets */
CREATE TABLE [schema1].[table_bracket]([field1] INTEGER,[field2] INTEGER);
SHOW TABLE schema1.table_bracket;

    Found table table_bracket in schema SCHEMA1

        Fields:
            field1 integer
            field2 integer 

Limitations

NuoDB has a limit of 128 characters for all database identifiers. Support for ascii characters only.

Reserved Words

The full list of reserved keywords and built-in functions in NuoDB can be found at SQL Keywords. The default escape character in NuoDB is a backslash '\'. Keywords and built-in function names can also be used out of context by enclosing the term in back-ticks, for example:

CREATE TABLE `GROUP`(x INT);