16.20 - Object Names - Teradata Database - Teradata Vantage NewSQL Engine

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.

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;

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.

Restricted Words in Object Names

New releases of Teradata Database can include 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

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.

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.

Characters Allowed in Object Names

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

Related Topics