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:
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
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.
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.
|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:
Or be one of the following characters:
Identifier-extender characters must be in the session character set and belong to one of the following Unicode General Category classes:
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:
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:
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:
The setting of the NameValidationRule field may define additional character restrictions.
|Other considerations||These additional restrictions apply:
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."