6.1.4 Specifying names
- Organization of this subsection
(1) About names
The following are specified as names:
-
Index identifiers
-
Correlation names
-
Query names
-
Authorization identifiers
-
Schema identifiers
-
Table identifiers
-
Column names
-
Constraint names
-
DB area names
A name can be specified either enclosed in double quotation marks (") or not. When specifying a name, we recommend enclosing it in double quotation marks ("). If a name containing alphabetic characters is enclosed in double quotation marks ("), it becomes case sensitive.
- Note
-
You cannot specify a name that is the same as a reserved word, unless you enclose the name in double quotation marks ("). As the functional scope of SQL is extended, reserved words might be added, so we recommend that you enclose all names in double quotation marks to avoid potential conflict with reserved words that might be added in the future.
Note that a name is specified as an identifier. Identifiers include normal identifiers, which are not enclosed in double quotation marks ("), and delimited identifiers, which are enclosed in double quotation marks ("). The following are examples of specifying a normal identifier and a delimited identifier.
-
Specifying a table identifier using the normal identifier format
Example: table01
-
Specifying a table identifier using the delimited identifier format
Example: "table01"
(2) Rules for characters that can be used in names
-
The first character of a name must be an uppercase alphabetic character, lowercase alphabetic character, half-width katakana character, or full-width character. Note, however, that a name can begin with a (half-width) left or right parenthesis in the following sections of the first query specification (except the query specification in the WITH clause of the SELECT statement):
-
Name of the selection-expression AS column-name column
-
Name of a column in the ORDER BY clause (except the ORDER BY clause specified in a subquery)
Note that if you specify a column name that includes a half-width parenthesis, you must enclose it in double quotation marks ("). That is, you must specify it as a delimited identifier.
-
-
The following table lists restrictions on characters and lengths that are permitted for names.
Table 6‒4: Restrictions on characters and lengths that are permitted for names No.
Type of name
Maximum length (bytes)
Characters#1
Full-width characters#1, #4
Uppercase and numeric characters
Lowercase characters#2
Katakana characters
Underscore (_)
Space#3
Hyphen (-)
1
Index identifier
100
Y
Y
Y
Y
D
D
Y
2
Correlation name
100
Y
Y
Y
Y
D
D
Y
3
Query name
100
Y
Y
Y
Y
D
D
Y
4
Authorization identifier#5
100
Y
Y
N
N
N
N
N
5
Schema identifier
100
Y
Y
N
N
N
N
N
6
Table identifier
100
Y
Y
Y
Y
D
D
Y
7
Column name
100
Y
Y
Y
Y
D
D
Y
8
Constraint name
100
Y
Y
Y
Y
D
D
Y
9
DB area name
30
Y#6
Y
N
Y
N
D
N
- Legend:
-
Y: Can be used.
D: Can be used when specifying a name using the delimited identifier format. This character cannot be used when specifying a name using the normal identifier format.
N: Cannot be used.
- #1
-
Names can use a mixture of half-width characters and full-width characters.
- #2
-
If the name is specified using the normal identifier format, single-byte lowercase letters are treated as single-byte uppercase letters.
If the name is specified using the delimited identifier format, single-byte lowercase and uppercase letters are distinguished.
- #3
-
If the name is specified using the delimited identifier format, the final character of the name cannot be a single-byte space.
- #4
-
Double-byte spaces are not permitted.
- #5
-
ALL, HADB, MASTER, and PUBLIC cannot be specified as authorization identifiers.
- #6
-
Not including #, @, and \.
(3) What to do if a name conflicts with an SQL reserved word
Change the SQL statement by enclosing the name that is in conflict with a reserved word in double quotation marks ("), thus specifying it in the delimited identifier format.
Note that if a name containing alphabetic characters is enclosed in double quotation marks ("), it becomes case sensitive.