16.10 - Object Names - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

Database objects (for example, databases, tables, and columns) that you specify in an SQL statement must conform to system object naming rules or the SQL statement is not valid.

Pass Through Characters may not be used in object names.

General Rules for Constructing Object Names

Object names must be unique within the scope of the object name.

Within the database system:

  • No two Database names can have the same name.
  • Creating a user always creates a database with the same name as the user, so a user can not be named the same as an existing database.
  • No two profiles can share a name.
  • No two roles can share a name.
  • A Profile can have the same name as a Role, but no Role or Profile can have the same name as a Database

Within a named Database:

  • Tables, views, stored procedures, join or hash indexes, triggers, user-defined functions, and macros must have unique names

Within a Table or View:

  • No two columns can have the same name.
  • No two named constraints can have the same name.
  • No two secondary indexes can have the same name.

Within a macro or stored procedure:

  • No two parameters can have the same name.

Names are optional for CHECK constraints, REFERENCE constraints, and INDEX objects.

Object names are also subject to the name validation rules enabled on the system.

Using QUOTATION MARKS Characters with Object Names

Enclosing an object name in QUOTATION MARKS characters (U+0022) enables the use of characters, spaces, symbols, and other special characters that may not otherwise be allowed.

For example, an object name containing a white space character must be enclosed in quotation marks:

"object name"

When used as part of an object name, rather than when used to delineate an object name, quotation marks must be represented as a sequence of two QUOTATION MARKS characters (U+0022). Each pair of quotation marks is counted as one character when calculating the name size limit.

QUOTATION MARKS characters that delineate object names are not stored in Dictionary tables, and when querying a Dictionary view, results that contain such names are displayed without the double quotation marks. However, if you include the name in a subsequent database request, you must add the double quotation marks, or the request fails.

Case Sensitivity of Object Names

Object names are not case-dependent. Any mix of uppercase and lowercase can be used when defining or referencing object names in a request. Because of this, you cannot reuse a name that is required to be unique just by changing its case.

For example, the following statements are identical:

   SELECT Salary FROM Employee WHERE EmpNo = 10005;
   SELECT SALARY FROM EMPLOYEE WHERE EMPNO = 10005;
   SELECT salary FROM employee WHERE eMpNo = 10005;
The case in which a column name is defined can be important. The column name is the default title of an output column, so the system returns these names in the same case in which they were defined.

