docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Commands

InterSystems SQL Reference
CREATE TABLE
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


Creates a table definition.
Synopsis
CREATE [GLOBAL TEMPORARY] TABLE 
table (table-element-commalist)

table-element ::= 
     [%DESCRIPTION string] 
     [%FILE string] 
     [{%EXTENTSIZE | %NUMROWS} integer] 
     [%PUBLICROWID] 
     [%ROUTINE string] 
     [{ %CLASSPARAMETER param_name [=] value }]

     { fieldname datatype
           [IDENTITY] | [AUTO_INCREMENT] |
           {
             [ [COLLATE] sqlcollation ]
             [ UNIQUE ]
             [ NULL | NOT NULL ]
             [ PRIMARY KEY ]
             [ DEFAULT [(]default-spec[)] ]
             [ ON UPDATE update-spec ]
             [ COMPUTECODE { ObjectScript-code } 
                   [ COMPUTEONCHANGE (field-commalist) |
                     CALCULATED | TRANSIENT ] ]
                [ %DESCRIPTION literal ]
           } , }

    [ SHARD [ KEY (field-commalist)  [ COSHARD [ WITH ] [(] table [)] ] ] ]

     [{ [CONSTRAINT uname] 
          UNIQUE (field-commalist) }]

    [ [CONSTRAINT pkname] 
          PRIMARY KEY (field-commalist) ] 

     [{ [CONSTRAINT fkname] 
          FOREIGN KEY (field-commalist) REFERENCES table 
              [(reffield-commalist)]  [referential-action] }]


sqlcollation ::=
     { %EXACT | %MINUS | %MVR | %PLUS | %SPACE |   
        %SQLSTRING [(maxlen)] | %SQLUPPER [(maxlen)] |
        %TRUNCATE[(maxlen)]  }
