15.00 - General Rules for Constructing Object Names - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

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. For details, see the topics beginning with “About Object Naming Controls” on page HIDDEN.

    Using QUOTATION MARK Characters with Object Names

    Enclosing an object name in QUOTATION MARK 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 MARK characters (U+0022). Each pair of quotation marks is counted as one character when calculating the name size limit.

    QUOTATION MARK 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.

    For details, see “Characters Allowed in Object Names” on page 81.

    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, 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; 

    Note: 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.

    Note: 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.

    For more information, see SQL Data Manipulation Language.

    Related Information

     

    For information on...

    See...

    Using the TITLE phrase

  • SQL Data Definition Language Syntax and Examples
  • SQL Data Manipulation Language
  • UPPER Function
  • TRANSLATE and TRANSLATE_CHK functions
  • SQL Functions, Operators, Expressions, and Predicates

    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.
  • For a list of reserved and nonreserved restricted words, see Appendix B.

    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.
  • Note: 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 is available on the Teradata User Documentation CD and at http://www.info.teradata.com.

     

    File Name

    Publication Product ID

    Language Mode

    UOBJNEXT.txt

    B035-1200-112K

    Any language mode

    Note: 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.

    Note: 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

    Note: 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.

    Note: 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]
  • Note: 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]
  • Note: 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]
  • Note: 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)
  • Note: 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.