Table Name
A table name can be qualified or unqualified.
-
An unqualified table name has the following syntax: tablename; it omits schema (and the period (.) character). An unqualified table name takes the default schema name. The initial system-wide default schema name is SQLUser, which corresponds to the default class package name User. Schema search path values are ignored.
The default schema name can be configured.
To determine the current system-wide default schema name, use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method.
-
A qualified table name has the following syntax: schema.tablename. It can specify either an existing schema name or a new schema name. Specifying an existing schema name places the table within that schema. Specifying a new schema name creates that schema (and associated class package) and places the table within that schema.
Table names and schema names follow SQL identifier naming conventions, subject to additional constraints on the use of non-alphanumeric characters, uniqueness, and maximum length. Names beginning with a % character are reserved for system use. By default, schema names and table names are simple identifiers, and are not case-sensitive.
InterSystems IRIS uses the table name to generate a corresponding class name. InterSystems IRIS uses the schema name is used to generate a corresponding class package name. A class name contains only alphanumeric characters (letters and numbers) and must be unique within the first 96 characters. To generate a class name, InterSystems IRIS first strips out symbol (non-alphanumeric) characters from the table name, and then generates a unique class name, imposing uniqueness and maximum length restrictions. To generate a package name, it then either strips out or performs special processing of symbol (non-alphanumeric) characters in the schema name. InterSystems IRIS then generates a unique package name, imposing uniqueness and maximum length restrictions. For further details on how package and class names are generated from schema and table names, refer to Table Names and Schema Names.
You can use the same name for a schema and a table. You cannot use the same name for a table and a view in the same schema.
A schema name is not case-sensitive; the corresponding class package name is case-sensitive. If you specify a schema name that differs only in case from an existing class package name, and the package definition is empty (contains no class definitions). InterSystems IRIS reconciles the two names by changing the case of the class package name. For further details on schema names, refer to Table Names and Schema Names.
InterSystems IRIS supports 16-bit (wide) characters for table and column names. For most locales, accented letters can be used for table names and the accent marks are included in the generated class name. The following example performs validation tests on an SQL table name:
TableNameValidation
SET tname="MyTestTableName"
SET x=$SYSTEM.SQL.IsValidRegularIdentifier(tname)
IF x=0 {IF $LENGTH(tname)>200
{WRITE "Tablename is too long" QUIT}
ELSEIF $SYSTEM.SQL.IsReservedWord(tname)
{WRITE "Tablename is reserved word" QUIT}
ELSE {
WRITE "Tablename contains invalid characters",!
SET nls=##class(%SYS.NLS.Locale).%New()
IF nls.Language [ "Japanese" {
WRITE "Japanese locale cannot use accented letters"
QUIT }
QUIT }
}
ELSE { WRITE tname," is a valid table name"}
Note:
The Japanese locale does not support accented letter characters in identifiers. Japanese identifiers may contain (in addition to Japanese characters) the Latin letter characters A-Z and a-z (65–90 and 97–122), the underscore character (95), and the Greek capital letter characters (913–929 and 931–937). The nls.Language test uses [ (the Contains operator) rather than = because there are different Japanese locales for different operating system platforms.
Existing Table
To determine if a table already exists in the current namespace, use $SYSTEM.SQL.Schema.TableExists("schema.tname")Opens in a new tab.
By default, when you try to create a table that has the same name as an existing table InterSystems IRIS rejects the create table attempt and issues an SQLCODE -201 error. To determine the current system-wide configuration setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL CREATE TABLE or CREATE VIEW for existing table or view setting. The default is 0; this is the recommended setting for this option. If this option is set to 1, InterSystems IRIS deletes the class definition associated with the table and then recreates it. This is much the same as performing a DROP TABLE, deleting the existing table and then performing the CREATE TABLE. In this case, it is strongly recommended that the $SYSTEM.SQL.CurrentSettings()Opens in a new tab, Does DDL DROP TABLE delete the table's data? value be set to 1 (the default). Refer to DROP TABLE for further details.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.
WITH table-option
The optional WITH clause can be specified after the SELECT query. The WITH clause can contain a comma-separated list of %CLASSPARAMETER clauses.
The %CLASSPARAMETER keyword enables you to define a class parameter as part of the CREATE TABLE AS SELECT command. A class parameter is always defined as a constant value. The %CLASSPARAMETER keyword is followed by the class parameter name, an optional equal sign, and the literal value (a string or number) to assign to that class parameter.
You can specify multiple %CLASSPARAMETER keyword clauses, defining one class parameter per clause. Multiple %CLASSPARAMETER clauses are separated by commas.
For example, by default CREATE TABLE AS SELECT creates an IDKEY index for the created table with a generated Global name, such as ^EPgS.D8T6.1; additional indexes use the same global name with a unique integer suffix. The following example shows how to specify an explicit Global name for the IDKEY index and future additional indexes:
CREATE TABLE Sample.YoungPeople
AS SELECT Name,Age
FROM Sample.People
WHERE Age<21
WITH %CLASSPARAMETER DEFAULTGLOBAL = '^GL.UNDERTWENTYONE'
For further details, refer to WITH Clause and %CLASSPARAMETER Keyword in the CREATE TABLE reference page.