Identifiers
An identifier is the name of an SQL entity, such as a table, a view, a column (field), a schema, a table alias, a column alias, an index, a stored procedure, a trigger, or some other SQL entity. An identifier name must be unique within its context; for example, two tables in the same schema, or two fields within the same table cannot have the same name. However, two tables in different schemas, or two fields in different tables can have the same name. In most cases, the same identifier name can be used for SQL entities of different types; for example, a schema, a table in that schema, and a field in that table can all have the same name without conflict. However, a table and a view in the same schema cannot have the same name.
InterSystems IRIS® data platform SQL identifiers follow a set of naming conventions, which may be further restricted according to the use of the identifier. Identifiers are not case-sensitive.
An identifier may be either a simple identifier or a delimited identifier. The InterSystems SQL default is to support both simple identifiers and delimited identifiers.
Simple Identifiers
A simple identifier has the following syntax:
simple-identifier ::= identifier-start { identifier-part }
identifier-start ::= letter | % | _
identifier-part ::= letter | number | _ | @ | # | $
Naming Conventions
The identifier-start is the first character of an SQL identifier. It must be one of the following:
-
An uppercase or lowercase letter. A letter is defined as any character that passes validation by the ObjectScript $ZNAME function; by default these are the uppercase letters A through Z (ASCII 65–90), the lowercase letters a through z (ASCII 97–122), and the letters with accent marks (ASCII 192–255, exclusive of ASCII 215 and 247). InterSystems IRIS can use any valid Unicode (16-bit) letter character within an SQL identifier in any locale. Simple identifiers are not case-sensitive (however, see below). By convention they are represented with initial capital letters.
The Japanese locale does not support accented Latin letter characters in InterSystems IRIS names. Japanese names may contain (in addition to Japanese characters) the Latin letter characters A-Z and a-z (65–90 and 97–122) and any Unicode character.
-
An underscore (_).
-
A percent sign (%). InterSystems IRIS names beginning with a % character (except those beginning with %Z or %z) are reserved as system elements and should not be used as identifiers. For further details, refer to Rules and Guidelines for Identifiers.
The identifier-part is any of the subsequent characters of an SQL identifier. These remaining characters may consist of zero or more:
-
Letters (including Unicode characters).
-
Numbers. A number is defined as the digits 0 through 9.
-
Underscores (_).
-
At signs (@).
-
Pound signs (#).
-
Dollar signs ($).
Some symbol characters are also used as operators. In SQL, the # sign is used as the modulo operator. In SQL, the underscore character can be used to concatenate two strings; this usage is provided for compatibility with ObjectScript, the preferred SQL concatenation operator is ||. The interpretation of a symbol as an identifier character always take precedence over its interpretation as an operator. Any ambiguity concerning the correct parsing of a symbol character as an operator can be resolved by adding spaces before and after the operator.
A simple identifier cannot contain blank spaces or non-alphanumeric characters (other than those symbol characters specified above). The InterSystems SQL import tool removes blank spaces from imported table names.
SQL cursor names do not follow identifier naming conventions. For details on cursor naming conventions, refer to the DECLARE statement.
InterSystems SQL includes reserved words that cannot be used as simple identifiers. For a list of these reserved words, see Reserved Words; to test if a word is a reserved word use the $SYSTEM.SQL.IsReservedWord()Opens in a new tab method. However, a delimited identifier can be the same as an SQL reserved word.
Any identifier that does not follow these naming conventions must be represented as a delimited identifier within an SQL statement.
Case of Letters
InterSystems SQL identifiers by default are not case-sensitive. InterSystems SQL implements this by comparing identifiers after converting them to all uppercase letters. This has no effect on the actual case of the names being used. (Note that other implementations of SQL may handle case sensitivity of identifiers differently. For this reason, it is recommended that you avoid case-based identifiers.)
Note that cursor names and passwords in InterSystems SQL are case-sensitive.
Testing Valid Identifiers
InterSystems IRIS provides the IsValidRegularIdentifier()Opens in a new tab method of the %SYSTEM.SQLOpens in a new tab class, which tests whether a string is a valid identifier. It tests both for character usage and for reserved words. It also performs a maximum length test of 200 characters (this is an arbitrary length used to avoid erroneous input; it is not an identifier validation). The following ObjectScript example shows the use of this method:
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("Fred")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%Fred#123")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%#$@_Fred")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("_1Fred")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%#$")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("1Fred")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("Fr ed")
WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%sqlupper")
The first three method calls return 1, indicating a valid identifier. The fourth and fifth method calls also return 1; these are valid identifiers, although they are not valid for use as table or field names. The last three method calls return 0, indicating an invalid identifier. Two of these are invalid because they violate the character rules — in these cases by beginning with a number or containing a blank. The final method call returns 0 because the specified string is a reserved word. Note that these rule tests are a minimum requirement; they do not certify an identifier as valid for all SQL uses.
This method can also be called as a stored procedure from ODBC or JDBC: %SYSTEM.SQL_IsValidRegularIdentifier("nnnn").
Namespace Names
A namespace name (also referred to as a database name) follows identifier naming conventions, with additional restrictions on punctuation characters and maximum length. For further details, refer to the CREATE DATABASE command.
A namespace name can be delimited identifier and can be the same as an SQL reserved word. However, the same namespace name punctuation restrictions apply to both simple identifiers and delimited identifiers.
Identifiers and Class Entity Names
SQL table names, view names, field names, index names, trigger names, and procedure names are used to generate corresponding persistent class entities by stripping out non-alphanumeric characters. The generated names of class entities and globals follow these translation rules.
Namespace names and SQL schema names and corresponding package names do not follow these translation rules.
-
Identifiers that differ only in their inclusion of punctuation characters are valid. Because class object names cannot include punctuation characters, InterSystems IRIS generates corresponding unique object names by stripping out all punctuation characters. If stripping out the punctuation characters of an identifier results in a non-unique class object name, InterSystems IRIS creates a unique name by replacing the last alphanumeric character with an incremented character suffix.
For tables, views, fields, triggers, and procedure classmethod names, this is an integer suffix, beginning with 0. For example, myname and my_name generate myname and mynam0, adding my#name generates mynam1. If the number of generated unique names is larger than 10 (mynam9), additional names are generated by substituting a capital letter suffix, starting with A (mynamA). Because tables and views share the same name space, the same suffix counter is incremented for either a table or a view.
For index names, this suffix is a capital letter, beginning with A. For example, myindex and my_index generate myindex and myindeA.
If you have defined a name that ends in a suffix character (for example my_name0 or my_indexA, InterSystems IRIS handles unique name generation by incrementing to the next unused suffix.
-
Identifiers that have a punctuation character as the first character and a number as the second character are not valid for table names, view names, or procedure names. They are valid for field names and index names. If the first character of an SQL field name or index name is a punctuation character (% or _) and the second character is a number, InterSystems IRIS appends a lowercase “n” as the first character of the corresponding property name.
-
Identifiers that consist entirely of punctuation characters, or begin with two underscore characters (__name), or contains two pound signs together (nn##nn) are generally invalid as SQL entity names and should be avoided in all contexts.
You can configure translation of specific characters in SQL identifiers to other characters in corresponding object identifiers by creating a list of from/to character pairs. When converting an SQL identifier to an Objects identifier at DDL runtime, the characters in the “From” string are converted to the corresponding characters in the “To” string. These system-wide character translations facilitate the use of identifiers across environments where the rules for permitted identifier characters differ. Use the $SYSTEM.SQL.Util.SetDDLIdentifierTranslations()Opens in a new tab method to set from/to character pairings. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.
Specifying SQL Names in a Class Definition
When you define a persistent class that projects SQL entities, the name of each SQL entity are the same as the name of its corresponding persistent class definition element. To make an SQL table, field, or index name different, use the SqlTableName, SqlFieldName, or SqlName (for an index) keyword to specify the SQL name within your class definition. For example:
Property LName As %String [SqlFieldName = "Family#Name"];
Index NameIdx As %String [SqlName = "FullNameIndex"];
Identifier Length Considerations
The maximum length for SQL identifiers is 128 characters. When InterSystems IRIS maps an SQL identifier to the corresponding object entity, it creates the corresponding property, method, query, or index name with a maximum of 96 characters. If two SQL identifiers are identical for the first 96 characters, InterSystems IRIS replaces the 96th character of the corresponding object name with an integer (beginning with 0) to create a unique name.
The maximum length for schema and table names is subject to additional considerations and restrictions. Refer to Table Names and Schema Names.
Delimited Identifiers
A delimited identifier has the following syntax:
delimited-identifier ::= " delimited-identifier-part { delimited-identifier-part } "
delimited-identifier-part ::= non-double-quote-character | double-quote-symbol
double-quote-symbol ::= ""
A delimited identifier is a unique identifier enclosed by delimiter characters. InterSystems SQL supports double quote characters (") as delimiter characters. Delimited identifiers are generally used to avoid the naming restrictions of simple identifiers.
Note that InterSystems SQL uses single quote characters (') to delimit literals. For this reason, delimited identifiers must be specified using double quote characters ("), and literals must be specified using single quote characters ('). For example, '7' is the numeric literal 7, but "7" is a delimited identifier. When an SQL statement is enclosed in double quotes (for example, in Dynamic SQL), double quote characters within that string must be doubled.
An SQL empty string should always be specified as a pair of single quote characters ''. When delimited identifier support is enabled, a pair of double quote characters "" is parsed as an invalid delimited identifier and generates an SQLCODE -1 error.
Delimited Identifier Valid Names
A delimited identifier must be a unique name. Delimited identifiers are not case-sensitive; by convention, identifiers are represented with initial capital letters.
A delimited identifier can be the same as an SQL reserved word. Delimited identifiers are commonly used to avoid concerns about naming conflicts with SQL reserved words.
A delimited identifier may contain almost any printable character, including blank spaces. Most delimited identifier names cannot contain the following characters: comma (,), period (.), caret (^), and the two-character arrow sequence (->); however delimited identifier role names and user names may contain these characters. A delimited identifier classname may contain periods (.). No delimited identifier may begin with an asterisk (*). The following term cannot be used as a delimited identifier: %vid. Violating these naming conventions results in an SQLCODE -1 error.
A delimited identifier used as a table, schema, column, or index name must be able to be converted to a valid class entity name. Therefore, it must contain at least one alphanumeric character. A delimited identifier that begins with a number (or punctuation followed by a number) generates a corresponding class entity name with the letter “n” prefix.
The following example shows a query that makes use of delimited identifiers for both column and table names:
SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'
Note that the delimited identifiers are delimited with double quotes, and the string literal A% is delimited with single quotes.
When specifying a delimited identifier for a table name, you must separately delimit the table name and the schema name. Thus, "schema"."tablename" or schema."tablename" are valid identifiers, but "schema.tablename" is not a valid identifier.
Disabling Delimited Identifier Support
By default, support is enabled for delimited identifiers.
When delimited identifier support is disabled, characters within double quotes are treated as string literals.
You can set delimited identifier support system-wide using the SET OPTION command with the SUPPORT_DELIMITED_IDENTIFIERS keyword.
You can set delimited identifier support system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method DelimitedIdentifiers option. Delimited identifiers are supported by default.
To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.
Delimited identifiers are required by other features of InterSystems IRIS, such as InterSystems IRIS Business Intelligence. As such, take care when disabling them to ensure that other parts of your configuration are not affected.
SQL Reserved Words
SQL includes a long list of reserved words that cannot be used as identifiers. For a list of these reserved words, see Reserved Words.