CREATE TABLE (SQL)

Creates a table definition.

Synopsis

Basic Table Creation

CREATE TABLE [IF NOT EXISTS] table (column type, column2 type2, ...) CREATE TABLE [IF NOT EXISTS] table AS SELECT query ...

Column Constraints

CREATE TABLE table (column type NOT NULL, ...)   CREATE TABLE table (column type UNIQUE, ...)   CREATE TABLE table (UNIQUE (column, column2, ...), ...)   CREATE TABLE table (..., CONSTRAINT uniqueName UNIQUE (column, column2, ...))     CREATE TABLE table (column type PRIMARY KEY, ...)   CREATE TABLE table (..., PRIMARY KEY (column, column2, ...))   CREATE TABLE table (..., CONSTRAINT pKeyName PRIMARY KEY (column, column2, ...))    CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (column) REFERENCES refTable (refColumn))   CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (column, column2, ...) REFERENCES refTable (refColumn, refColumn2, ...))   CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES refTable))   CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... ON UPDATE refAction))   CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... ON DELETE refAction))   CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... NOCHECK))

Special Columns and Column Properties

CREATE TABLE table (column type DEFAULT defaultSpec, ...) CREATE TABLE table (column type COMPUTECODE [OBJECTSCRIPT | PYTHON ] {code}, ...)  CREATE TABLE table (column type COMPUTECODE ... {code} COMPUTEONCHANGE (column, column2, ...), ...)  CREATE TABLE table (column type COMPUTECODE ... {code} CALCULATED, ...)  CREATE TABLE table (column type COMPUTECODE ... {code} TRANSIENT, ...)  CREATE TABLE table (column type ON UPDATE updateSpec, ...)  CREATE TABLE table (column type IDENTITY, ...)

Table Options

CREATE TABLE table ...  SHARD CREATE TABLE table ... SHARD KEY (shardKeyColumn, shardKeyColumn2, ...)  CREATE TABLE table ... SHARD KEY (coshardKeyColumn) COSHARD WITH (coshardTable)  CREATE GLOBAL TEMPORARY TABLE table ...   CREATE TABLE table ... WITH %CLASSPARAMETER pName = pValue, %CLASSPARAMETER pName2 = pValue2, ...

Description

The CREATE TABLE command creates a table definition of the structure specified. CREATE TABLE creates both an SQL table and the corresponding InterSystems IRIS® class. For more details, see Class Definitions of Created Tables.

Note:

These syntaxes do not include keywords that are parsed for compatibility only but perform no operation. For more details on these keywords, see Options Supported for Compatibility Only.

Basic Table Creation

You can create a table by specifying column definitions and their data types. Alternatively, you can use a CREATE TABLE AS SELECT query to copy column definitions and data from an existing table.

CREATE TABLE [IF NOT EXISTS] table (column type, column2 type2, ...) creates a table containing one or more columns, each of the specified data type.
This statement creates a table with two columns. The first column accepts string values of up to 30 characters. The second column accepts valid dates.
```
CREATE TABLE Sample.Person (
    Name VARCHAR(30),
    DateOfBirth TIMESTAMP)
```
Example: Create and Populate Table
CREATE TABLE [IF NOT EXISTS] table AS SELECT query ... copies column definitions and column data from an existing table (or tables) into a new table based on the specified SELECT query. The SELECT query can specify any combination of tables or views. You may also specify a STORAGETYPE, %CLASSPARAMETER, or sharded table by supplying the relevant clauses.
This statement creates a new table, Sample.YoungPeople, based on a subset of data from the Sample.People table with a columnar storage type.
```
CREATE TABLE Sample.YoungPeople
AS SELECT Name,Age
FROM Sample.People
WHERE Age < 21
WITH STORAGETYPE = COLUMNAR
```

When creating a table, the user has the option to include the IF NOT EXISTS condition. Doing so suppresses the error if table already exists. For further details, see the following section on methods to check for existing tables.

Column Constraints

Column constraints govern what values are permitted for a column, what the default value is for a column, and whether the column values must be unique. You can also define primary and foreign key constraints on columns. You can specify multiple column constraints per column, in any order. Separate column constraints by a space.

NOT NULL Constraint

CREATE TABLE table (column type NOT NULL, ...) requires all records of the specified column to have a value defined, that is, not be NULL values.
This statement creates a table where neither column can be null.
```
CREATE TABLE Sample.Person (
    Name VARCHAR(30) NOT NULL,
    DateOfBirth TIMESTAMP NOT NULL)
```
The empty string ('') is not considered a null value. You can input an empty string into a column that accepts character strings, even if that column is defined with a NOT NULL restriction.
The NULL data constraint keyword (without NOT) explicitly specifies that this column can accept a null value. This is the default definition for a column.

Default Constraint

CREATE TABLE table (column type DEFAULT defaultSpec, ...) specifies the default data value that InterSystems IRIS provides automatically for this column during an INSERT operation if the INSERT does not supply a data value. If the INSERT operation inserts a NULL value into a column that specifies both a DEFAULT value and a NOT NULL constraint, the column uses the DEFAULT value. If the column does not define a NOT NULL constraint, then it uses the NULL value instead of the DEFAULT value.
This statement sets default values for the MembershipStatus and MembershipTerm columns.
```
CREATE TABLE Sample.Member (
    MemberId INT NOT NULL,
    MembershipStatus CHAR(13) NOT NULL DEFAULT 'M',
    MembershipTerm INT NOT NULL DEFAULT 2)
```

Unique Constraints

Unique constraints require that a column can contain only unique values. To see which columns have the unique constraint set, see Catalog Details for a Table.

CREATE TABLE table (column type UNIQUE, ...) constrains the specified column to accept only unique values. No two records can contain the same value for this column.
This statement sets the unique constraint on the UserName column:
```
CREATE TABLE Sample.People (
    UserName VARCHAR(30) UNIQUE NOT NULL,
    FirstName VARCHAR(30),
    LastName VARCHAR(30))
```
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 column. 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 column, use the NOT NULL keyword constraint.

Note:

In sharded tables, the unique constraint adds a significant performance cost to inserts and updates. If insert or update performance is important, avoid this constraint or include a shard key for the table. Note that sharded tables have additional restrictions on the UNIQUE constraint.
For more details on query performance, see Evaluate Unique Constraints and Querying the Sharded Cluster.
CREATE TABLE table (UNIQUE (column, column2, ...), ...) requires that all values for a specified group of columns, when concatenated together, result in a unique value. The individual columns do not need to be unique. You can specify this constraint at any location within the comma-separated list of columns being defined.
This statement requires that the combination of FirstName and LastName records in the created table are unique, even though FirstName and LastName records can individually contain duplicates.
```
CREATE TABLE Sample.People (
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    UNIQUE (FirstName,LastName))
```
CREATE TABLE table (..., CONSTRAINT uniqueName UNIQUE (column,column2, ...)) specifies a name for the UNIQUE constraint. If you want to drop a UNIQUE constraint from a table definition, then the ALTER TABLE command requires this constraint name.
This statement is functionally equivalent to the previous statement and names the constraint FirstLast.
```
CREATE TABLE Sample.People (
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    CONSTRAINT FirstLast UNIQUE (FirstName,LastName))
```

Primary Key Constraints

The PRIMARY KEY constraint designates a column, or combination of columns, as the primary key, constraining that column or columns to be unique and not null. Defining a primary key is optional. When you define a table, InterSystems IRIS automatically creates a generated column, the RowID Column (default name "ID"), which functions as a unique row identifier. For more details on the primary key, see Defining the Primary Key.