This synopsis does not include keywords that are parsed for compatibility only, but perform no operation. These supported no-op keywords are listed in a separate section below.
Arguments
GLOBAL TEMPORARY Optional — This keyword clause creates the table as a temporary table.
table The name of the table to be created, specified as a valid identifier. A table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the system-wide default schema name.
table-element
A comma-separated list of one or more field definitions or keyword phrases.
Each field definition consists of (at minimum) a field name (specified as a valid identifier) followed by a data type.
A keyword phrase can consist of just a keyword (%PUBLICROWID), a keyword followed by literal, or a keyword (%CLASSPARAMETER) followed by a name and associated literal.
COLLATE sqlcollation Optional — Specify one of the following SQL collation types: %EXACT, %MINUS, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE, or %MVR. The default is the namespace default collation (%SQLUPPER, unless changed). %SQLSTRING, %SQLUPPER, and %TRUNCATE may be specified with an optional maximum length truncation argument, an integer enclosed in parentheses. The percent sign (%) prefix to these collation parameter keywords is optional. The COLLATE keyword is optional. For further details refer to Table Field/Property Definition Collation in the “Collation” chapter of Using InterSystems SQL.
uname
pkname
fkname
Optional — The name of a constraint, specified as a valid identifier. If specified as a delimited identifier, a constraint name can include the ".", "^", ",", and "->" characters. This optional constraint name is used in ALTER TABLE to identify a defined constraint.
field-commalist A field name or a comma-separated list of field names in any order. Used to define a unique, primary key, foreign key, or shard key constraint. All field names specified for a constraint must also be defined in the field definition. Must be enclosed in parentheses.
reffield-commalist Optional — A field name or a comma-separated list of existing field names defined in the referenced table specified in the foreign key constraint. If specified, must be enclosed in parentheses. If omitted, a default value is taken, as described in Defining Foreign Keys.
Description
The CREATE TABLE command creates a table definition of the structure specified. InterSystems IRIS automatically creates a persistent class corresponding to this table definition, with properties corresponding to the field definitions. CREATE TABLE defines the corresponding class as DdlAllowed. It does not specify an explicit StorageStrategy in the corresponding class definition; it uses the default storage %Storage.Persistent. By default, CREATE TABLE specifies the Final class keyword in the corresponding class definition, indicating that it cannot have subclasses. (You can change this default using the SetDDLFinal() method; to determine the current setting, call the $SYSTEM.SQL.CurrentSettings() method).
This reference page describes the following CREATE TABLE considerations:
SQL Security and Privileges
The CREATE TABLE command is a privileged operation. Prior to using CREATE TABLE it is necessary for your process to have %CREATE_TABLE privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation). You can use the GRANT command to assign %CREATE_TABLE privileges to a user or role, if you hold appropriate granting privileges.
This privileges requirement is configurable, using the $SYSTEM.SQL.SetSQLSecurity() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays an SQL Security ON: setting.
The default is “Yes” (1). When “Yes”, a user can only perform actions on a table or view for which that user has been granted privilege. This is the recommended setting for this option.
If this method is set to “No” (0), SQL Security is disabled for any new process started after changing this setting. This means privilege-based table/view security is suppressed. You can create a table without specifying a user. In this case, Dynamic SQL assigns “_SYSTEM” as user, and Embedded SQL assigns "" (the empty string) as user. Any user can perform actions on a table or view even if that user has no privileges to do so.
Embedded SQL does not use SQL privileges. In Embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges. You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login() method. For further information, refer to %SYSTEM.Security in the InterSystems Class Reference.
The following embedded SQL example creates the Employee table:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  NEW SQLCODE,%msg
  &sql(CREATE TABLE Employee (
     EMPNUM     INT NOT NULL,
     NAMELAST   CHAR(30) NOT NULL,
     NAMEFIRST  CHAR(30) NOT NULL,
     STARTDATE  TIMESTAMP,
     SALARY     MONEY,
     ACCRUEDVACATION   INT,
     ACCRUEDSICKLEAVE  INT,
     CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM))
  )
  IF SQLCODE=0 {WRITE !,"Table created"}
  ELSE {WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
This table, named Employee, has a number of defined fields. The EMPNUM field (containing the employee's company ID number) is an integer value that cannot be NULL; additionally, it is declared as a primary key for the table. The employee's last and first names each have a field, both of which are character strings with a maximum length of 30, that cannot be NULL. Additionally, there are fields for the employee's start date, accrued vacation time, and accrued sick time (which use the TIMESTAMP and INT data types).
Use the following program to delete the table created in the previous example:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  NEW SQLCODE,%msg
  &sql(DROP TABLE Employee)
  IF SQLCODE=0 {WRITE !,"Table deleted"}
  ELSE {WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
CREATE TABLE and INSERT
Embedded SQL is compiled SQL. In Embedded SQL you cannot both create a table and insert data into that table in the same program. The reason is as follows: Table creation is performed at runtime. However, the INSERT statement needs to verify the existence of the table at compile time. A SELECT statement needs to verify the existence of its table(s) at compile time, and thus has the same restriction.
A compiled program can freely combine CREATE TABLE statements with DML statements (such as INSERT and SELECT) that refer to other already-existing tables.
You can circumvent this restriction by directing the preprocessor to handle an Embedded SQL program as Deferred SQL. This is done using the #SQLCompile Mode=Deferred macro preprocessor directive, as described in the Preprocessor Directives Reference section of Using ObjectScript.
This restriction does not apply to Dynamic SQL, which is parsed at runtime.
You can create a table from an existing table definition and insert data from the existing table in a single operation using the $SYSTEM.SQL.QueryToTable() method.
Table Name
A table name can be qualified or unqualified.
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 in the “Defining Tables” chapter of Using InterSystems SQL.
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 in the “Defining Tables” chapter of Using InterSystems SQL.
InterSystems IRIS supports 16-bit (wide) characters for table and field 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.TableExists().
What happens when you try to create a table that has the same name as an existing table depends on a configuration setting. By default, InterSystems IRIS rejects an attempt to create a table with the name of an existing table and issues an SQLCODE -201 error. This is configurable system-wide using the $SYSTEM.SQL.SetDDLNo201() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Suppress SQLCODE=-201 Errors setting.
The default is “No” (0). This is the recommended setting for this option. If this option is set to “Yes” (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(), Does DDL DROP TABLE delete the table's data? value be set to 1 (the default). Refer to DROP TABLE for further details.
GLOBAL TEMPORARY Table
Specifying the GLOBAL TEMPORARY keyword defines the table as a global temporary table. The table definition is global (available to all processes); the table data is temporary (persists for the duration of the process). The corresponding class definition contains an additional Class parameter SQLTABLETYPE="GLOBAL TEMPORARY". Like standard InterSystems IRIS tables, the ClassType=persistent, and the class includes the Final keyword, indicating that it cannot have subclasses.
Regardless of which process creates a temporary table, the owner of the temporary table is automatically set to _PUBLIC. This means that all users can access a cached temporary table definition. For example, if a stored procedure creates a temporary table, the table definition can be accessed by any user that is permitted to invoke the stored procedure. This applies only to the temporary table definition; the temporary table data is specific to the invocation, and therefore can only be accessed by the current user process.
The table definition of a global temporary table is the same as a base table. A global temporary table must have a unique name; attempting to give it the same name as an existing base table results in an SQLCODE -201 error. The table persists until it is explicitly deleted (using DROP TABLE). You can alter the table definition using ALTER TABLE.
The table data (including Stream data) and indices in a global temporary table are temporary. They are stored in process-private globals. This means that this data is only available to the process that created the global temporary table, and this data is deleted when the process terminates.
The following embedded SQL example creates a global temporary table:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  NEW SQLCODE,%msg
  &sql(CREATE GLOBAL TEMPORARY TABLE TempEmp (
    EMPNUM     INT NOT NULL,
    NAMELAST   CHAR(30) NOT NULL,
    NAMEFIRST  CHAR(30) NOT NULL,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM))
  )
  IF SQLCODE=0 {WRITE !,"Table created"}
  ELSE {WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
%DESCRIPTION, %FILE, %EXTENTSIZE / %NUMROWS, %ROUTINE
These optional keyword phrases can be specified anywhere in the comma-separated list of table elements.
InterSystems SQL provides a %DESCRIPTION keyword, which you can use to provide a description for documenting a table or a field. %DESCRIPTION is followed by text string enclosed in single quotes. This text can be of any length, and can contain any characters, including blank spaces. (A single quote character within a description is represented by two single quotes. For example: 'Joe''s Table'.) A table can have a %DESCRIPTION. Each field of a table can have its own %DESCRIPTION, specified after the data type. If you specify more than one table-wide %DESCRIPTION for a table, InterSystems IRIS issues an SQLCODE -82 error. If you specify more than one %DESCRIPTION for a field, the system retains only the last %DESCRIPTION specified. In Studio, a description appears prefaced by three slashes on the line immediately before the corresponding table (Class) or field (Property). For example:
/// Joe's Table
InterSystems SQL provides a %FILE keyword, which is used to provide a file name for documenting a table. %FILE is followed by text string enclosed in single quotes. A table definition can have only one %FILE keyword; specifying multiples generates an SQLCODE -83 error.
InterSystems SQL provides the optional %EXTENTSIZE and %NUMROWS keywords, which are used to store an integer recording the anticipated number of rows in this table. These two keywords are synonymous; %EXTENTSIZE is the preferred term. When a table is being created to hold a known number of rows of data, especially if the initial number of rows is not likely to change subsequently (such as a table of states and provinces), setting %EXTENTSIZE can save space and improve performance. If not specified, the default initial allocation is 100,000 for a standard table, 50 for a temporary table. A table definition can have only one %EXTENTSIZE or %NUMROWS keyword; specifying multiples results in an SQLCODE -84 error. Once the table is populated with data, this %EXTENTSIZE value can be changed to the actual number of rows by running Tune Table. For further details, see “Optimizing Tables”.
InterSystems SQL provides a %ROUTINE keyword, which allows you to specify the routine name prefix for routines generated for this base table. %ROUTINE is followed by text string enclosed in single quotes. For example, %ROUTINE 'myname', generates code in routines named myname1, myname2, and so forth. You cannot call a user-defined (“extrinsic”) function from a %ROUTINE. A table definition can have only one %ROUTINE keyword; specifying multiples results in an SQLCODE -85 error. In Studio, the routine name prefix appears as the SqlRoutinePrefix value.
%CLASSPARAMETER Keyword
The optional %CLASSPARAMETER keyword enables you to define a class parameter as part of the CREATE TABLE command. A class parameter is always defined as a constant value. You can specify multiple %CLASSPARAMETER keyword clauses, defining one class parameter per clause. Like all table keyword clauses, %CLASSPARAMETER can be specified anywhere in the comma-separated list of table elements; multiple %CLASSPARAMETER clauses are separated by commas.
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. The following example defines two class parameters; the first %CLASSPARAMETER clause uses an equal sign, the second omits the equal sign:
CREATE TABLE OurEmployees (
    %CLASSPARAMETER DEFAULTGLOBAL = '^EMPLOYEE',
    %CLASSPARAMETER MANAGEDEXTENT 0,
    EMPNUM     INT NOT NULL,
    NAMELAST   CHAR(30) NOT NULL,
    NAMEFIRST  CHAR(30) NOT NULL,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM))
Some of the class parameters currently in use are: ALLOWIDENTITYINSERT, DEFAULTGLOBAL, DSINTERVAL, DSTIME, EXTENTQUERYSPEC, EXTENTSIZE, GUIDENABLED, IDENTIFIEDBY, MANAGEDEXTENT, READONLY, ROWLEVELSECURITY, SQLPREVENTFULLSCAN, USEEXTENTSET, VERSIONCLIENTNAME, VERSIONPROPERTY. Refer to the %Library.Persistent class for descriptions of these class parameters.
You can use the USEEXTENTSET and DEFAULTGLOBAL class parameters to define the global naming strategy for table data storage and index data storage.
IDENTIFIEDBY relationships must be converted to proper Parent/Child relationships to be supported in InterSystems IRIS.
A CREATE TABLE that defines a sharded table cannot define the VERSIONPROPERTY class parameter.
The user can specify additional class parameters as needed. For further details refer to Class Parameters in Defining and Using Classes.
Options Supported for Compatibility Only
InterSystems SQL accepts the following CREATE TABLE options for parsing purposes only, to aid in the conversion of existing SQL code to InterSystems SQL. These options do not provide any actual functionality.
{ON | IN} dbspace-name

LOCK MODE [ROW | PAGE]

[CLUSTERED | NONCLUSTERED]

WITH FILLFACTOR = literal

MATCH [FULL | PARTIAL]

CHARACTER SET identifier

COLLATE identifier  /* But note use of COLLATE keyword, described below */

NOT FOR REPLICATION
Field Definition
Following the table name, a set of parentheses contains the definitions of all of the fields (columns) of the table. Definitions of fields are separated by commas. By convention, each field definition is usually presented on a separate line and indentation is used; this is recommended, but not required. After the last field is defined, remember to provide a closing parenthesis for the field definition.
The parts of a field definition are separated by blank spaces. The field name is listed first, followed by its data characteristics. The data characteristics of a field are presented in the following sequence: the data type, the (optional) data size, then the (optional) data constraints. You can then append an optional field %DESCRIPTION to document the field.
Rather than defining a field, a field definition can reference an existing embedded serial object that defines multiple fields (properties). The field name is followed by the package and class name of the serial object. For example, Office Sample.Address. Do not specify a data type or data constraints; you can specify a %DESCRIPTION. You cannot create an embedded serial object using CREATE TABLE.
Note:
We recommend that you avoid creating tables with over 400 columns. Redesign your database so that either: these columns become rows; the columns are divided among several related tables; or the data is stored in fewer columns as character streams or bit streams.
Field Name
Field names follow identifier conventions, with the same naming restrictions as table names. Field names beginning with a % character should be avoided (field names beginning with %z or %Z are permitted). A field name should not exceed 128 characters. By default, field names are simple identifiers. They are not case-sensitive. Attempting to create a field name that differs only in letter case from another field in the same table generates an SQLCODE -306 error. For further details see the “Identifiers” chapter of Using InterSystems SQL.
InterSystems IRIS uses the field name to generate a corresponding class property name. A property name contains only alphanumeric characters (letters and numbers) and is a maximum of 96 characters in length. To generate this property name, InterSystems IRIS first strips punctuation characters from the field name, and then generates a unique identifier of 96 (or less) characters. InterSystems IRIS substitutes an integer (beginning with 0) for the final character of a field name when this is needed to create a unique property name.
The following example shows how InterSystems IRIS handles field names that differ only in punctuation. The corresponding class properties for these fields are named PatNum, PatNu0, and PatNu1:
CREATE TABLE MyPatients (
     _PatNum VARCHAR(16),
     %Pat@Num INTEGER,
     Pat_Num VARCHAR(30),
     CONSTRAINT Patient_PK PRIMARY KEY (_PatNum))
The field name, as specified in CREATE TABLE, is shown in the class property as the SqlFieldName keyword value.
During a dynamic SELECT operation, InterSystems IRIS may generate property name aliases to facilitate common letter case variants. For example, given the field name Home_Street, InterSystems IRIS might assign the property name aliases home_street, HOME_STREET, and HomeStreet. InterSystems IRIS does not assign an alias if that name would conflict with the name of another field name, or with an alias assigned to another field name.
Data Types
InterSystems SQL supports most standard SQL data types. A complete list of supported data types is provided in the Data Types section of this reference.
CREATE TABLE allows you to specify the same data type in several ways: VARCHAR(24), CHARACTER VARYING(24), %Library.String(MAXLEN=24), and %String(MAXLEN=24) all specify the same data type. Note however, that the default MAXLEN may differ: VARCHAR() and CHARACTER VARYING() default to MAXLEN=1; %Library.String and %String default to MAXLEN=50.
InterSystems IRIS maps these standard SQL data types to InterSystems IRIS data types by providing an SQL.SystemDataTypes mapping table and an SQL.UserDataTypes mapping table. SQL.UserDataTypes can be added to by the user to include additional user-defined data types.
To view and modify the current data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, System-defined DDL Mappings. To create additional data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, User-defined DDL Mappings.
If you specify a data type in SQL for which no corresponding InterSystems IRIS data type exists, the SQL data type name is used as the data type for the corresponding class property. You must create this user-defined InterSystems IRIS data type before DDL runtime (SQLExecute).
You may also override data type mappings for a single parameter value. For instance, suppose you didn't want VARCHAR(100) to map to the supplied standard mapping %String(MAXLEN=100). You could override this by added a DDL data type of 'VARCHAR(100)' to the table and then specify its corresponding InterSystems IRIS type. For example:
VARCHAR(100) maps to MyString100(MAXLEN=100)
Note:
A CREATE TABLE that defines a sharded table cannot contain stream data type fields, or a field of the ROWVERSION data type or SERIAL (%Library.Counter) data type.
Data Size
Following a data type, you can present the permissible data size in parentheses. Whitespace between the data type name and data size parentheses is permitted, but not required.
For a string, data size represents the maximum number of characters. For example:
ProductName VARCHAR (64)
For a numeric that permits fractional numbers, this is represented as a pair of integers (p,s). The first integer (p) is the data type precision, but it is not identical to numerical precision (the number of digits in the number). This is because the underlying InterSystems IRIS data type classes do not have a precision, but instead use this number to calculate the MAXVAL and MINVAL; The second integer (s) is the scale, which specifies the maximum number of decimal digits. For example:
UnitPrice NUMERIC(6,2)  /* maximum value 9999.99 */
To determine the maximum and minimum permissible values for a field, use the following ObjectScript functions:
  WRITE $$maxval^%apiSQL(6,2),!
  WRITE $$minval^%apiSQL(6,2)
Note that because p is not a digit count, it can be smaller than the scale s value:
  FOR i=0:1:6 {
      WRITE "Max for (",i,",2)=",$$maxval^%apiSQL(i,2),!}
For further details, refer to the Data Types reference page in this manual.
Field Data Constraints
Data constraints govern what values are permitted for a field, what the default value is for a field, and what type of collation is used for data values. All of these data constraints are optional. Multiple data constraints can be specified in any order, separated by a blank space. For further details, see field-constraint.
NULL and NOT NULL
The NOT NULL data constraint keyword specifies that this field does not accept a null value; in other words, every record must have a specified value for this field. NULL and empty string ('') are different values in InterSystems IRIS. You can input an empty string into a field that accepts character strings, even if that field is defined with a NOT NULL restriction. You cannot input an empty string into a numeric field. For further details, refer to the NULL section of the “Language Elements” chapter of Using InterSystems SQL.
The NULL data constraint keyword explicitly specifies that this field can accept a null value; this is the default definition for a field.
UNIQUE
The UNIQUE data constraint specifies that this field accepts only unique values. Thus, no two records can contain the same value for this field. The SQL empty string ('') is considered to be a data value, so with the UNIQUE data constraint applied, no two records can contain an empty string value for this field. A NULL is not considered to be a data value, so the UNIQUE data constraint does not apply to multiple NULLs. To restrict use of NULL for a field, use the NOT NULL keyword constraint.
A table defined as a sharded table has additional restrictions on the use of the UNIQUE data constraint. A unique constraint on a field or group of fields which does not include the shard key adds significant performance cost to inserts and updates. It is therefore recommended that this type of unique constraint be avoided when insert and update performance is an important consideration. See Evaluate Unique Constraints and Querying the Sharded Cluster in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
Refer to the Constraints option of Catalog Details for ways to list the fields of a table that are defined with a unique constraint.
DEFAULT
The DEFAULT data constraint specifies the default data value that InterSystems IRIS automatically provides for this field during an INSERT operation if the INSERT does not supply a data value for this field. If the INSERT operation supplies NULL for the field data value, the NULL is taken rather than the default data value. It is therefore common to specify both the DEFAULT and the NOT NULL data constraints for the same field.
The DEFAULT value can be supplied as a literal value or as a keyword option. A string supplied as a literal default value must be enclosed in single quotes. A numeric default value does not require single quotes. For example:
CREATE TABLE membertest
(MemberId INT NOT NULL,
Membership_status CHAR(13) DEFAULT 'M',
Membership_term INT DEFAULT 2)
The DEFAULT value is not validated when creating a table. When defined, a DEFAULT value can ignore data type, data length, and data constraint restrictions. However, when using INSERT to supply data to the table, the DEFAULT value is constrained; it is not limited by data type and data length restrictions, but is limited by data constraint restrictions. For example, a field defined Ordernum INT UNIQUE DEFAULT 'No Number' can take the default once, ignoring the INT data type restriction, but cannot take the default a second time, as this would violate the UNIQUE field data constraint.
If no DEFAULT is specified, the implied default is NULL. If a field has a NOT NULL data constraint, you must specify a value for that field, either explicitly or by DEFAULT. Do not use the SQL zero-length string (empty string) as a NOT NULL default value. Refer to NULL section of the “Language Elements” chapter of Using InterSystems SQL for further details on NULL and the empty string.
DEFAULT Keywords
The DEFAULT data constraint can accept a keyword option to define its value. The following options are supported: NULL, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, SYSDATE, and OBJECTSCRIPT.
The USER, CURRENT_USER, and SESSION_USER default keywords set the field value to the ObjectScript $USERNAME special variable, as described in the ObjectScript Reference.
The CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, and SYSDATE SQL functions can also be used as DEFAULT values. They are described in their respective reference pages. You can specify CURRENT_TIME or timestamp function with or without a precision value when used as a DEFAULT value. If no precision is specified, it will use the precision of the SQL configuration setting "Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP", which defaults to 0. The DEFAULT function uses the time precision setting in effect when the CREATE TABLE statement is prepared/compiled, not at the time of execution of the statement.
CURRENT_TIMESTAMP can be specified as the default for a field of data type %Library.PosixTime or %Library.TimeStamp; the current date and time is stored in the format specified by the field’s data type. CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, and SYSDATE can be specified as a default for a %Library.TimeStamp field (data type TIMESTAMP or DATETIME). InterSystems IRIS converts the date value to the appropriate format for the data type.
CREATE TABLE mytest
(TestId INT NOT NULL,
CREATE_DATE DATE DEFAULT CURRENT_TIMESTAMP(2),
WORK_START DATE DEFAULT SYSDATE)
You can use the TO_DATE function as the DEFAULT data constraint for data type DATE. You can use the TO_TIMESTAMP function as the DEFAULT data constraint for data type TIMESTAMP.
The OBJECTSCRIPT literal keyword phrase enables you to generate a default value by providing a quoted string containing ObjectScript code, as shown in the following example:
CREATE TABLE mytest
(TestId INT NOT NULL,
CREATE_DATE DATE DEFAULT OBJECTSCRIPT '+$HOROLOG' NOT NULL,
LOGNUM NUMBER(12,0) DEFAULT OBJECTSCRIPT '$INCREMENT(^LogNumber)')
See the ObjectScript Reference for further information.
ON UPDATE
The ON UPDATE clause makes the field computed with a COMPUTEONCHANGE value of %%UPDATE. This is a shortcut syntax for defining a field that will always be computed whenever a row is updated in the table. The most common use of this feature would be to define a column in a table that contains a timestamp value for the last time the row was updated.
Available update-spec options are:
CURRENT_DATE | CURRENT_TIME[(precision)] | CURRENT_TIMESTAMP[(precision)] | GETDATE([prec]) | GETUTCDATE([prec]) | SYSDATE | 
USER | CURRENT_USER | SESSION_USER | SYSTEM_USER |
 NULL | <literal> | -<number> 
The following example sets the RowTS field to the current timestamp value when a row is inserted and each time that row is updated:
CREATE TABLE mytest
(Name VARCHAR(48),
 RowTS TIMESTAMP DEFAULT Current_Timestamp(6) ON UPDATE Current_Timestamp(6) )
In this example the DEFAULT keyword sets RowTS to the current timestamp on INSERT if no explicit value is specified for the RowTS field. If an UPDATE specifies an explicit value for the RowTS field, the ON UPDATE keyword validates, but ignores, the specified value, and updates RowTS with the current timestamp. If the specified value fails validation, a SQLCODE -105 error is generated.
The following example sets the HasBeenUpdated field to a boolean value:
CREATE TABLE mytest
(Name VARCHAR(48),
 HasBeenUpdated TINYINT DEFAULT 0 ON UPDATE 1 )
The following example sets the WhoLastUpdated field to the current user name:
CREATE TABLE mytest
(Name VARCHAR(48),
 WhoLastUpdated VARCHAR(48) DEFAULT CURRENT_USER ON UPDATE CURRENT_USER )
You cannot specify an ON UPDATE clause if the field also has a COMPUTECODE data constraint. Attempting to do so results in an SQLCODE -1 error at compile/prepare time.
COMPUTECODE
The COMPUTECODE data constraint specifies ObjectScript code to compute a default data value for this field. The ObjectScript code is specified within curly braces. Within the ObjectScript code, SQL field names can be specified with curly brace delimiters. The ObjectScript code can consist of multiple lines of code. It can contain Embedded SQL. Whitespace and line returns are permitted before or after the ObjectScript code curly brace delimiters.
COMPUTECODE specifies the SqlComputeCode field name and computation for its value. When you specify a computed field name, either in COMPUTECODE or in the SqlComputeCode class property, you must specify the SQL field name, not the corresponding generated table property name. The SqlComputeCode property keyword is described in the Class Definition Reference.
A default data value supplied by compute code must be in Logical (internal storage) mode. Embedded SQL in compute code is automatically compiled and run in Logical mode.
The following example defines the Birthday COMPUTECODE field. It use ObjectScript code to compute its default value from the DOB field value:
CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Birthday VARCHAR(10) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")},
   Grade INT
   )
The COMPUTECODE can contain the pseudo-field reference variables {%%CLASSNAME}, {%%CLASSNAMEQ}, {%%OPERATION}, {%%TABLENAME}, and {%%ID}. These pseudo-fields are translated into a specific value at class compilation time. All of these pseudo-field keywords are not case-sensitive.
The COMPUTECODE value is a default; it is only returned if you did not supply a value to the field. The COMPUTECODE value is not limited by data type restrictions. The COMPUTECODE value is limited by the UNIQUE data constraint and other data constraint restrictions. If you specify both a DEFAULT and a COMPUTECODE, the DEFAULT is always taken.
COMPUTECODE can optionally take a COMPUTEONCHANGE, CALCULATED, or TRANSIENT keyword. The following keyword combination behaviors are supported:
If there is an error in the ObjectScript COMPUTECODE code, SQL does not detect this error until the code is executed for the first time. Therefore, if the value is first computed upon insert, the INSERT operation fails with an SQLCODE -415 error; if the value is first computed upon update, the UPDATE operation fails with an SQLCODE -415 error; if the value is first computed when queried, the SELECT operation fails with an SQLCODE -400 error.
A COMPUTECODE stored value can be indexed. The application developer is responsible for making sure that computed field stored values are validated and normalized (numbers in canonical form), based on their data type, especially if you define (or intend to define) an index for the computed field.
COMPUTEONCHANGE
By itself, COMPUTECODE causes a field value to be computed and stored in the database during INSERT; this value remains unchanged by subsequent operations. By default, subsequent UPDATE or trigger code operations do not change the computed value. Specifying the COMPUTEONCHANGE keyword causes subsequent UPDATE or trigger code operations to recompute and replace this stored value.
If you use the COMPUTEONCHANGE clause to specify a field or comma-separated list of fields, any change to the value of one of these fields causes InterSystems IRIS to recompute the COMPUTECODE field value.
If a field specified in COMPUTEONCHANGE is not part of the table specification, an SQLCODE -31 is generated.
In the following example, Birthday is computed upon insert based on the DateOfBirth value. Birthday is recomputed when DateOfBirth is updated:
CREATE TABLE SQLUser.MyStudents (
   Name VARCHAR(16) NOT NULL,
   DateOfBirth DATE,
   Birthday VARCHAR(40) COMPUTECODE {
        SET {Birthday}=$PIECE($ZDATE({DOB},9),",")
        _" changed: "_$ZTIMESTAMP }
        COMPUTEONCHANGE (DOB)
     )
Note:
An UPDATE to a DateOfBirth value that specifies the existing DateOfBirth value does not recompute the Birthday field value.
COMPUTEONCHANGE defines the SqlComputeOnChange keyword with the %%UPDATE value for the class property corresponding to the field definition. This property value is initially computed as part of the INSERT operation, and recomputed during an UPDATE operation. For a corresponding Persistent Class definition, refer to Defining a Table by Creating a Persistent Class in the “Defining Tables” chapter of Using InterSystems SQL.
CALCULATED and TRANSIENT
Specifying the CALCULATED or TRANSIENT keyword specifies that the COMPUTECODE field value is not saved in the database; it is calculated as part of each query operation that accesses it. This reduces the size of the data storage, but may slow query performance. Because these keywords cause InterSystems IRIS to not store the COMPUTECODE field value, these keywords and the COMPUTEONCHANGE keyword are mutually exclusive. The following is an example of a CALCULATED field:
CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Days2Birthday INT COMPUTECODE{SET {Days2Birthday}=$ZD({DOB},14)-$ZD($H,14)} CALCULATED
   )
CALCULATED defines the Calculated boolean keyword for the class property corresponding to the field definition. TRANSIENT defines the Transient boolean keyword for the class property corresponding to the field definition. These property keywords are described in the Class Definition Reference.
CALCULATED and TRANSIENT provide nearly identical behavior, with the following differences. TRANSIENT means that InterSystems IRIS does not store the property. CALCULATED means that InterSystems IRIS does not allocate any instance memory for the property. Thus when CALCULATED is specified, TRANSIENT is implicitly set.
TRANSIENT properties cannot be indexed. CALCULATED properties cannot be indexed unless the property is also SQLComputed.
Collation Parameters
The optional collation parameters specify what type of string collation to use when sorting values for a field. InterSystems SQL supports ten types of collation. If no collation is specified, the default is %SQLUPPER collation, which is not case-sensitive.
It is recommended that you specify the optional keyword COLLATE before the collation parameter for programming clarity, but this keyword is not required. The percent sign (%) prefix to the various collation parameter keywords is optional.
%EXACT collation follows the ANSI (or Unicode) character collation sequence. This provides case-sensitive string collation and recognizes leading and trailing blanks and tab characters.
The %SQLUPPER collation converts all letters to uppercase for the purpose of collation. For further details on not case-sensitive collation, refer to the %SQLUPPER function.
The %SPACE and %SQLUPPER collations append a blank space to the data. This forces string collation of NULL and numeric values.
The %SQLSTRING, %SQLUPPER, and %TRUNCATE collations provide an optional maxlen parameter, which must be enclosed in parentheses. maxlen is a truncation integer that specifies the maximum number of characters to consider when performing collation. This parameter is useful when creating indices with fields containing large data values.
The %PLUS and %MINUS collations handle NULL as a zero (0) value.
InterSystems SQL provides functions for most of these collation types. Refer to the %EXACT, %SQLSTRING, %SQLUPPER, %TRUNCATE functions for further details.
Note:
A shard key field can only take %EXACT, %SQLSTRING, or %SQLUPPER collation, with no truncation. See Querying the Sharded Cluster in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
ObjectScript provides the Collation() method of the %SYSTEM.Util class for data collation conversion.
Note:
To change the namespace default collation from %SQLUPPER (which is not case-sensitive) to another collation type, such as %SQLSTRING (which is case-sensitive), use the following command:
  WRITE $$SetEnvironment^%apiOBJ("collation","%Library.String","SQLSTRING")
After issuing this command, you must purge indexes, recompile all classes, then rebuild indexes. Do not rebuild indices while the table’s data is being accessed by other users. Doing so may result in inaccurate query results.
%DESCRIPTION
You can provide a description text for a field. This option follows the same conventions as providing a description text for a table. It is described with the other table elements, above.
Unique Fields Constraint
The unique fields constraint imposes a unique value constraint on the combined values of multiple fields. It has the following syntax:
CONSTRAINT uname UNIQUE (f1,f2)
This constraint specifies that the combination of values of fields f1 and f2 must always be unique, even though either of these fields by itself may take non-unique values. You can specify one, two, or more than two fields for this constraint.
All of the fields specified in this constraint must be defined in the field definition. If you specify a field in this constraint that does not also appear in the field definitions, an SQLCODE -86 error is generated. The specified fields should be defined as NOT NULL. None of the specified fields should be defined as UNIQUE, as this would make specifying this constraint meaningless.
Fields may be specified in any order. The field order dictates the field order for the corresponding index definition. Duplicate field names are permitted. Although you may specify a single field name in the UNIQUE fields constraint, this would be functionally identical to specify the UNIQUE data constraint to that field. A single-field constraint does provide a constraint name for future use.
You may specify multiple unique fields constraint statements in a table definition. Constraint statements can be specified anywhere in the field definition; by convention they are commonly placed at the end of the list of defined fields.
Refer to the Constraints option of Catalog Details for ways to list the fields of a table that are defined with a unique constraint.
The Constraint Name
The CONSTRAINT keyword and the unique fields constraint name are optional. The following are functionally equivalent:
CONSTRAINT myuniquefields UNIQUE (name,dateofbirth)
UNIQUE (name,dateofbirth)
The constraint name uniquely identifies the constraint, and is also used to derive the corresponding index name. Specifying CONSTRAINT name is recommended; this constraint name is required when using the ALTER TABLE command to drop a constraint from the table definition. The constraint name can be any valid identifier; if specified as a delimited identifier, a constraint name can include the ".", "^", ",", and "->" characters.
ALTER TABLE cannot drop a column that is listed in CONSTRAINT UNIQUE. Attempting to do so generates an SQLCODE -322 error.
RowID Record Identifier
In SQL, every record is identified by a unique integer value, known as the RowID. In InterSystems SQL you do not need to specify a RowID field. When you create a table and specify the desired data fields, a RowID field is automatically created. This RowID is used internally, but is not mapped to a class property. By default, its existence is only visible when a class is projected to an SQL table. In this projected SQL table, an additional RowID field appears. By default, this field is named "ID" and is assigned to column 1. For further details on the RowID, refer to RowID Field in the “Defining Tables” chapter in Using InterSystems SQL.
%PUBLICROWID
By default, the RowID is hidden and PRIVATE. Specifying the %PUBLICROWID keyword makes the RowID not hidden and public. If you specify the %PUBLICROWID keyword, the class corresponding to the table is defined with “Not SqlRowIdPrivate”. This optional keyword can be specified anywhere in the comma-separated list of table elements. ALTER TABLE cannot be used to specify %PUBLICROWID.
If the RowID is public:
Bitmap Extent Index
When you create a table using CREATE TABLE, by default InterSystems IRIS automatically defines a bitmap extent index for the corresponding class. The SQL MapName of the bitmap extent index is %%DDLBEIndex:
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
This bitmap extent index is not created in any of the following circumstances:
If, after creating a bitmap index, you invoke CREATE BITMAPEXTENT INDEX is run against a table where a bitmap extent index was automatically defined, the bitmap extent index previously defined is renamed to the name specified by the CREATE BITMAPEXTENT INDEX statement.
For DDL operations that automatically delete an existing bitmap extent index, refer to ALTER TABLE.
For further details refer to “Bitmap Extent Index” in the “Defining and Building Indices” chapter of SQL Optimization Guide.
IDENTITY Field
InterSystems SQL automatically creates a RowID field for each table, which contains a system-generated integer that serves as a unique record id. The optional IDENTITY keyword allows you to define a named field with the same properties as a RowID record id field. An IDENTITY field behaves as a single-field IDKEY index, whose value is a unique system-generated integer.
Defining an IDENTITY field prevents the defining of the Primary Key as the IDKEY.
Just as with any system-generated ID field, an IDENTITY field has the following characteristics:
Following an INSERT, UPDATE, or DELETE operation, you can use the LAST_IDENTITY function to return the value of the IDENTITY field for the most-recently modified record. If no IDENTITY field is defined, LAST_IDENTITY returns the RowID value of the most-recently modified record.
The following two Embedded SQL programs create a table with an IDENTITY field and then insert a record into the table, generating an IDENTITY field value. Note that in Embedded SQL the CREATE TABLE and INSERT statements must be in separate programs:
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(CREATE TABLE Employee (
  EmpNum INT NOT NULL,
  MyID   IDENTITY NOT NULL,
  Name   CHAR(30) NOT NULL,
  CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum))
  )
  IF SQLCODE'=0 {
    WRITE !,"CREATE TABLE error is: ",SQLCODE }
  ELSE {
   WRITE !,"Table created" }
  &sql(INSERT INTO Employee (EmpNum,Name) 
    SELECT ID,Name FROM SQLUser.Person WHERE Age >= '25')
  IF SQLCODE'=0 {
    WRITE !,"INSERT error is: ",SQLCODE }
  ELSE {
   WRITE !,"Record inserted into table" }
