6.1.4 Specifying names

(1) Names

The following specification are specified as names.

• Index identifier

• Correlation name

• Query name

• Authorization identifier

• Schema identifier

• Table identifier

• Column name

• Constraint name

• Field name

• Foreign server name

• DB area name

There are two ways of specifying a name: with double quotation marks (") or without double quotation marks. When specifying a name, it is recommended that the name be enclosed in double quotation marks. When a name is enclosed in double quotation marks, it is treated as case-sensitive.

Note

The name cannot be the same as the reserved word, but can be the same as the reserved word if enclosed in double quotation marks. Since reserved words may be added as SQL is extended, enclose the names in double quotation marks in advance to avoid duplicate problems with reserved words added later.

The name is specified as an identifier. There are two types of identifiers: normal identifiers, which are specified without double quotation marks ("), and delimited identifiers, which are specified with double quotation marks ("). Note the following rules for specifying normal identifiers and delimited identifiers.

• When specifying table identifiers in the form of normal identifiers

  Example: table01

• When specifying table identifiers in the form of delimited identifiers

  Example: "table01"

To Page Top

(2) Rules for characters that can be used in names

• The first character of a name must be an uppercase alphabetic character (A to Z, #, @, \, _), lowercase alphabetic character (a to z), 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 the selection-expression AS column-name column in the first query specification of the COPY statement

  • 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#7 and numeric characters	Lowercase characters#2	Katakana characters	Space#3	Hyphen (-)	Vertical bar (|)
  1	Index identifier	100	Y	Y	Y	D	D	D	Y
  2	Correlation name	100	Y	Y	Y	D	D	D	Y
  3	Query name	100	Y	Y	Y	D	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	D	D	D	Y
  7	Column name	100	Y	Y	Y	D	D	D	Y
  8	Constraint name	100	Y	Y	Y	D	D	D	Y
  9	Field name	100	Y	Y	Y	D	D	D	Y
  10	Foreign server name	100	Y	Y	Y	D	D	D	Y
  11	DB area name	30	Y#6	Y	N	N	D	N	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 \.

  Only the characters A to Z and a to z can be specified for the first character of the DB area name.

  #7

  Uppercase characters mean A to Z, #, @, \, and _.

To Page Top

(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.

To Page Top