CREATE TABLE table (column type PRIMARY KEY, ...) designates a single column in the table as the primary key, constraining it be unique and not null.
This statement creates a table that designates the EmpNum column as the primary key:
```
CREATE TABLE Sample.Employee (
    EmpNum INT PRIMARY KEY,
    NameLast CHAR (30) NOT NULL,
    NameFirst CHAR (30) NOT NULL,
    StartDate TIMESTAMP,
    Salary MONEY)
```
In the Catalog Details section of the Management Portal, the generated primary key name has the form tablePKeyN, where table is the name of the table and N is the constraint count integer.
CREATE TABLE table (..., PRIMARY KEY (column, column2, ...)) designates one or more columns as the primary key. You can specify the PRIMARY KEY clause at any location within the comma-separated list of columns. Specifying a single column in this clause is functionally equivalent to specifying this clause on a specific column by using the previous syntax. If you specify a comma-separated list of columns in this clause, then each column is defined as not null but may contain duplicate values, so long as the combination of the column values is a unique value.
This statement designates the combination of the FirstName and LastName columns as the primary key:
```
CREATE TABLE Sample.People (
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    PRIMARY KEY (FirstName,LastName))
```
CREATE TABLE table (..., CONSTRAINT pKeyName PRIMARY KEY (column, column2, ...)) enables you to explicitly name your primary key. You can view the name of the primary key from the Catalog Details section of the Management Portal.
This statement is functionally equivalent to the first PRIMARY KEY syntax and additionally names the primary key EmployeePK.
```
CREATE TABLE Sample.Employee (
    EmpNum INT,
    NameLast CHAR (30) NOT NULL,
    NameFirst CHAR (30) NOT NULL,
    StartDate TIMESTAMP,
    Salary MONEY,
    CONSTRAINT EmployeePK PRIMARY KEY (EmpNum))
```

Foreign Key Constraints

The FOREIGN KEY constraint designates a column, or combination of columns, as a reference to another table. The value stored in the foreign key column uniquely identifies a record in the other table. You can designate more than one foreign key per table. Each foreign key reference must exist in the referenced table and must be defined as unique. The referenced column cannot contain duplicate values or NULL. For more details on foreign keys, see Defining a Foreign Key.

CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (column) REFERENCES refTable (refColumn)) designates a column from the table being created as a foreign key that references the refColumn column of the refTable reference table. The foreign key column and referenced column can have different names but they must have the same data type and column constraints. fKeyName specifies the name of the foreign key and is required.
This statement creates an Orders table that defines a foreign key named CustomersFK. With this foreign key, the values of the CustomerNum column are IDs specified in the CustID column of the Customers table.
```
CREATE TABLE Orders (
    OrderID INT,
    OrderItem VARCHAR,
    OrderQuantity INT,
    CustomerNum INT,
    CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
    CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum) REFERENCES Customers (CustID))
```
CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (column, column2, ...) REFERENCES refTable (refColumn, refColumn2, ...)) designates a combination of columns as the foreign key of the referenced columns. The foreign key columns and referenced columns must correspond in number of columns and in order listed.
This statement designates the CustomerNum and SalesPersonNum column combination of the Orders as the foreign key. These column values reference the corresponding CustID and SalespID columns of the Customers table.
```
CREATE TABLE Orders (
    OrderID INT,
    OrderItem VARCHAR,
    OrderQuantity INT,
    CustomerNum INT,
    SalesPersonNum INT,
    CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
    CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum,SalesPersonNum) REFERENCES Customers (CustID,SalespID))
```
CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES refTable)) omits the reference column name. The foreign key of the column, or combination of columns, defaults to the primary key of the reference table (if defined), otherwise the IDENTITY column of the reference table (if defined), and otherwise the RowID column of the reference table.
This statement sets a foreign key in which the CustomerNum column references the primary key of the Customers table, assuming that this table has the primary key defined.
```
CREATE TABLE Orders (
    OrderID INT,
    OrderItem VARCHAR,
    OrderQuantity INT,
    CustomerNum INT,
    SalesPersonNum INT,
    CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
    CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum) REFERENCES Customers)
```
CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... ON UPDATE refAction)) defines the UPDATE rule for the reference table. When you attempt to change the primary key value of a row from the reference table, the ON UPDATE clause defines what action to take for the rows in that table. Valid reference action values are NO ACTION (default), SET DEFAULT, SET NULL, and CASCADE. You can specify this clause in conjunction with the ON DELETE clause.
This statement creates a table that, when the reference column CustID is updated, the foreign key column CustomerNum receives the same update.
```
CREATE TABLE Orders (
    OrderID INT,
    OrderItem VARCHAR,
    OrderQuantity INT,
    CustomerNum INT,
    CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
    CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum) REFERENCES Customers (CustID)
        ON UPDATE CASCADE)
```
CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... ON DELETE refAction)) defines the DELETE rule for the reference table. When you attempt to delete a row from the reference table, the ON DELETE clause defines what action to take for rows in that table. Valid reference action values are NO ACTION (default), SET DEFAULT, SET NULL, and CASCADE. You can specify this clause in conjunction with the ON UPDATE clause.
This statement creates a table that cascades updates of reference column values to the foreign key column, but if a reference column value is deleted, the corresponding foreign key values are set to NULL.
```
CREATE TABLE Orders (
    OrderID INT,
    OrderItem VARCHAR,
    OrderQuantity INT,
    CustomerNum INT,
    CONSTRAINT OrdersPK PRIMARY KEY (OrderID),
    CONSTRAINT CustomersFK FOREIGN KEY (CustomerNum) REFERENCES Customers (CustID)
        ON UPDATE CASCADE
        ON DELETE SET NULL)
```
CREATE TABLE table (..., CONSTRAINT fKeyName FOREIGN KEY (...) REFERENCES ... NOCHECK)) disables checking for referential integrity of the foreign key, meaning that an INSERT or UPDATE operation might specify a value for a foreign key column that does not correspond to a row in the reference table.
The NOCHECK keyword also prevents the execution of the ON DELETE or ON UPDATE referential actions for the foreign key, if these actions are specified. The SQL query processor can use foreign keys to optimize joins among tables. However, if a foreign key is defined as NOCHECK, the SQL query processor does not consider it as defined. A NOCHECK foreign key is still reported to database driver catalog queries as a foreign key. For more information, see Using Foreign Keys.

Special Columns and Column Properties

Computed Columns

These syntaxes show how to define columns that are computed on INSERT or UPDATE rather than user-supplied. For more details on these columns, see Computing a column value on INSERT or UPDATE.

CREATE TABLE table (column type COMPUTECODE [OBJECTSCRIPT | PYTHON] {code}, ...) defines a column in which values are computed and stored upon INSERT using the specified ObjectScript or Python code. If you omit the OBJECTSCRIPT or PYTHON keyword, the code defaults to ObjectScript. The values in computed columns remain unchanged by subsequent table updates, such as an UPDATE command or trigger code operations.

This statement creates a table that, when a row is inserted, computes the Age column based on the date specified in the DOB column.

CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Age VARCHAR(12) COMPUTECODE {
        set bdate = $zdate({DOB}, 8)
        set today = $zdate($horolog,8)
        set {Age} = $select(bdate = "":"", 1:(today - bdate) \ 10000)},
   Grade INT)

CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Age VARCHAR(12) COMPUTECODE PYTHON {
        import datetime as d
        iris_date_offset = d.date(1840,12,31).toordinal()
        bdate = d.date.fromordinal(cols.getfield('DOB') + iris_date_offset).strftime("%Y%m%d")
        today = d.date.today().strftime("%Y%m%d")
        return str((int(today) - int(bdate)) // 10000) if bdate else ""},
   Grade INT)

CREATE TABLE table (column type COMPUTECODE ... {code} COMPUTEONCHANGE (column, column2, ...), ...) recomputes the value of the computed column when any one of the table columns specified in the COMPUTEONCHANGE clause changes in a subsequent table update. The recomputed value replaces the previously stored value. If a column specified in COMPUTEONCHANGE is not part of the table specification, then InterSystems SQL generates an SQLCODE -31 error.
This statement recomputes the Age column when the DOB column is updated. The Birthday column includes the timestamp for when the column last changed.
```
CREATE TABLE MyStudents (
    Name VARCHAR(20) NOT NULL,
    DOB TIMESTAMP,
    Birthday VARCHAR(40) COMPUTECODE {
        set {Birthday} = $zdate({DOB})
        _" changed: "_$zdatetime($ztimestamp) }
        COMPUTEONCHANGE (DOB))
```
COMPUTEONCHANGE defines the SqlComputeOnChange keyword with the %%UPDATE value for the class property corresponding to the column 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, see Defining a Table by Creating a Persistent Class.
CREATE TABLE table (column type COMPUTECODE ... {code} CALCULATED, ...) specifies that the column value is not stored in the database but is instead generated each time the column is queried. Calculated columns reduce the size of the data storage but can slow query performance.
This column defines the Calculated boolean keyword for the class property corresponding to the column definition. CALCULATED properties cannot be indexed unless the property is also SQLComputed.
This statement calculates the value of the DaysToBirthday column, which changes depending on the current date. The {*} code is a shortcut syntax for specifying the column being computed, in this case DaysToBirthday.
```
CREATE TABLE MyStudents (
    Name VARCHAR(20) NOT NULL,
    DOB TIMESTAMP,
    DaysToBirthday INT COMPUTECODE {
        set {*} = $zdate({DOB},14) - $zdate($horolog,14) } CALCULATED)
```
CREATE TABLE table (column type COMPUTECODE ... {code} TRANSIENT, ...) is similar to CALCULATED and also specifies that the column is not saved to the database.
This column defines the Transient boolean keyword for the class property corresponding to the column definition. TRANSIENT properties cannot be indexed.
The CALCULATED and TRANSIENT keywords are mutually exclusive and provide similar behavior. 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.
CREATE TABLE table (column type ON UPDATE updateSpec, ...) defines a column that is recomputed whenever a row is updated in the table, based on the value specified by updateSpec. You cannot specify an ON UPDATE clause if the column also has a COMPUTECODE data constraint.
This statement creates a table containing a LastUpdated column whose values are updated to the current time any time the corresponding rows are updated. The timestamp values stored in the table have a precision of two digits.
```
CREATE TABLE MyStudents (
    Name VARCHAR(20) NOT NULL,
    DOB TIMESTAMP,
    LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2))
```
CREATE TABLE table (column type IDENTITY, ...) replaces the system-generated integer RowID column with the specified named column.
Like the RowID column, this column behaves as a single-column IDKEY index whose values are unique system-generated integers, where each value serves as a unique record ID for the corresponding table row. Defining an IDENTITY column prevents the defining of the Primary Key as the IDKEY. You can define only one IDENTITY column per table. type must be an integer data type. If you omit type, then the data type is defined as BIGINT. The IDENTITY values cannot be user-specified and cannot be modified in an UPDATE statement.
This statement sets the IdNum column as the IDKEY. This column is returned as part of selection queries such as SELECT *.
```
CREATE TABLE Employee (
    EmpNum INT NOT NULL,
    IdNum IDENTITY NOT NULL,
    Name CHAR(30) NOT NULL,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum))
```

For more details about working with the IDENTITY column, see Creating Named RowId Column Using IDENTITY Keyword.

Counter Columns

InterSystems SQL provides three types of system-generated integer counter columns. These columns are not mutually exclusive and can be specified together within the same table. The data types of all three columns map to the %Library.BigIntOpens in a new tab data type class.

Counter Type	Scope of Counter	Automatically Incremented by	When User-supplied value is	User-supplied values	Duplicate Values	Columns 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 column	INSERT	NULL or 0	Allowed, may increment system counter	Allowed	Multiple per table	TRUNCATE TABLE	No
ROWVERSION	Namespace-wide	INSERT and UPDATE	Not applicable	Not allowed	Not allowed	One per table	Not reset	No

For more details on these counter columns, see RowVersion, AutoIncrement and Serial Counter Columns.

CREATE TABLE table (column type AUTO_INCREMENT, ...) creates a counter column that increments upon each INSERT into the table. You can designate only one AUTO_INCREMENT counter column per table. You must set the AUTO_INCREMENT keyword after an explicit integer data type. For example:
```
CREATE TABLE MyStudents (
    Name VARCHAR(16) NOT NULL,
    DOB TIMESTAMP,
    AutoInc BIGINT AUTO_INCREMENT)
```
Alternatively, you can define an AUTO_INCREMENT column using the %Library.AutoIncrementOpens in a new tab data type. Thus the following are also valid column definition syntax: MyAutoInc %AutoIncrement, MyAutoInc %AutoIncrement AUTO_INCREMENT.
CREATE TABLE table (column SERIAL, ...) creates a counter column that increments upon each INSERT into the table. You can designate multiple columns as SERIAL counter columns. Specify the SERIAL keyword in place of an explicit data type. For example:
```
CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
    DOB TIMESTAMP,
    Counter SERIAL)
```
CREATE TABLE table (column ROWVERSION, ...) creates a counter column that increments upon each INSERT or UPDATE operation across all tables in the namespace. Specify the ROWVERSION keyword in place of an explicit data type. For example:
```
CREATE TABLE MyStudents (
    Name VARCHAR(16) NOT NULL,
    DOB TIMESTAMP,
    RowVer ROWVERSION)
```

%DESCRIPTION Keyword

CREATE TABLE table (..., %DESCRIPTION description) specifies a description for the table being created. Enclose the description text string in quotes. For example:

CREATE TABLE Employee (
    %Description 'Employees at XYZ Inc.',
    EmpNum INT PRIMARY KEY,
    NameLast VARCHAR(30) NOT NULL,
    NameFirst VARCHAR(30) NOT NULL,
    StartDate TIMESTAMP)

CREATE TABLE table (column type %DESCRIPTION description, ...) specifies a description for a column. You can specify one description per column. Enclose the description text string in quotes. For example:

CREATE TABLE Employee (
    EmpNum INT PRIMARY KEY,
    NameLast VARCHAR(30) NOT NULL,
    NameFirst VARCHAR(30) NOT NULL,
    StartDate TIMESTAMP %Description 'Format: MM/DD/YYYY')

%PUBLICROWID Keyword

CREATE TABLE table (%PUBLICROWID, ...) makes the unique, system-generated integer RowID column public. For example:
```
CREATE TABLE Employee (
    %PUBLICROWID,
    EmpNum INT PRIMARY KEY,
    NameLast VARCHAR(30) NOT NULL,
    NameFirst VARCHAR(30) NOT NULL,
    StartDate TIMESTAMP)
```
This column is named "ID" and is assigned to column 1. The class corresponding to the table is defined with “Not SqlRowIdPrivate”. ALTER TABLE cannot be used to specify %PUBLICROWID.
If the RowID is public:
- RowID values are displayed by SELECT *.
- The RowID can be used as a foreign key reference.
- If there is no defined primary key, the RowID is treated as an Implicit PRIMARY KEY constraint with the Constraint Name RowIDField_As_PKey.
- The table cannot be used to copy data into a duplicate table without specifying the column names to be copied.
For more details of the RowID column, see RowID Hidden?.

Collation Property

CREATE TABLE table (column type COLLATE sqlCollation, ...) sets the collation type used to order and compare values in the specified column. Valid collation values are %EXACT, %MINUS, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE, and %MVR.
This statement sorts the UserName column as a case-sensitive string, treating NULL and numeric values in the column as string characters.
```
CREATE TABLE Sample.People (
    UserName VARCHAR(30) COLLATE %SQLSTRING,
    FirstName VARCHAR(30),
    LastName VARCHAR(30))
```

Table Options

Sharded Tables

These syntaxes provide the option to define a sharded table, where table rows are automatically horizontally partitioned across data nodes by using one of its columns as a shard key. A sharded table improves the performance of queries against that table, especially for tables containing a large number of rows. To improve the performance of queries that join large tables, you can coshard the tables. Cosharding partitions rows in different tables that have matching shard key values into the same data nodes. For more details on sharding, see Horizontally Scaling for Data Volume with Sharding.

Note:

Not all CREATE TABLE syntaxes support sharded tables. For more details, see Sharded Table Restrictions.

CREATE TABLE table ... SHARD defines a sharded table and uses the RowID column as the shard key. This is known as a system-assigned shard key (SASK). If the table has a defined IDENTITY column and no explicit shard key, InterSystems SQL uses the IDENTITY column as the SASK instead. Using a SASK is the simplest and most effective way to shard a table. Specify the SHARD keyword after the column definitions.
This statement creates a sharded table that uses the default RowID column as the shard key.
```
CREATE TABLE Vehicle (
    Make VARCHAR(30) NOT NULL,
    Model VARCHAR(20) NOT NULL,
    Year INT NOT NULL,
    Vin CHAR(17) NOT NULL)
SHARD
```
CREATE TABLE table ... SHARD KEY (shardKeyColumn, shardKeyColumn2, ...) specifies a column, or comma-separated list of columns, to use as the shard key. This is known as a user-defined shard key (UDSK).
This statement creates a table with a shard key composed of two columns.
```
CREATE TABLE Car (
    Owner VARCHAR(30) NOT NULL,
    Plate VARCHAR(10) NOT NULL,
    State CHAR(2) NOT NULL)
SHARD KEY (Plate, State)
```
You can also use this syntax to coshard two or more tables that you are defining. Joins on the UDSK columns of cosharded tables perform much more efficiently than joins on non-UDSK columns, so defining UDSKs on the most frequently used set of join columns is recommended.
These statements create two tables with defined shard keys on the columns Vin and VehicleNumber.
```
CREATE TABLE Vehicle (
    Make VARCHAR(30) NOT NULL,
    Model VARCHAR(20) NOT NULL,
    Year INT NOT NULL
    Vin CHAR(17) NOT NULL)
SHARD KEY (Vin)

CREATE TABLE Citation (
    CitationID VARCHAR(8) NOT NULL,
    Date TIMESTAMP NOT NULL,
    LicenseNumber VARCHAR(12) NOT NULL
    Plate VARCHAR(10) NOT NULL,
    VehicleNumber CHAR(17) NOT NULL)
SHARD KEY (VehicleNumber)
```
These tables benefit from improved JOIN performance when joining on the UDSK column, such as this query that returns traffic citations associated with a vehicle:
```
SELECT * FROM Citation, Vehicle WHERE Citation.VehicleNumber = Vehicle.Vin
```
For more details on choosing a shard key, see Choose a Shard Key.
CREATE TABLE table ... SHARD KEY (coshardKeyColumn) COSHARD WITH (coshardTable) creates a table and sets one of its integer-valued columns as a shard key, coshardKeyColumn. You can use this shard key column in joins with another sharded table, coshardTable, which must have a system-assigned shard key (SASK) defined using the SHARD syntax. You can optionally define coshardKeyColumn as a foreign reference to the SASK column of coshardTable as well. The foreign key reference can be used to enforce referential integrity, while the shard key provides faster performance for joins with the other table by matching on its SASK column.
This statement creates a table that defines the CustomerID column as the shard key. This key is used in cosharded joins with the shard key of an existing sharded table, Customer.
```
CREATE TABLE Order (
    Date TIMESTAMP NOT NULL,
    Amount DECIMAL(10,2) NOT NULL, 
    CustomerID CUSTOMER NOT NULL)
SHARD KEY (CustomerID) COSHARD WITH Customer
```

Temporary Tables

CREATE GLOBAL TEMPORARY TABLE table ... creates the table as a global temporary table, where the table definition is available to all processes but the table data (including Stream data) and indices persist only for the duration of the process that created the table. This data is stored in process-private globals and is deleted when the process terminates.
This statement creates a temporary table:
```
CREATE GLOBAL TEMPORARY TABLE TempEmp (
    EmpNum INT NOT NULL,
    NameLast CHAR(30) NOT NULL,
    NameFirst CHAR(30) NOT NULL,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum))
```
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.
You can only define global temporary tables through DDL statements.
Like standard InterSystems IRIS tables, the ClassType=persistent, and the class includes the Final keyword, indicating that it cannot have subclasses.

Table Storage

CREATE TABLE table ... WITH STORAGETYPE = [ROW | COLUMNAR] specifies the layout used to store the underlying data in the table.
- Specify ROW to store data in rows. Row storage enables efficient transactions, such as when frequently updating or inserting rows in online transaction processing (OLTP) workflows. If you omit the WITH STORAGETYPE clause, the created table defaults to row storage.
- Specify COLUMNAR to store data in columns. Columnar storage enables efficient queries, such as when filtering or aggregating data in specific columns in online analytical processing (OLAP) workflows. Columnar storage is an experimental feature for 2022.2. In previous InterSystems IRIS versions, all table data is stored in rows.
  
  Note:
  
  For performance reasons, unless the collation type is specified explicitly, columnar storage layouts default to using EXACT collation. Row storage layouts use namespace-default SQLUPPER collation.
For more details on choosing a storage layout, see Choose an SQL Table Storage Layout.
This statement creates a table with a columnar storage layout.
```
CREATE TABLE Sample.TransactionHistory (
  AccountNumber INTEGER,
  TransactionDate DATE,
  Description VARCHAR(100),
  Amount NUMERIC(10,2),
  Type VARCHAR(10))
WITH STORAGETYPE = COLUMNAR
```
This statement creates a table with columnar storage by using the CREATE TABLE AS SELECT clause.
```
CREATE TABLE Sample.TransactionHistory AS
  SELECT AccountNumber, TransactionDate, Description, Amount, Type
  FROM Sample.BankTransaction
WITH STORAGETYPE = COLUMNAR
```
Tip:

You can use CREATE TABLE AS SELECT to experiment with creating tables that have different storage types and comparing their performance.
CREATE TABLE table (column type ... WITH STORAGETYPE = [ROW | COLUMNAR], ...) specifies individual columns as having row or columnar storage. All other columns default to row storage or to the storage type specified in the WITH STORAGETYPE clause at the end of the table definition.
This statement creates a table with a columnar storage layout only for the Amount column. Because this statement omits the WITH STORAGETYPE clause at the end of the table definition, the rest of the columns default to a row storage layout.
```
CREATE TABLE Sample.BankTransaction (
  AccountNumber INTEGER,
  TransactionDate DATE,
  Description VARCHAR(100),
  Amount NUMERIC(10,2) WITH STORAGETYPE = COLUMNAR,
  Type VARCHAR(10))
```

Class Parameters

CREATE TABLE table ... WITH %CLASSPARAMETER pName = pValue, %CLASSPARAMETER pName2 = pValue2, ... specifies one or more %CLASSPARAMETER name-value pairs that define core aspects of the table being created. Each parameter name, pName, is set to the specified value, pValue.
In this statement, the USEEXTENTSET class parameter disables the use of generated Global names, such as ^EPgS.D8T6.1. These globals are used as IDKEY indexes into the data. The DEFAULTGLOBAL class parameter specifies ^GL.EMPLOYEE as an explicit Global name for indices.
```
CREATE TABLE Employees (
    EmpNum INT NOT NULL,
    NameLast CHAR(30) NOT NULL,
    NameFirst CHAR(30) NOT NULL,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum)
    )
WITH %CLASSPARAMETER USEEXTENTSET = 0,
     %CLASSPARAMETER DEFAULTGLOBAL = '^GL.EMPLOYEE'
```
You can use DEFAULTGLOBAL to specify an extended global reference, either the full reference (%CLASSPARAMETER DEFAULTGLOBAL = '^|"USER"|GL.EMPLOYEE'), or just the namespace portion (%CLASSPARAMETER DEFAULTGLOBAL = '^|"USER"|').

Arguments

table

In a CREATE TABLE command, specify the name of the table you want to create as a valid identifier. 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 system-wide 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 Name and Schema Name Conventions

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. 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.

InterSystems IRIS uses the schema name to generate a corresponding class package name. To generate a package name, first it 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. A schema name is not case-sensitive but the corresponding class package name is. 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.

You can use the same name for a schema and a table, but you cannot use the same name for a table and a view in the same schema. For more details on how package and class names are generated from schema and table names, see Table Names and Schema Names.

Table Name Character Restrictions

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.

Note:

The Japanese locale does not support accented letter characters in identifiers. Japanese identifiers can 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.

Check for Existing Tables

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 an Allow DDL CREATE TABLE or CREATE VIEW for existing table or view setting. The default is 0, which is the recommended setting. If this option is set to 1, InterSystems IRIS deletes the class definition associated with the table and then recreates it. This is similar to performing a DROP TABLE to delete the existing table and then performing 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. For more information on configuring these settings, consult the SQL Configuration Parameters page.

The behavior of the predicate IF NOT EXISTS takes priority over the settings described above. These settings effectively overwrite the table and return SQLCODE 0. When IF NOT EXISTS is specified, the command does nothing and returns SQLCODE 1 along with a message.

column

In a CREATE TABLE command, specify the column name, or a comma-separated list of column names, used to define the columns of the table you are creating. You can specify the column names in any order, with a space separating the column name from its associated data type. For example: CREATE TABLE myTable (column1 INT, column2 VARCHAR(10)). By convention, each column definition is usually presented on a separate line and indentation is used. This is recommended for readability but is not required. Column names are also used to define unique, primary key, and foreign key constraints.

Enclose column name lists in parentheses.

Rather than defining a column, a column definition can reference an existing embedded serial object that defines multiple columns (properties). The column 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, but you can specify a %DESCRIPTION. You cannot create an embedded serial object using CREATE TABLE.

Column Name Conventions

Column names follow identifier conventions, with the same naming restrictions as table names. Avoid beginning column names beginning with a % character, though column names beginning with %z or %Z are permitted. A column name should not exceed 128 characters. By default, column names are simple identifiers. They are not case-sensitive. Attempting to create a column name that differs only in letter case from another column in the same table generates an SQLCODE -306 error.

InterSystems IRIS uses the column 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 column name, and then generates a unique identifier of 96 or fewer characters. InterSystems IRIS substitutes an integer, beginning with 0, for the final character of a column name when this is needed to create a unique property name.

This example shows how InterSystems IRIS handles column names that differ only in punctuation. The corresponding class properties for these columns 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 column name, as specified in CREATE TABLE, is shown in the class property as the SqlFieldName keyword value.

During a dynamic SELECT operation, InterSystems IRIS might generate property name aliases to facilitate common letter case variants. For example, given the column 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.

type

The data type class of the column name specified by column. A specified data type limits a column’s allowed data values to the values appropriate for that data type. InterSystems SQL supports most standard SQL data types.

You can specify either an InterSystems SQL data type (for example, VARCHAR(24) or CHARACTER VARYING(24)) or the class that the data type maps to (for example, %Library.String(MAXLEN=24) or %String(MAXLEN=24)).

Specify data type classes when you want to define additional data definition parameters, such as an enumerated list of permitted data values, pattern matching of permitted data values, maximum and minimum numeric values, and automatic truncation of data values that exceed the maximum length (MAXLEN).

Note:

A data type class parameter default may differ from the InterSystems SQL data type default. For example, VARCHAR() and CHARACTER VARYING() default to MAXLEN=1; The corresponding data type class %Library.String defaults 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.

To view and modify the current data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, System DDL Mappings. To create additional data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, User 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 did not 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)

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)