In this case, the primary key (EmpNum) is taken from the ID field of another table. Thus EmpNum values are unique integers, but (because of the WHERE clause) may contain gaps in their sequence. The IDENTITY field, MyID, assigns a user-visible unique sequential integer to each record.
ROWVERSION, SERIAL, and AUTO_INCREMENT Fields
InterSystems SQL provides three types of system-generated integer counter fields. All three data types are subclasses that extend the %Library.BigInt data type class.
Counter Type Scope of Counter Automatically Incremented by When User-supplied value is User-supplied values Duplicate Values Fields of this type Counter Reset by Sharded Table Support
AUTO_INCREMENT per-table INSERT NULL or 0 Allowed, does not affect system counter Allowed one per table TRUNCATE TABLE Yes
SERIAL per-serial counter field INSERT NULL or 0 Allowed, may increment system counter Allowed multiple per table TRUNCATE TABLE No
ROWVERSION namespace-wide INSERT and UPDATE   Not Allowed Not Allowed one per table not reset No
The following CREATE TABLE example defines these fields:
CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   AutoInc BIGINT AUTO_INCREMENT,
   Counter SERIAL,
   RowVer ROWVERSION
   )
Note that you can define an AUTO_INCREMENT field using the %Library.AutoIncrement data type, rather than the AUTO_INCREMENT keyword.
For further details on these counter fields, refer to RowVersion, AutoIncrement and Serial Counter Fields in the “Defining Tables” chapter of Using InterSystems SQL.
Defining the Shard Key
The option to define a table as sharded is provided to improve the performance of queries against that table, especially for tables containing a large number of records.
If the current namespace is configured for sharding (the master namespace on the shard master data server), you can specify a Shard Key for the table. If the current namespace is not configured for sharding, a CREATE TABLE that specifies a shard key fails with an SQLCODE -400 fatal error, with %msg ERROR #9319: Current namespace %1 has no shards configured. See Configure the Shard Master Data Server in the Scalability Guide.
There are three options for specifying a shard key:
You must define a table as a sharded table, either using CREATE TABLE or a persistent class definition. You cannot use ALTER TABLE to add a shard key to an existing table.
If a table has a defined IDENTITY field or IDKEY, the field(s) must be defined as the shard key field(s). You can neither specify SHARD KEY (fieldname) with fieldname not being this field, nor can you specify SHARD with no defined key field. Attempting to do so results in an SQLCODE -400 error, with a %msg such as the following: ERROR #5597: Sharded table's shard key (f2,f1) must be the same as the idkey (f1,f2) when the idkey (or identity field) is defined.
A unique fields constraint on a sharded table can have a significant negative impact on insert/update performance unless the shard key is a subset of the unique key. See Evaluate Unique Constraints in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
A table that is involved in complex transactions requiring atomicity should never be sharded.
A sharded table is defined in the master namespace on the shard master data server. This master namespace can also include non-sharded tables. Sharding is transparent to SQL queries; no special query syntax is required. A query does not need to know whether a table is sharded or non-sharded. The same query can access sharded and non-sharded tables. A query can include joins between sharded and non-sharded tables.
For further details, refer to Create Target Sharded Tables in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
Sharded Table Definition Restrictions
Defining a Primary Key
Defining a primary key is optional. When you define a table, IRIS automatically creates a generated field, the RowID Field (default name "ID") which functions as a unique row identifier. As each record is added to a table, InterSystems IRIS assigns a unique non-modifiable positive integer to that record’s RowID field. You can optionally define a primary key that also functions as a unique row identifier. A primary key allows the user to define a row identifier that is meaningful to the application. For example, a primary key might be an Employee ID field, or a Social Security Number, or a Patient Record ID field, or a inventory stock number.
You can explicitly define a field (or group of fields) as the primary record identifier by using the PRIMARY KEY clause. There are three syntactic forms for defining a primary key:
CREATE TABLE MyTable (Field1 INT PRIMARY KEY, Field2 INT)

