Skip to main content

Table Name

Table Name

Every table has a unique name within its schema. A table has both an SQL table name and a corresponding persistent class name; these names differ in permitted characters, case-sensitivity, and maximum length. If defined using the SQL CREATE TABLE command, you specify an SQL table name that follows identifier conventions; the system generates a corresponding persistent class name. If defined as a persistent class definition, you must specify a name that contains only alphanumeric characters; this name is used as both the case-sensitive persistent class name and (by default) the corresponding non-case-sensitive SQL table name. The optional SqlTableName class keyword allows the user to specify a different SQL table name.

When you use the CREATE TABLE command to create a table, InterSystems IRIS uses the table name to generate a corresponding persistent class name. Because the naming conventions for tables and their corresponding classes differ, the user should be aware of name conversion considerations for non-alphanumeric characters:

  • Initial character:

    • % (percent): % as the first character of a table name is reserved and should be avoided (see Identifiers). If specified, the % character is stripped from the corresponding persistent class name.

    • _ (underscore): If the first character of a table name is the underscore character, this character is stripped from the corresponding persistent class name. For example, the table name _MyTable generates the class name MyTable.

    • Numbers: The first character of a table name cannot be a number. If the first character of the table name is a punctuation character, the second character cannot be a number. This results in an SQLCODE -400 error, with a %msg value of “ERROR #5053: Class name 'schema.name' is invalid” (without the punctuation character). For example, specifying the table name _7A generates the %msg “ERROR #5053: Class name 'User.7A' is invalid”.

  • Subsequent characters:

    • Letters: A table name must include at least one letter. Either the first character of the table name or the first character after initial punctuation characters must be a letter. A character is a valid letter if it passes the $ZNAME test; $ZNAME letter validation differs for different locales. (Note that $ZNAME cannot be used validate SQL identifiers because an identifier can contain punctuation characters.)

    • _ (underscore), @, #, $ characters: If a table name contains any of these characters, these characters are stripped from the corresponding class name and a unique persistent class name is generated. Because generated class names do not include punctuation characters, it is not advisable to create table names that differ only in their punctuation characters.

  • A table name must be unique within its schema. Attempting to create a table with a name that differs only in letter case from an existing table generates an SQLCODE -201 error.

    A view and a table in the same schema cannot have the same name. Attempting to do so results in an SQLCODE -201 error.

    You can determine if a table name already exists using the $SYSTEM.SQL.Schema.TableExists()Opens in a new tab method. You can determine if a view name already exists using the $SYSTEM.SQL.Schema.ViewExists()Opens in a new tab method. These methods also return the class name corresponding to the table or view name. The Management Portal SQL interface Catalog Details Table Info option displays the Class Name corresponding to the selected SQL table name.

Attempting to specify “USER” or any other SQL reserved word as a table name or schema name results in an SQLCODE -312 error. To specify an SQL reserved word as a table name or schema name, you can specify the name as a delimited identifier. If you use a delimited identifier to specify a table or schema name that contains non-alphanumeric characters, InterSystems IRIS strips out these non-alphanumeric characters when generating the corresponding class or package name.

The following table name length limits apply:

  • Uniqueness: InterSystems IRIS performs uniqueness checking on the first 189 characters of the persistent class name. The corresponding SQL table name may be more than 189 characters long, but, when stripped of non-alphanumeric characters, it must be unique within this 189 character limit. InterSystems IRIS performs uniqueness checking on the first 189 characters of a package name.

  • Recommended maximum length: as a general rule, a table name should not exceed 128 characters. A table name may be much longer than 96 characters, but table names that differ in their first 96 alphanumeric characters are much easier to work with.

  • Combined maximum length: a package name and its persistent class name (when added together) cannot exceed 220 characters. This includes the default schema (package) name (if no schema name was specified) and the dot character separating the package name and class name. A combined schema and table name can be longer than 220 characters when the characters in excess of 220 are stripped out when the table name is converted to the corresponding persistent class name.

For further details on table names, refer to the CREATE TABLE command. For further details, see Classes.

Next sectionRowID Field
Previous sectionSchema Name
FeedbackOpens in a new tab