A numeric value that permits fractional numbers is represented as a pair of integers, (p,s). The first integer, p, is the data type precision. This number is not identical to numerical precision, that is, the number of digits in the number, because the underlying InterSystems IRIS data type classes do not have a precision. Instead, these classes use this number to calculate the MAXVAL and MINVAL values. 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 */

For more details on how precision and scale work, see Data Types.

query

A SELECT query that supplies the column definitions and column data for a table being created using the CREATE TABLE AS SELECT syntax. This query can specify a table, a view, or multiple joined tables. However, it cannot contain any ? parameters like regular SELECT queries.

The data definition of the CREATE TABLE AS SELECT query is as follows:

CREATE TABLE AS SELECT copies column definitions from the query table. To rename copied columns specify a column alias in the query.
CREATE TABLE AS SELECT can copy column definitions from multiple tables if the query specifies joined tables.
CREATE TABLE AS SELECT always defines the RowID as hidden.
- If the source table has a hidden RowID, CREATE TABLE AS SELECT does not copy the source table RowID, but creates a new RowID column for the created table. Copied rows are assigned new sequential RowID values.
- If the source table has a public (non-hidden) RowID, or if the query explicitly selects a hidden RowID, CREATE TABLE AS SELECT creates a new RowID column for the table. The source table RowID is copied into the new table as an ordinary BigInt column that is not hidden, not unique, and not required. If the source table RowID is named “ID”, the new table’s RowID is named “ID1”.
If the source table has an IDENTITY column, CREATE TABLE AS SELECT copies it and its current data as an ordinary BIGINT column for non-zero positive integers that is neither unique nor required.
CREATE TABLE AS SELECT defines an IDKEY index. It does not copy indices associated with copied column definitions.
CREATE TABLE AS SELECT does not copy any column constraints: it does not copy NULL/NOT NULL, UNIQUE, Primary Key, or Foreign Key constraints associated with a copied column definition.
CREATE TABLE AS SELECT does not copy a Default restriction or value associated with a copied column definition.
CREATE TABLE AS SELECT does not copy a COMPUTECODE data constraint associated with a copied column definition.
CREATE TABLE AS SELECT does not copy a %DESCRIPTION string associated with copied table or column definition.