CREATE TABLE MyTable (Field1 INT, Field2 INT, PRIMARY KEY (Field1))

CREATE TABLE MyTable (Field1 INT, Field2 INT, CONSTRAINT MyTablePK PRIMARY KEY (Field1))
The first syntax defines a field as the primary key; by designating it as the primary key, this field is by definition unique and not null. The second and third syntax can be used for a single field primary key but allow for a primary key consisting of more than one field. For example, PRIMARY KEY (Field1,Field2). If you specify a single field, this field is by definition unique and not null. If you specify a comma-separated list of fields, each field is defined as not null but may contain duplicate values, so long as the combination of the field values is a unique value. The third syntax allows you to explicitly name your primary key; the first two syntax forms generate a primary key name as follows: table name + “PKey” + constraint count integer. For further details on generated primary key names, refer to Constraints option of Catalog Details.
A primary key accepts only unique values and does not accept NULL. (The primary key index property is not automatically defined as Required; however, it effectively is required, since a NULL value cannot be filed or saved for a primary key field.) The collation type of a primary key is specified in the definition of the field itself.
Refer to the Constraints option of Catalog Details for ways to list the fields of a table that are defined as the primary key.
For further details, refer to Primary Key in the “Defining Tables” chapter in Using InterSystems SQL.
Primary Key As IDKEY
By default, the primary key is not the unique IDKEY index. In many cases this is preferable, because it enables you to update primary key values, set the collation type for the primary key, etc. There are cases where it is preferable to define the primary key as the IDKEY index. Be aware that this imposes the IDKEY restrictions on the future use of the primary key.
If you add a primary key constraint to an existing field, the field may also be automatically defined as an IDKEY index. This depends on whether data is present and upon a configuration setting established in one of the following ways:
However, if an IDENTITY field is defined in the table, the primary key can never be defined as the IDKEY, even when you have used one of these configuration setting to establish defining the primary key as the IDKEY.
InterSystems IRIS supports properties (fields) that are part of the IDKEY index to be SqlComputed. For example, a parent reference field. The property must be a triggered computed field. An IDKEY property defined as SqlComputed is only computed upon the initial save of a new Object or an INSERT operation. UPDATE computation is not supported, because fields that are part of the IDKEY index cannot be updated.
No Primary Key
In most cases, you should explicitly define a primary key. However, if a primary key is not designated, InterSystems IRIS attempts to use another field as the primary key for ODBC/JDBC projection, according to the following rules:
  1. If there is an IDKEY index on a single field, report the IDKEY field as the SQLPrimaryKey field.
  2. Else if the class is defined with SqlRowIdPrivate=0 (the default), report the RowID field as the SQLPrimaryKey field.
  3. Else if there is an IDKEY index, report the IDKEY fields as the SQLPrimaryKey fields.
  4. Else do not report an SQLPrimaryKey.