For example, assume that the columns in the SalesReps table are defined:

   CREATE TABLE SalesReps
    ( last_name VARCHAR(20) NOT NULL,
      first_name VARCHAR(12) NOT NULL, ...

In response to a query that does not define a TITLE phrase, for example:

   SELECT Last_Name, First_Name
   FROM SalesReps
   ORDER BY Last_Name;

the column names are returned exactly as defined, that is, last_name, then first_name.

You can use the TITLE phrase to specify the case, wording, and placement of an output column heading either in the column definition or in an SQL request.

Restricted Words in Object Names

Be careful not to use restricted words in object names. Restricted words include both reserved and nonreserved words.

  • If you attempt to create a new object with an unquoted name that includes a reserved word, the request fails with an error.
  • The system does not reject object names that use nonreserved words, but in some contexts, the system may interpret such words using the nonreserved word semantics rather than that of an object name.

New releases of Teradata Database often add new reserved and nonreserved words. Names that conflict with these new reserved words should be identified and renamed as part of preparation for a Teradata Database software upgrade. Instead you can enclose names containing reserved or nonreserved words in quotation marks, but changing the words is preferred to avoid possible developer errors.

Object Name Processing and Storage

Object names are stored in the Data Dictionary tables using the UNICODE server character set, and are processed internally as UNICODE strings. For backwards compatibility, object names are available in HELP output and in compatibility views translated to LATIN or KANJI1 based on the Language Support Mode (Standard or Japanese).

Comparison and Normalization of Object Names

In comparing two names, the following rules apply:

  • Names are case insensitive.
  • Object names are considered identical if they are converted to NFC or NFD and compared as case insensitive. This means that a fullwidth LATIN SMALL LETTER A (U+FF41) is not the same as a LATIN SMALL LETTER A (U+0061), but case pairs are equivalent.
  • The system converts all database object names to UNICODE for storage in the Data Dictionary. Names are normalized so that they convert to UNICODE Normalization Form C (NFC) after conversion to upper case.
Teradata Database supports all UNICODE normalization forms; NFC, NFD, NFCK, and NFDK. For information on use of the TRANSLATE function to specify alternate UNICODE normalization forms, for example, UNICODE_TO_UNICODE_NFD, see SQL Functions, Operators, Expressions, and Predicates.

UNICODE and Object Names

All object names are stored in the Data Dictionary in UNICODE. Teradata provides a file that lists the UNICODE representation of all characters that are valid in an object name. The file UOBJNEXT.txt (B035-1200) is available at http://www.info.teradata.com.

Some characters in this list may not be usable if the DBSControl NameValidationRule field is configured to enforce a reduced character set.

Object Name Validation Options

The DBS Control field NameValidationRule is used to define object name restrictions.

Values of 2 and above place additional limits on the character repertoire allowed.

The NameValidationRule is enforced by the system at object creation. Characters outside the repertoire defined by the rule are rejected by the system if they appear when creating object names.

Characters Allowed in Object Names

The following table summarizes the use of characters in object names.

Parameter Description
Object name length A maximum of 128 characters when expressed in UNICODE normalization form D.

NameValidationRule can be used to apply additional character restrictions.

Characters allowed in unquoted object names
Additional characters may be allowed in quoted or Unicode delimited names.
An object name not enclosed in quotation marks must be composed of an identifier-start character followed by a sequence of identifier-start or identifier extend characters, up to the maximum object name length limit.
Characters in object names not enclosed in quotation marks must also be in the session character set.

Identifier start characters must be contained in the session character set and belong to one of the following Unicode General Category classes:

  • Upper-case letters [Lu]
  • Lower-case letters [Ll]
  • Title-case letters [Lt]
  • Modifier letters [Lm]
  • Other letters ([Lo]
  • Letter numbers [Nl]

Or be one of the following characters:

  • NUMBER SIGN (U+0023)
  • DOLLAR SIGN (U+0024)
  • LOW LINE (U+005F)
  • INVERTED EXCLAIMATION MARK (U+001A)
  • OVERLINE (U+203E)
  • EURO SIGN (U+20AC)
  • KATAKANA-HIRAGANA VOICED SOUND MARK (U+309B)
  • KATAKANA-HIRAGANA SEMI-VOICED SOUND MARK (U+309B)
  • FULLWIDTH NUMBER SIGN (U+FF03)
  • FULLWIDTH DOLLAR SIGN (U+FF04)
  • FULLWIDTH LOW LINE (U+FF3F)

Identifier-extender characters must be in the session character set and belong to one of the following Unicode General Category classes:

  • Non-spacing marks [Mn]
  • Spacing combing marks [Mc]
  • Decimal numbers [Nd]
  • Connector punctuations [Pc]
  • Formatting codes [Cf]
The MIDDLE DOT character (U+00B7) is also a valid identifier-extender character.
Characters allowed only in object names that are enclosed in quotation marks A string literal is required for object names that:
  • Have an identifier-extender character as the first character.
  • Include the white space character, SPACE (U+0020)
  • Are Teradata keywords

In addition, object names that contain any character from the following classes must be enclosed in quotation marks, unless the character explicitly appears in the list of allowed characters:

  • Other, Control [Cc]
  • Other, Not Assigned [Cn]

    No characters in this category appear in UNICODE character repertoire.

  • Other, Private Use [Co]
  • Other, Surrogate [Cs]
  • Letter, Cased [LC]
  • Mark, Enclosing [Me]
  • Number, Other [No]
  • Punctuation, Dash [Pd]
  • Punctuation, Close [Pe]
  • Punctuation, Final quotation marks [Pf] (may behave like Ps or Pe depending on usage)
  • Punctuation, Initial quotation marks [Pi] (may behave like Ps or Pe depending on usage)
  • Punctuation, Other [Po]
  • Punctuation, Open [Ps]
  • Symbol, Currency [Sc]
  • Symbol, Modifier [Sk]
  • Symbol, Math [Sm]
  • Symbol, Other [So]
  • Separator, Line [Zl]
  • Separator, Paragraph [Zp]
  • Separator, Space [Zs]
When used as part of an object name, quotation marks must be represented as a sequence of two QUOTATION MARKS characters (U+0022). Each set of two quotation marks is counted as one character when calculating the name size limit.
Disallowed characters The following characters cannot appear in an object name:
  • NULL (U+0000)
  • SUBSTITUTE character (U+001A)
  • REPLACEMENT CHARACTER (U+FFFD)
  • Compatibility ideographs (U+FA6C, U+FA6F, U+FAD0, FAD1, FAD5, FAD6, and FAD7)
The setting of the NameValidationRule field may define additional character restrictions.
Other considerations These additional restrictions apply:
  • An object name consisting entirely of white spaces is not allowed.
  • A trailing white space is not considered part of an object name
  • You can use the NameValidationRule field to restrict object name allowable characters to a subset of those normally allowed.

Related Topics

For more information about:

  • For a list of what the system considers to be an object name for the purpose of name validation, see “System Validated Object Names.”
  • Using the TITLE phrase, see SQL Data Definition Language - Syntax and Examples, B035-1144 and SQL Data Manipulation Language , B035-1146 .
  • UPPER Function or TRANSLATE and TRANSLATE_CHK functions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.
  • Case Sensitivity of Object Names, see SQL Data Manipulation Language , B035-1146 .
  • For a list of reserved and nonreserved restricted words, see "Restricted Words."