defaultSpec

The default value of a column, specified in the DEFAULT clause 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 column defined Ordernum INT UNIQUE DEFAULT 'No Number' can take the default once, ignoring the INT data type restriction. However, this column cannot take the default a second time, as this would violate the UNIQUE column data constraint.

If no DEFAULT is specified, the implied default is NULL. If a column has a NOT NULL data constraint, you must specify a value for that column, either explicitly or by DEFAULT. Do not use the SQL zero-length string (empty string) as a NOT NULL default value. For more details on null values and the empty string, see NULL.

The DEFAULT data constraint accepts these keyword options: 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 column value to the ObjectScript $USERNAME special variable.

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 a timestamp function with or without a precision value when used as a DEFAULT value. If no precision is specified, InterSystems SQL uses 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 and compiled, not at the time of statement execution.

CURRENT_TIMESTAMP can be specified as the default for a column of data type %Library.PosixTimeOpens in a new tab or %Library.TimeStampOpens in a new tab; the current date and time is stored in the format specified by the column’s data type. CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, and SYSDATE can be specified as a default for a %Library.TimeStampOpens in a new tab column (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_TIMESTAMP DATE DEFAULT CURRENT_TIMESTAMP(2),
    WORK_START TIMESTAMP 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.

uniqueName

The name of the constraint listed in the CONSTRAINT UNIQUE clause, specified as a valid identifier. If specified as a delimited identifier, a constraint name can include the ".", "^", ",", and "->" characters. The constraint name uniquely identifies the constraint and is also used to derive the corresponding index name. This constraint name is required when using the ALTER TABLE command to drop a constraint from the table definition. Note that ALTER TABLE cannot drop a column that is listed in CONSTRAINT UNIQUE. Attempting to do so generates an SQLCODE -322 error.

The CONSTRAINT UNIQUE clause has this syntax:

CONSTRAINT uniqueName UNIQUE (column1,column2)

This constraint specifies that the combination of values of columns column1 and column2 must always be unique, even though either of these columns by itself may take non-unique values. You can specify one or more columns for this constraint.

All of the columns specified in this constraint must be defined in the column definition. Specifying a column that does not also appear in the column definitions generates an SQLCODE -86 error. The specified columns should be defined as NOT NULL. None of the specified columns should be defined as UNIQUE, as this would make specifying this constraint meaningless.

Columns can be specified in any order. The column order dictates the column order for the corresponding index definition. Duplicate column names are permitted. Although you may specify a single column name in the UNIQUE columns constraint, this would be functionally identical to specify the UNIQUE data constraint to that column. A single-column constraint does provide a constraint name for future use.

You can specify multiple unique column constraint statements in a table definition. Constraint statements can be specified anywhere in the column definition; by convention they are commonly placed at the end of the list of defined columns.

Refer to the Constraints option of Catalog Details for ways to list the columns of a table that are defined with a unique constraint.

pKeyName

The name of the primary key defined in the PRIMARY KEY constraint clause, 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.

For more details on defining the primary key of a table, see Defining a Primary Key.

fKeyName

The name of a foreign key defined in the FOREIGN KEY constraint clause, 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.

For more details on defining a foreign key in a table, see Defining a Foreign Key.

refTable

The name of the table to reference in the FOREIGN KEY clause, specified as a valid identifier. A table name can be qualified (schema.table), or unqualified (table).

refColumn

A column name or a comma-separated list of existing column names defined in the reference table that is specified in the foreign key constraint. Enclose the referenced columns in parentheses. If you omit refColumn, then CREATE TABLE assigns a default reference column, as described in Defining a Foreign Key.

To specify an explicit RowID as the reference column, specify refColumn as %ID. For example: FOREIGN KEY (CustomerNum) REFERENCES Customers (%ID). This value is synonymous with an omitted column name, provided that the reference table has no primary key or foreign key specified. If the class definition for the table contains SqlRowIdName, you can specify this value as the explicit RowID.

refAction

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. In CREATE TABLE, the ON DELETE refAction and ON UPDATE refAction clauses specify what action to take when a foreign key column specified by refColumn is changed.

The ON DELETE clause defines the DELETE rule for the reference table. When an attempt to delete a row from the reference table is made, the ON DELETE clause defines what action to take for the rows in the reference table.
The ON UPDATE clause defines the UPDATE rule for the reference table. When an attempt to change (update) the primary key value of a row from the reference table is made, the ON UPDATE clause defines what action to take for the rows in the reference table.

InterSystems SQL supports these foreign key referential actions:

Referential Action	Description
NO ACTION (default)	If any row in the foreign key column references the row being deleted or updated, the delete or update fails. This constraint does not apply if the foreign key references itself.
SET DEFAULT	Set the foreign key columns that reference the row being deleted or updated to their default values. If the foreign key column does not have a default value, it is set to NULL. A row must exist in the referenced table that contains an entry for the default value.
SET NULL	Set the foreign key columns that reference the row being deleted or updated to NULL. The foreign key columns must allow NULL values.
CASCADE	ON DELETE — Also delete the rows of the foreign key columns that reference the row being deleted. ON UPDATE — Also update the rows of foreign key columns that reference the row being updated.

Do not define two foreign keys with different names that reference the same column combination and perform contradictory referential actions. In accordance with the ANSI standard, InterSystems SQL does not issue an error if such cases (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. For more information, see Using Foreign Keys.

code

Lines of code used in the COMPUTECODE data constraint to compute a default value of a column. Specify the code in curly braces. Whitespace and line returns are permitted before or after the curly braces.

The programming language of the code depends on the value you set in the COMPUTECODE clause:

COMPUTECODE or COMPUTECODE OBJECTSCRIPT — Specify code as ObjectScript code. Within the code, you can reference SQL column names with curly brace delimiters, for example, {DOB}. The ObjectScript code can contain Embedded SQL. In the projected class, COMPUTECODE specifies the SqlComputeCode column name and the computation for its value.
COMPUTECODE PYTHON — Specify code as Python code. Within the code, you can reference SQL column names by using the cols.getfield method, for example, cols.getfield('DOB'). In the projected class, COMPUTECODE specifies the PropertyComputation class method, which stores the code that computes the column values. Property is the name of the column being computed. The projected class uses this class method in place of a SqlComputeCode property keyword.

When you specify a computed field name, either in COMPUTECODE or in the SqlComputeCode property keyword, you must specify the SQL field name, not the corresponding generated table property name.

A default data value supplied by COMPUTECODE 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 column. It uses ObjectScript code to compute its default value from the DOB column value:

CREATE TABLE MyStudents (
    Name VARCHAR(16) NOT NULL,
    DOB TIMESTAMP,
    Birthday VARCHAR(12) 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 variables are translated into specific values at class compilation time. The variables are not case-sensitive.

In ObjectScript compute code, call pseudo-field reference variables by enclosing them in curly braces. For example: {%%CLASSNAME}
In Python compute code, call pseudo-field reference variables by using the cols.getfield method. For example: cols.getfield(%%CLASSNAME)

The COMPUTECODE value is a default. It is returned only if you did not supply a value to the column. 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.

If an error in the ObjectScript COMPUTECODE code occurs, 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 -350 error.

A COMPUTECODE stored value can be indexed. The application developer is responsible for making sure that computed column 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 column.

updateSpec

When you create a table and specify a column using the ON UPDATE clause, that column is computed every time a row is updated in the table. The most common use of this feature is to define a column in a table that contains a timestamp value for the last time the row was updated.

Available updateSpec 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 column 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 column. If an UPDATE specifies an explicit value for the RowTS column, 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 column to a boolean value:

CREATE TABLE mytest (
    Name VARCHAR(48),
    HasBeenUpdated TINYINT DEFAULT 0 ON UPDATE 1 )

The following example sets the WhoLastUpdated column 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 column also has a COMPUTECODE data constraint. Attempting to do so results in an SQLCODE -1 error at compile or prepare time.

description

InterSystems SQL provides a %DESCRIPTION keyword, which you can use to provide a description for documenting a table or a column. %DESCRIPTION is followed by text string, description, 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 column 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 column, the system retains only the last %DESCRIPTION specified. You cannot use ALTER TABLE to alter existing descriptions.

In the corresponding persistent class definition, a description appears prefaced by three slashes on the line immediately before the corresponding class (table) or property (column) syntax. For example: /// Joe's Table. In the Class Reference for the corresponding persistent class, the table description appears at the top just after the class name and SQL table name; a column description appears just after the corresponding property syntax.

You can display %DESCRIPTION text using the DESCRIPTION property of INFORMATION.SCHEMA.TABLESOpens in a new tab or INFORMATION.SCHEMA.COLUMNSOpens in a new tab. For example:

SELECT COLUMN_NAME,DESCRIPTION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'

sqlCollation

The type of collation used to sort values of a column, specified as one of the following SQL collation types: %EXACT, %MINUS, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE, or %MVR. Collation keywords are 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 also optional.

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. For more information on collation, see Table Field/Property Definition Collation.

%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 columns 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.

ObjectScript provides the Collation()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab 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, and 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.

shardKeyColumn

The column, or comma-separated list of columns, used as the shard key. Specify shardKeyColumn in the SHARD KEY clause, immediately after the closing parenthesis of the table column list but before the WITH clause (if specified). Specifying the shard key definition as an element within the table column list is supported for backwards compatibility, but defining a shard key in both locations generates an SQLCODE -327 error.

You cannot define the RowID column as the shard key. However, if the created table includes an IDENTITY column or IDKEY, you can define either of those columns as the shard key.

For information on choosing a shard key, see Choose a Shard Key.

coshardKeyColumn

The name of the shard key column that is used in cosharded joins with the shard key of the table defined in coshardTable. Specify coshardKeyColumn in the COSHARD WITH syntax: SHARD KEY (coshardKeyColumn) COSHARD WITH coshardTable.

coshardTable

The name of an existing table that the table being created coshards with. The table specified in the COSHARD WITH clause must be a sharded table with a system-assigned shard key.

When you specify this table, InterSystems IRIS sets the CoshardWith index keyword in the ShardKey index for the sharded table. This CoshardWith index keyword is equal to the class that projects the table.

To determine which sharded tables specified in a query are cosharded, view the Cosharding comment option.

pName = pValue

A %CLASSPARAMETER name-value pair that sets the class parameter named pName to the value pValue. You can specify multiple %CLASSPARAMETER clauses using comma-separated name-value pairs. For example: WITH %CLASSPARAMETER DEFAULTGLOBAL = '^GL.EMPLOYEE', %CLASSPARAMETER MANAGEDEXTENT 0. Separate the name and value using an equal sign or at least one space. Class parameter values are literal strings and numbers and must be defined as constant values.

You can use the USEEXTENTSETOpens in a new tab and DEFAULTGLOBALOpens in a new tab class parameters to define the global naming strategy for table data storage and index data storage.

The IDENTIFIEDBYOpens in a new tab class parameter is deprecated. You must convert IDENTIFIEDBY relationships to proper Parent/Child relationships to be supported in InterSystems IRIS.

A CREATE TABLE that defines a sharded table cannot define the DEFAULTGLOBALOpens in a new tab, DSINTERVALOpens in a new tab, DSTIMEOpens in a new tab, or VERSIONPROPERTYOpens in a new tab class parameter.

You can specify additional class parameters as needed. For more details, see Class Parameters.

Examples

Create and Populate Table

Use CREATE TABLE to create a table, Employee, with several columns:

The EmpNum column (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 and automatically increments each time a row is inserted into the table.
The employee's last and first names are stored in character string columns that have a maximum length of 30 and cannot be NULL.
The remaining columns are for the employee's start date, accrued vacation time, and accrued sick time, which use the TIMESTAMP and INT data types.

CREATE TABLE Employee (
    EmpNum INT NOT NULL AUTO_INCREMENT,
    NameLast CHAR(30) NOT NULL,
    NameFirst CHAR(30) NOT NULL,
    StartDate TIMESTAMP,
    AccruedVacation INT,
    AccruedSickLeave INT,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EmpNum))

To modify the table schema, use ALTER TABLE. For example, this statement changes the name of the table from Employee to Employees.

ALTER TABLE Employee RENAME Employees

To insert rows into a table, use INSERT. For example, this statement inserts a row with only the required columns in the table. The EmpNum column is also required, but you do not need to specify it because it auto-increments.

INSERT INTO Employees (NameLast, NameFirst) VALUES ('Zubik','Jules')

To update inserted rows, use UPDATE. For example, in the inserted row, this statement sets a value in one of the columns that was missing data.

UPDATE Employees SET AccruedVacation = 15 WHERE Employees.EmpNum = 1

To delete a row, use DELETE. For example, this statement deletes the inserted row.

DELETE FROM Employess WHERE EmpNum = 1

To delete an entire table, use DROP TABLE. Be careful using DROP TABLE. Unless you specify the %NODELDATA keyword, this command deletes both the table and all associated data.

DROP TABLE Employess

Security and Privileges

The CREATE TABLE command is a privileged operation that requires %CREATE_TABLE administrative privileges. Executing a CREATE TABLE command without these privileges results in an SQLCODE -99 error. To assign %CREATE_TABLE privileges to a user or role, use the GRANT command, assuming that you hold appropriate granting privileges. If you are using the CREATE TABLE AS SELECT syntax, then you must have SELECT privilege on the table specified in the query. Administrative privileges are namespace-specific. For more details, see Privileges.

By default, CREATE TABLE security privileges are enforced. To configure this privilege requirement system-wide, use the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. For example: SET status=$SYSTEM.SQL.Util.SetOption("SQLSecurity",0,.oldval). To determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method, which displays an SQL security enabled setting. The default is 1 (enabled). When SQL security is enabled (recommended), a user can perform actions only on table or views for which they have privileges. Set this method to 0 to disable SQL security for any new process started after changing this setting. This means that 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()Opens in a new tab 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 example:

  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  NEW SQLCODE,%msg
  &sql(CREATE TABLE MyTable (col1 INT, col2 INT))
  IF SQLCODE=0 {WRITE !,"Table created"}
  ELSE {WRITE !,"SQLCODE=",SQLCODE,": ",%msg }

For more information, see %SYSTEM.SecurityOpens in a new tab.

If CREATE TABLE is used with computed columns that require executing code, the user will need %Development:USE privileges in addition to %CREATE_TABLE privileges unless the command is used in Embedded SQL.

Users can also avoid privilege checks by creating a command with the %SQL.StatementOpens in a new tab class and using either the %Prepare() method with the checkPriv argument set to 0 or the %ExecDirectNoPriv() method.

More About

Class Definitions of Created Tables

When you create an SQL table using CREATE TABLE, InterSystems IRIS® automatically creates a persistent class corresponding to this table definition, with properties corresponding to the column 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. (The default is 1; you can change this default system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method SET status=$SYSTEM.SQL.Util.SetOption("DDLFinal",0,.oldval); to determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method).

Defining a Primary Key

Defining a primary key is optional. When you define a table, InterSystems IRIS automatically creates a generated column, the RowID Column (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 column. 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 column, a Social Security Number, a Patient Record ID column, or an inventory stock number. You can explicitly define a column or group of columns as the primary record identifier by using the PRIMARY KEY clause.

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 column.) The collation type of a primary key is specified in the definition of the column itself.

Refer to the Constraints option of Catalog Details for ways to list the columns of a table that are defined as the primary key.

For more details, see Primary Key.

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, and so on. 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 column, the column 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:

The SQL SET OPTION PKEY_IS_IDKEY statement.
The system-wide $SYSTEM.SQL.Util.SetOption()Opens in a new tab method configuration option DDLPKeyNotIDKey. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays Are primary keys created through DDL not ID keys; the default is 1.
Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. View the current setting of Define primary key as ID key for tables created via DDL.
- If the check box is not selected (the default), the Primary Key does not become the IDKEY index in the class definition. Access to records using a primary key that is not the IDKEY is significantly less efficient; however, this type of primary key value can be modified.
- If the check box is selected, when a Primary Key constraint is specified through DDL, it automatically becomes the IDKEY index in the class definition. With this option selected, data access is more efficient, but a primary key value, once set, can never be modified.

However, if an IDENTITY column 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 define the primary key as the IDKEY.

InterSystems IRIS supports properties (columns) that are part of the IDKEY index to be SqlComputed. For example, a parent reference column. The property must be a triggered computed column. 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 columns 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 column as the primary key for ODBC/JDBC projection, according to the following rules:

If there is an IDKEY index on a single column, report the IDKEY column as the SQLPrimaryKey column.
Else if the class is defined with SqlRowIdPrivate=0 (the default), report the RowID column as the SQLPrimaryKey column.
Else if there is an IDKEY index, report the IDKEY columns as the SQLPrimaryKey columns.
Else do not report an SQLPrimaryKey.

Multiple Primary Keys

You can only define one primary key. 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. The SQLCODE -307 error is issued even if the second definition of the primary key is identical to the first definition. To determine the current configuration, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays an Allow create primary key through DDL when key exists setting. The default is 0 (No), which is the recommended configuration setting. If this option is set to 1 (Yes), InterSystems IRIS drops the existing primary key constraint and establishes the last-specified primary key as the table's primary key.

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.

Defining a Foreign Key

A foreign key is a column that references another table; the value stored in the foreign key column 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 column 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 column 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 column cannot contain duplicate values or NULL.

In a foreign key definition, you can specify:

One column name: FOREIGN KEY (CustomerNum) REFERENCES Customers (CustID). The foreign key column (CustomerNum) and referenced column (CustID) may have different names (or the same name), but must have the same data type and column constraints.
A comma-separated list of column names: FOREIGN KEY (CustomerNum,SalespersonNum) REFERENCES Customers (CustID,SalespID). The foreign key columns and referenced columns must correspond in number of columns and in order listed.
An omitted column name: FOREIGN KEY (CustomerNum) REFERENCES Customers.
An explicit RowID column: FOREIGN KEY (CustomerNum) REFERENCES Customers (%ID). Synonymous with an omitted column name. If the class definition for the table contains SqlRowIdName you can specify this value as the explicit RowID.

If you define a foreign key and omit the referenced column name, the foreign key defaults as follows:

The primary key column defined for the specified table.
If the specified table does not have a defined primary key, the foreign key defaults to the IDENTITY column defined for the specified table.
If the specified table does not have either a defined primary key or 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 define the RowID as public, InterSystems IRIS issues an SQLCODE -315 error. You must omit the referenced column name when defining a foreign key on the RowID; attempting to explicitly specify ID as the referenced column 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 columns of a table that are defined as foreign key columns and the generated Constraint Name for a foreign key.

In a class definition, you can specify a Foreign Key that contains a column 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 column defined in a foreign key of a child has to be part of the IDKEY index of the parent class, the only referential action supported for foreign keys of this type is NO ACTION.

If a foreign key references a nonexistent table, InterSystems IRIS issues an SQLCODE -310 error, with additional information provided in %msg.
If a foreign key references a nonexistent column, InterSystems IRIS issues an SQLCODE -316 error, with additional information provided in %msg.
If a foreign key references a nonunique column, InterSystems IRIS issues an SQLCODE -314 error, with additional information provided in %msg.

If the foreign key column references a single column, the two columns must have the same data type and column 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 a database driver.

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 can be the shard key or another key. A foreign key can be a single column or multiple columns.

NO ACTION is the only referential action supported for sharded tables.

For more details, see Querying the Sharded Cluster.

Implicit Foreign Key

It is preferable to explicitly define all foreign keys. If there is an explicit foreign key defined, InterSystems IRIS reports this constraint and the implicit foreign key constraint is not defined.

However, it is possible to project implicit foreign keys to ODBC/JDBC and the Management Portal. These implicit foreign keys are reported as UPDATE and DELETE referential actions of NO ACTION. This implicit reference foreign key is not a true foreign key as there are no referential actions enforced. The name of this foreign key reported for the reference is "IMPLICIT_FKEY_REFERENCE__"_columnname. The reporting of this reference as a foreign key is provided for interoperability with third-party tools.

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:

The table is defined as a global temporary table (CREATE TABLE GLOBAL TEMPORARY TABLE ...).
The table defines an explicit IDKEY index.
The table contains a defined IDENTITY column that does not have MINVAL=1.
The $SYSTEM.SQL.Util.SetOption()Opens in a new tab method DDLDefineBitmapExtent option is set to 0 to override the default system-wide. To determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method, which displays a Do classes created by a DDL CREATE TABLE statement define a bitmap extent index setting.

If, after creating a bitmap index, the CREATE BITMAPEXTENT INDEX command 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 more details, see Bitmap Extent Index.

Creating Named RowId Column Using IDENTITY Keyword

InterSystems SQL automatically creates a RowID column 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 column with the same properties as a RowID record id column. An IDENTITY column behaves as a single-column IDKEY index, whose value is a unique system-generated integer.

Defining an IDENTITY column prevents the defining of the Primary Key as the IDKEY.

Just as with any system-generated ID column, an IDENTITY column has the following characteristics:

You can only define one column per table as an IDENTITY column. Attempting to define more than one IDENTITY column for a table generates an SQLCODE -308 error.
The data type of an IDENTITY column must be an integer data type. If you do not specify a data type, its data type is automatically defined as BIGINT. You can specify any integer data type, such as INTEGER or SMALLINT; BIGINT is recommended to match the data type of RowID. Any specified column constraints, such as NOT NULL or UNIQUE are accepted but ignored.
Data values are system-generated. They consist of unique, nonzero, positive integers.
By default, IDENTITY column data values cannot be user-specified. By default, an INSERT statement does not, and cannot, specify an IDENTITY column value. Attempting to do so generates an SQLCODE -111 error. To determine whether an IDENTITY column value can be specified, call the $SYSTEM.SQL.Util.GetOption("IdentityInsert")Opens in a new tab method; the default is 0. To change this setting for the current process, call the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("IdentityInsert",1,.oldval). You can also specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1 in the table definition. Specifying ALLOWIDENTITYINSERT=1 overrides any setting applied using SetOption("IdentityInsert"). For further details, refer to the INSERT statement.
IDENTITY column data values cannot be modified in an UPDATE statement. Attempting to do so generates an SQLCODE -107 error.
The system automatically projects a primary key on the IDENTITY column to ODBC and JDBC. If a CREATE TABLE or ALTER TABLE statement defines a primary key constraint or a unique constraint on an IDENTITY column, or on a set of columns including an IDENTITY column, the constraint definition is ignored and no corresponding primary key or unique index definition is created.
A SELECT * statement does return a table's IDENTITY column.

Following an INSERT, UPDATE, or DELETE operation, you can use the LAST_IDENTITY function to return the value of the IDENTITY column for the most-recently modified record. If no IDENTITY column is defined, LAST_IDENTITY returns the RowID value of the most recently modified record.

These SQL statements create a table with an IDENTITY column and insert a rows into that table, generating an IDENTITY column value for the created table:

CREATE TABLE Employee (
    EmpNum INT NOT NULL,
    MyID IDENTITY NOT NULL,
    Name VARCHAR(30) NOT NULL,
    CONSTRAINT EmployeePK PRIMARY KEY (EmpNum))

INSERT INTO Employee (EmpNum,Name) 
SELECT ID,Name FROM SQLUser.Person WHERE Age >= '25'

In this case, the primary key, EmpNum, is taken from the ID column of another table. EmpNum values are unique integers, but because of the WHERE clause, this column might contain gaps in the sequence. The IDENTITY column, MyID, assigns a user-visible unique sequential integer to each record.

Sharded Table Restrictions

When defining a sharded table, keep these restrictions in mind:

A sharded table can only be used in a sharded environment; a non-sharded table can be used in a sharded or non-sharded environment. Not all tables are good candidates for sharding. Optimal performance in a sharded environment is generally achieved by using a combination of sharded tables (generally very large tables) and non-sharded tables. For more details, see Evaluating the Benefits of Sharding and Evaluate Existing Tables for Sharding.
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.
A UNIQUE column 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. For more details, see Evaluate Unique Constraints in “Horizontally Scaling InterSystems IRIS for Data Volume with Sharding”.
Sharding a table that is involved in complex transactions requiring atomicity is not recommended.
A sharded table cannot contain a ROWVERSION data type or SERIAL (%Library.Counter) data type column.
A sharded table cannot specify the VERSIONPROPERTYOpens in a new tab class parameter.
To specify a shard key, the current namespace must be configured for sharding. If the current namespace is not configured for sharding, a CREATE TABLE that specifies a shard key fails with an SQLCODE -400 error. For details on configuring namespaces for sharding, see Configure the Shard Master Data Server.
The only referential action supported for sharded tables is NO ACTION. Any other referential action results in an SQLCODE -400 error.
A shard key column can only take %EXACT, %SQLSTRING, or %SQLUPPER collation, with no truncation. For more details, see Querying the Sharded Cluster.

For more details on sharding, see Create Target Sharded Tables.

Legacy Options

%EXTENTSIZE and %NUMROWS Keywords

The %EXTENTSIZE and %NUMROWS keywords provide an option to store the anticipated number of rows in the table being created. The InterSystems SQL query optimizer uses this value to estimate the cost of query plans. A table can define one or the other of these values but not both. For example:

CREATE TABLE Sample.DaysInAYear (
    %EXTENTSIZE 366, 
    MonthName VARCHAR(24),
    Day INTEGER)

Starting in 2021.2, the first time you query a table, InterSystems IRIS collects statistics such as the table size automatically. The SQL query optimizer uses these generated statistics to suggest appropriate query plan, making the %EXTENTSIZE and %NUMROWS keywords unnecessary. For more details on optimizing tables with table statisticss, see Table Statistics for Query Optimizer.

%FILE Keyword

The %FILE keyword provides an option to specify a file name that documents the table. For example:

CREATE TABLE Employee (
    %FILE 'C:\SQL\employee_table_desc.txt',
    EmpNum INT PRIMARY KEY,
    NameLast VARCHAR(30) NOT NULL,
    NameFirst VARCHAR(30) NOT NULL,
    StartDate TIMESTAMP %Description 'MM/DD/YY')

This keyword is not recommended. Instead, document the table by using the %DESCRIPTION keyword.

Shard Key and %CLASSPARAMETER in Column List Parentheses

Old CREATE TABLE code might include the Shard Key definition and %CLASSPARAMETER clauses as comma-separated elements within the table element parentheses. For example: CREATE TABLE myTable(Name VARCHAR(50), DOB DATE, %CLASSPARAMETER USEEXTENTSET = 1). The preferred syntax is to specify these clauses after the closing parenthesis. For example: CREATE TABLE myTable(Name VARCHAR(50), DOB TIMESTAMP) WITH %CLASSPARAMETER USEEXTENTSET = 1. Specifying duplicates of these clauses generates an SQLCODE -327 error.

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 COLLATE keyword is still used*/ NOT FOR REPLICATION

CREATE TABLE (SQL)

Synopsis

Description

Basic Table Creation

Column Constraints

NOT NULL Constraint

Default Constraint

Unique Constraints

Primary Key Constraints

Foreign Key Constraints

Special Columns and Column Properties

Computed Columns

Counter Columns

%DESCRIPTION Keyword

%PUBLICROWID Keyword

Collation Property

Table Options

Sharded Tables

Temporary Tables

Table Storage

Class Parameters

Arguments

table

Table Name and Schema Name Conventions

Table Name Character Restrictions

Check for Existing Tables

column

Column Name Conventions

type

Data Size

query

defaultSpec

uniqueName

pKeyName

fKeyName

refTable

refColumn

refAction

code

updateSpec

description

sqlCollation

shardKeyColumn

coshardKeyColumn

coshardTable

pName = pValue

Examples

Create and Populate Table

Security and Privileges

More About

Class Definitions of Created Tables

Defining a Primary Key

Primary Key As IDKEY

No Primary Key

Multiple Primary Keys

Defining a Foreign Key

Sharded Tables and Foreign Keys

Implicit Foreign Key

Bitmap Extent Index

Creating Named RowId Column Using IDENTITY Keyword

Sharded Table Restrictions

Legacy Options

%EXTENTSIZE and %NUMROWS Keywords

%FILE Keyword

Shard Key and %CLASSPARAMETER in Column List Parentheses

Options Supported for Compatibility Only

See Also