Multiple Primary Keys
You can only define one primary key. What happens when you try to specify more than one primary key for a table is configuration-dependent. By default, InterSystems IRIS rejects an attempt to define a primary key when one already exists, or to define the same primary key twice, and issues an SQLCODE -307 error. You can set this behavior system-wide using the $SYSTEM.SQL.SetDDLNo307() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Suppress SQLCODE=-307 Errors setting.
The default is “No” (0). If this option is set to “No”, InterSystems IRIS issues an SQLCODE -307 error when an attempt is made to add a primary key constraint to a table through DDL when a primary key constraint already exists for the table. The error is issued even if the second definition of the primary key is identical to the first definition.
For example, the following CREATE TABLE statement:
CREATE TABLE MyTable (f1 VARCHAR(16), 
CONSTRAINT MyTablePK PRIMARY KEY (f1))
creates the primary key (if none exists). A subsequent ALTER TABLE statement:
ALTER TABLE MyTable ADD CONSTRAINT MyTablePK PRIMARY KEY (f1)
generates an SQLCODE -307 error.
If the $SYSTEM.SQL.SetDDLNo307() option is set to “Yes” (1), InterSystems IRIS drops the existing primary key constraint and establishes the last-specified primary key as the table's primary key.
Defining Foreign Keys
A foreign key is a field that references another table; the value stored in the foreign key field is a value that uniquely identifies a record in the other table. The simplest form of this reference is shown in the following example, in which the foreign key explicitly references the primary key field CustID in the Customers table:
CREATE TABLE Orders (
   OrderID INT UNIQUE NOT NULL,
   OrderItem VARCHAR,
   OrderQuantity INT,
   CustomerNum INT,
   CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
   CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum) REFERENCES Customers (CustID)
   )
