docs.intersystems.com
Home  /  Application Development: Core Topics  /  Using InterSystems SQL  /  Identifiers


Using InterSystems SQL
Identifiers
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


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:
The identifier-part is any of the subsequent characters of an SQL identifier. These remaining characters may consist of zero or more:
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.
Note:
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 the Reserved Words section in the InterSystems SQL Reference; to test if a word is a reserved word use the $SYSTEM.SQL.IsReservedWord() 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() method of the %SYSTEM.SQL 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").
Identifiers and Class Entities
Table names, view names, field names, and index names are used to generate corresponding classes, properties, and indices by stripping out non-alphanumeric symbol characters. This imposes additional restrictions on the use of symbol characters in the names of these SQL entities:
By default, the name of an SQL entity (when stripped of its non-alphanumeric symbol characters) is the same as the name of its corresponding entity within a class definition. To make the SQL name different, specify an SQL alias within your class definition. For example:
Property Insert As %String [SqlFieldName = "X_Insert"];
You can configure translation of specific characters in SQL identifiers to other characters in corresponding object identifiers. This facilitates the use of identifiers across environments where the rules for permitted identifier characters differ.
Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings. On this screen you can view and edit the current settings of Identifier Translation — From and Identifier Translation — To. When converting an SQL identifier to an Objects identifier at DDL runtime, the characters in the “From” string are converted to the characters in the “To” string.
You can also use the SetDDLIdentifierTranslations() method of the %SYSTEM.SQL class.
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 in the “Defining Tables” chapter of this manual.
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.
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.
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. The following characters should be avoided in delimited identifier names: comma (,), period (.), caret (^), and the two-character arrow sequence (->). It may begin with any valid character, except the asterisk (*). The following term should not be used as a delimited identifier: %vid.
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.
Configuring Delimited Identifier Support
Within InterSystems SQL, support for delimited identifiers is optional. By default, support is enabled for both simple identifiers and delimited identifiers.
When delimited identifier support is disabled, characters within double quotes are treated as string literals.
Delimited identifier support for the current process can be set using the SET OPTION command with the SUPPORT_DELIMITED_IDENTIFIERS keyword. You can set delimited identifier support system-wide using either of the following:
The default is “Yes”, which specifies that InterSystems IRIS supports both simple identifiers and delimited identifiers system-wide. Setting this option to “No” specifies that InterSystems IRIS only supports simple identifiers for table names, field names, and other entities.
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 the Reserved Words section in the InterSystems SQL Reference.