Skip to main content

Table Names and Schema Names

Table Names and Schema Names

You can create a table either by defining the table (using CREATE TABLE) or by defining a persistent class that is projected to a table:

  • DDL: Caché uses the table name specified in CREATE TABLE to generate a corresponding persistent class name, and uses the specified schema name to generate a corresponding package name.

  • Class Definition: Caché uses the persistent class name to generate a corresponding table name, and uses the package name to generate a corresponding schema name.

The correspondence between these two names may not be identical for the following reasons:

  • Persistent classes and SQL tables follow different naming conventions. Different valid character and length requirements apply. Schema and table names are not case-sensitive; package and class names are case-sensitive. The system automatically converts a valid supplied name to a valid corresponding name, insuring that the generated name is unique.

  • The match between a persistent class name and the corresponding SQL table name is a default. You can use the SqlTableName class keyword to supply a different SQL table name.

  • The default schema name may not match the default package name. If you specify an unqualified SQL table name or persistent class name, the system supplies a default schema name or package name. The initial default schema name is SQLUser; the initial default package name is User.

Schema Name

A table, view, or stored procedure name is either qualified (schema.name) or unqualified (name).

  • If you specify a schema name (qualified name), when creating a table, view, or stored procedure, the specified item is assigned to that schema. If the schema does not exist, Caché SQL creates the schema and assigns the table, view, or stored procedure to it.

  • If you do not specify a schema name (unqualified name) when creating or referencing a table, view, or stored procedure, Caché SQL assigns a schema using either the default schema name or a schema search path, as described below.

Default Schema Name

  • When performing a DDL operation, such as creating or deleting a table, view, trigger, or stored procedure, an unqualified name is supplied the default schema name. Schema search path values are ignored.

  • When performing a DML operation, such as a SELECT, CALL, INSERT, UPDATE, or DELETE to access an existing table, view, or stored procedure, an unqualified name is supplied the schema name from the schema search path (if provided). If there is no schema search path, or the named item is not located using the schema search path, the default schema name is supplied.

The initial setting is to use the same default schema name for all namespaces (system-wide). You can set the same default schema name for all namespace, or set a default schema name for the current namespace.

If you create a table or other item with an unqualified name, Caché assigns it the default schema name, and the corresponding persistent class package name. If a named or default schema does not exist, Caché creates the schema (and package) and assigns the created item to the schema. If you delete the last item in a schema, Caché deletes the schema (and package). The following description of schema name resolution applies to table names, view names, and stored procedure names.

The initial system-wide default schema name is SQLUser. The corresponding persistent class package name is User. Therefore, either the unqualified table name Employee or the qualified table name SQLUser.Employee would generate the class User.Employee.

Because USER is a reserved word, attempting to specify a qualified name with the schema name of User (or any SQL Reserved Word) results in an SQLCODE -1 error.

To return the current default schema name, invoke the $SYSTEM.SQL.DefaultSchema()Opens in a new tab method:

  WRITE $SYSTEM.SQL.DefaultSchema()

Or use the following pre-processor macro:

#include %occConstant
  WRITE $$$DefSchema

You can change the default schema name using either of the following:

  • Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then SQL. On this screen you can view and edit the current system-wide setting of Default Schema. This option sets the default schema name system-wide. This system-wide setting can be overridden by a SetDefaultSchema() method value for the current namespace.

  • The $SYSTEM.SQL.SetDefaultSchema()Opens in a new tab method. By default, this method sets the default schema name system-wide. However, by setting the Boolean 3rd argument = 1, you can set the default schema for just the current namespace. When different namespaces have different default schema names, the DefaultSchema() method returns the default schema name for the current namespace.

Caution:

When you change the default SQL schema name, the system automatically purges all cached queries in all namespaces on the system. By changing the default schema name, you change the meaning of all queries that contain unqualified table, view, or stored procedure names. It is strongly recommended that the default SQL schema name be established at Caché installation and not subsequently modified.

The schema name is used to generate the corresponding class package name. Because these names have different naming conventions, they may not be identical.

You can create a schema with the same name as an SQL reserved word by setting this as the system-wide Default Schema, though this is not recommended. A default schema named User generates the corresponding class package name Use0, following the class naming uniqueness convention.

_CURRENT_USER Keyword

  • As Default Schema Name: If you specify _CURRENT_USER as the default schema name, Caché assigns the user name of the currently logged-in process as the default schema name. The _CURRENT_USER value is the first part of the $USERNAME ObjectScript special variable value. If $USERNAME consists of a name and a system address (Deborah@TestSys), _CURRENT_USER contains only the name piece; this means that _CURRENT_USER can assign the same default schema name to more than one user. If the process has not logged in, _CURRENT_USER specifies SQLUser as the default schema name.

    If you specify _CURRENT_USER/name as the default schema name, where name is any string of your choice, then Caché assigns the user name of the currently logged-in process as the default schema name. If the process has not logged in, name is used as the default schema name. For example, _CURRENT_USER/HMO uses HMO as the default schema name if the process has not logged in.

    In $SYSTEM.SQL.SetDefaultSchema(), specify "_CURRENT_USER" as a quoted string.

  • As Schema Name in DDL Command: If you specify _CURRENT_USER as the explicit schema name in a DDL statement, Caché replaces it with the current default schema name. For example, if the system-wide default schema is SQLUser, the command DROP TABLE _CURRENT_USER.OldTable drops SQLUser.OldTable. This is a convenient way to qualify a name to explicitly indicate that the system-wide default schema should be used. It is functionally identical to specifying an unqualified name. This keyword cannot be used in DML statements.