Most commonly, a foreign key references the primary key field of the other table. However, a foreign key can reference a RowID (ID) or an IDENTITY column. In every case, the foreign key reference must exist in the referenced table and must be defined as unique; the referenced field cannot contain duplicate values or NULL.
In a foreign key definition, you can specify:
If you define a foreign key and omit the referenced field name, the foreign key defaults as follows:
  1. The primary key field defined for the specified table.
  2. If the specified table does not have a defined primary key, the foreign key defaults to the IDENTITY column defined for the specified table.
  3. If the specified table has neither a defined primary key nor a defined IDENTITY column, the foreign key defaults to the RowID. This occurs only if the specified table defines the RowID as public; the specified table definition can do this explicitly, either by specifying the %PUBLICROWID keyword, or through the corresponding class definition with SqlRowIdPrivate=0 (the default). If the specified table does not defines the RowID as public, InterSystems IRIS issues an SQLCODE -315 error. You must omit the referenced field name when defining a foreign key on the RowID; attempting to explicitly specify ID as the referenced field name results in an SQLCODE -316 error.
If none of these defaults apply, InterSystems IRIS issues an SQLCODE -315 error.
Refer to the Constraints option of Catalog Details for ways to list the fields of a table that are defined as foreign key fields and the generated Constraint Name for a foreign key.
In a class definition you can specify a Foreign Key that contains a field based on a parent table IDKEY property, as shown in the following example:
  ForeignKey Claim(CheckWriterPost.Hmo,Id,Claim) References SQLUser.Claim.Claim(DBMSKeyIndex);
Because the parent field defined in a foreign key of a child has to be part of the parent class's IDKEY index, the only referential action supported for foreign keys of this type is NO ACTION.
If the foreign key field references a single field, the two fields must have the same data type and field data constraints.
In a parent/child relationship there is no defined ordering of the children. Application code must not rely on any particular ordering.
You can define a foreign key constraint that references a class in a database that is mounted read-only. To define a FOREIGN KEY, the user must have REFERENCES privilege on the table being referenced or on the columns of the table being referenced. REFERENCES privilege is required if the CREATE TABLE is executed via Dynamic SQL or xDBC.
Referential Action Clause
If a table contains a foreign key, a change to one table has an effect on another table. To keep the data consistent, when you define a foreign key, you also define what effect a change to the record from which the foreign key data comes has on the foreign key value.
A Foreign Key definition may contain two referential action clauses:
ON DELETE ref-action
and
ON UPDATE ref-action
The ON DELETE clause defines the DELETE rule for the referenced table. When an attempt to delete a row from the referenced table is made, the ON DELETE clause defines what action should be taken for the row(s) in the referencing table.
The ON UPDATE clause defines the UPDATE rule for the referenced table. When an attempt to change (update) the primary key value of a row from the referenced table is made, the ON UPDATE clause defines what action should be taken for the row(s) in the referencing table.
InterSystems SQL supports the following Foreign Key referential actions:
NO ACTION — When a row is deleted or its key value updated in the referenced table, all referencing tables are checked to see if any row references the row being deleted or updated. If so, the delete or update fails. (This constraint does not apply if the foreign key references itself.) NO ACTION is the default. NO ACTION is the only referential action supported for sharded tables. Any other referential action results in an SQLCODE -400 error with a message such as: ERROR #5600: Feature not supported for sharded class Sample.MyShardT: Foreign Key ON UPDATE action of 'setnull'.
SET NULL — When a row is deleted or its key value updated in the referenced table, all referencing tables are checked to see if any row references the row being deleted or updated. If so, the action causes the foreign key fields which reference the row being deleted or updated to be set to NULL. The foreign key field must allow NULL values.
SET DEFAULT — When a row is deleted or its key value updated in the referenced table, all referencing tables are checked to see if any row references the row being deleted or updated. If so, the action causes the foreign key fields which reference the row being deleted or updated to be set to the field's default value. If the foreign key field does not have a default value, it will be set to NULL. It is important to note that a row must exist in the referenced table which contains an entry for the default value.
CASCADE — When a row is deleted in the referenced table, all referencing tables are checked to see if any row references the row being deleted. If so, the delete causes rows whose foreign key fields which reference the row being deleted to be deleted as well.
When the key value of a row is updated in the referenced table, all referencing tables are checked to see if any row references the row being updated. If so, the update causes the foreign key fields which reference the row being updated to cascade the update to all referencing rows.
Your table definition should not have two foreign keys with different names that reference the same identifier-commalist field(s) and perform contradictory referential actions. In accordance with the ANSI standard, InterSystems SQL does not issue an error if you define two foreign keys that perform contradictory referential actions on the same field (for example, ON DELETE CASCADE and ON DELETE SET NULL). Instead, InterSystems SQL issues an error when a DELETE or UPDATE operation encounters these contradictory foreign key definitions.
Here is an embedded SQL example that issues a CREATE TABLE statement that uses both referential action clauses. Note that this example assumes a related table named Physician (with a primary key field of PhysNum) already exists.
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  &sql(CREATE TABLE Patient (
     PatNum VARCHAR(16),
     Name VARCHAR(30),
     DOB DATE,
     Primary_Physician VARCHAR(16) DEFAULT 'A10001982321',
     CONSTRAINT Patient_PK PRIMARY KEY (PatNum),
     CONSTRAINT Patient_Physician_FK FOREIGN KEY
          Primary_Physician REFERENCES Physician (PhysNum)
          ON UPDATE CASCADE
          ON DELETE SET NULL)
  )
  WRITE !,"SQL code: ",SQLCODE