Schema Search Path

When accessing an existing table (or view, or stored procedure) for a DML operation, an unqualified name is supplied the schema name from the schema search path. Schemas are searched in the order specified and the first match is returned. If no match is found in the schemas specified in the search path, or no search path exists, the default schema name is used. (Note that the #import macro directive uses a different search strategy and does not “fall through” to the default schema name.)

  • In Embedded SQL you can use the #sqlcompile path macro directive or the #import macro directive to supply a schema search path that Caché uses to resolve unqualified names. #sqlcompile path resolves an unqualified name with the first match encountered. #import resolves an unqualified name if there is exactly one match for all the schemas listed in the search path.

  • The following example provides a search path containing two schema names:

    #sqlcompile path=Customers,Employees

    For further details, refer to “ObjectScript Macros and the Macro Preprocessor” in Using Caché ObjectScript.

  • In Dynamic SQL you can use the %SchemaPath property to supply a schema search path that Caché uses to resolve unqualified table names. You can specify the %SchemaPath property directly or specify it as the second parameter of the %SQL.StatementOpens in a new tab %New()Opens in a new tab method. The following example provides a search path containing two schema names:

      SET tStatement = ##class(%SQL.Statement).%New(0,"Customers,Employees")

    For further details, refer to “Using Dynamic SQL” in Using Caché SQL.

  • In SQL Shell you can set the PATH SQL Shell configuration parameter to supply a schema search path that Caché uses to resolve unqualified names.

If the unqualified name does not match any of the schemas specified in the schema search path or the default schema name, an SQLCODE -30 error is issued, such as the following: SQLCODE: -30 Message: Table 'PEOPLE' not found within schemas: CUSTOMERS,EMPLOYEES,SQLUSER.

Schema Naming Considerations

Schema names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters. A schema name should not be specified as a delimited identifier. Attempting to specify “USER” or any other SQL reserved word as a schema name results in an SQLCODE -1 or -312 error. The INFORMATION_SCHEMA schema name and the corresponding INFORMATION.SCHEMA package name are reserved in all namespaces. Users should not create tables/classes within this schema/package.

When you issue a create operation, such as CREATE TABLE, that specifies a schema that does not yet exist, Caché creates the new schema. Caché uses the schema name to generate a corresponding package name. Because the naming conventions for schemas and their corresponding packages differ, the user should be aware of name conversion considerations for non-alphanumeric characters. These name conversion considerations are not the same as for tables:

  • Initial character:

    • % (percent): Specify % as the first character of a schema name denotes the corresponding package as a system package, and all of its classes as system classes. This usage requires appropriate privileges; otherwise, this usage issues an SQLCODE -400 error with the %msg indicating a <PROTECT> error.

    • _ (underscore): If the first character of a schema name is the underscore character, this character is replaced by a lowercase “u” in the corresponding package name. For example, the schema name _MySchema generates the package name uMySchema.

  • Subsequent characters:

    • _ (underscore): If any character other than the first character of a schema name is the underscore character, this character is replaced by a period (.) in the corresponding package name. Because a period is the class delimiter, an underscore divides a schema into a package and a sub-package. Thus My_Schema generates the package My containing the package Schema (My.Schema).

    • @, #, $ characters: If a schema name contains any of these characters, these characters are stripped from the corresponding package name. If stripping these characters would produce a duplicate package name, the stripped package name is further modified: the final character of the stripped schema name is replaced by a sequential integer (beginning with 0) to produce a unique package name. Thus My@#$Schema generates package MySchema, and subsequently creating My#$Schema generates package MySchem0. The same rules apply to table name corresponding class names.

Platform-Specific Schema Names

When creating an ODBC-based query to run from Microsoft Excel via Microsoft Query on the Mac, if you choose a table from the list of those available, the generated query does not include the table’s schema (equivalent to the package for a class). For example, if you choose to return all the rows of the Person table from the Sample schema (in the Samples namespace), the generated query is:

SELECT * FROM Person

Because Caché interprets an unqualified table name as being in the SQLUser schema, this statement either fails or returns data from the wrong table. To correct this, edit the query (on the SQL View tab) to explicitly refer to the desired schema. The query should then be:

SELECT * FROM Sample.Person

Listing Schemas

The INFORMATION.SCHEMA.SCHEMATAOpens in a new tab persistent class lists all schemas in the current namespace.

The following example returns all non-system schema names in the current namespace:

SELECT SCHEMA_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA WHERE NOT SCHEMA_NAME %STARTSWITH '%'

The left side of the Management Portal SQL interface allows you to view the contents of a schema (or multiple schemas that match a filter pattern). See Filtering Schema Contents for further details.

Table Naming Considerations

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, Caché 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.TableExists()Opens in a new tab method. You can determine if a view name already exists using the $SYSTEM.SQL.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, Caché strips out these non-alphanumeric characters when generating the corresponding class or package name.

The following table name length limits apply:

  • Uniqueness: Caché performs uniqueness checking on the first 59 alphanumeric characters of the persistent class name. The corresponding SQL table name may be more than 59 characters long, but, when stripped of non-alphanumeric characters, it must be unique within this 59 character limit. Caché 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 in the Caché SQL Reference. For further details, on classes refer to “Caché Classes” in the Using Caché Objects manual.

FeedbackOpens in a new tab