For further information refer to the “Using Foreign Keys” chapter in Using InterSystems SQL.
Sharded Tables and Foreign Keys
Foreign keys are supported for any combination of sharded and unsharded tables, including: key table sharded, fkey table unsharded; key table unsharded, fkey table sharded; and both key table and fkey table sharded. The key in the referenced table may be the shard key, or may be another key. A foreign key may be a single field or multiple fields.
NO ACTION is the only referential action supported for sharded tables.
For further details, see Querying the Sharded Cluster in the chapter “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding” in the Scalability Guide.
Implicit Foreign Key
It is preferable to explicitly define all foreign keys. However, it is possible to project implicit foreign keys to ODBC/JDBC and the Management Portal.
If a foreign key is not explicitly defined, the rules for an implicit foreign key are as follows:
  1. If there is an explicit foreign key defined, InterSystems IRIS reports this constraint.
  2. Else, each reference column in the table is checked to see if the reference is to a table with an index that is a primary key and IDKEY. If so, InterSystems IRIS reports this reference as a foreign key constraint.
  3. Else, if the reference field is the parent reference field and the referenced table reports the RowID field as the implicit primary key field, InterSystems IRIS reports this parent reference as a foreign key constraint.
If any of these implicit foreign key constraints are covered by an explicit foreign key definition, the implicit foreign key constraint is not defined.
Examples: Dynamic SQL and Embedded SQL
The following examples demonstrate a CREATE TABLE using Dynamic SQL and Embedded SQL. Note that in Dynamic SQL you can create a table and insert data into the table in the same program; in Embedded SQL you must use separate programs to create a table and insert data into that table.
The last program example deletes the table, so that you may run these examples repeatedly.
The following Dynamic SQL example creates the table SQLUser.MyStudents. Note that because COMPUTECODE is ObjectScript code, not SQL code, the ObjectScript $PIECE function uses double quote delimiters; because the line of code is itself a quoted string, the $PIECE delimiters must be escaped as literals by doubling them, as shown:
CreateStudentTable
    SET stuDDL=5
    SET stuDDL(1)="CREATE TABLE SQLUser.MyStudents ("
    SET stuDDL(2)="StudentName VARCHAR(32),StudentDOB DATE,"
    SET stuDDL(3)="StudentAge INTEGER COMPUTECODE {SET {StudentAge}="
    SET stuDDL(4)="$PIECE(($PIECE($H,"","",1)-{StudentDOB})/365,""."",1)} CALCULATED,"
    SET stuDDL(5)="Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))"
  SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
  SET qStatus = tStatement.%Prepare(.stuDDL)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rtn = tStatement.%Execute()
  IF rtn.%SQLCODE=0 {WRITE !,"Table Create successful"}
  ELSEIF rtn.%SQLCODE=-201 {WRITE "Table already exists, SQLCODE=",rtn.%SQLCODE,!}  
  ELSE {WRITE !,"table create failed, SQLCODE=",rtn.%SQLCODE,!
        WRITE rtn.%Message,! }
The following Embedded SQL example creates the table SQLUser.MyStudents:
  &sql(CREATE TABLE SQLUser.MyStudents (
       StudentName VARCHAR(32),StudentDOB DATE,
       StudentAge INTEGER COMPUTECODE {SET {StudentAge}=
       $PIECE(($PIECE($H,",",1)-{StudentDOB})/365,".",1)} CALCULATED,
       Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))
       )
  IF SQLCODE=0 {WRITE !,"Created table" }
  ELSEIF SQLCODE=-201 {WRITE !,"SQLCODE=",SQLCODE," ",%msg }
  ELSE {WRITE !,"CREATE TABLE failed, SQLCODE=",SQLCODE } 
The following example deletes the table created by the prior examples:
  &sql(DROP TABLE SQLUser.MyStudents)
  IF SQLCODE=0 {WRITE !,"Table deleted" }
  ELSE {WRITE !,"SQLCODE=",SQLCODE," ",%msg }
See Also


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-19 06:56:35