Skip to main content

ALTER TABLE (SQL)

Modifies a table.

Synopsis

ADD

ALTER TABLE table ADD [CONSTRAINT identifier] 
  ( FOREIGN KEY (field-commalist) 
    REFERENCES table (field-commalist) 
    [ON DELETE ref-action] 
    [ON UPDATE ref-action] 
    [NOCHECK]
  )
ALTER TABLE table ADD ( UNIQUE (field-commalist) )
ALTER TABLE table ADD ( PRIMARY KEY (field-commalist) )
ALTER TABLE table ADD DEFAULT ( default-spec ) FOR field
ALTER TABLE table ADD COLUMN ( field datatype  [sqlcollation]  
  [%DESCRIPTION string] [DEFAULT [(] default-spec [)] ]   
  [ON UPDATE update-spec ] [UNIQUE] [NOT NULL] 
  [REFERENCES table (field-commalist)
  [ON DELETE ref-action] [ON UPDATE ref-action]  
  [NOCHECK] )
ALTER COLUMN

ALTER TABLE table ALTER COLUMN field alter-column-action
ALTER TABLE table ALTER COLUMN field RENAME newfieldname
ALTER TABLE table ALTER COLUMN field datatype
ALTER TABLE table ALTER COLUMN field SET DEFAULT ( default-spec ) 
ALTER TABLE table ALTER COLUMN field DROP DEFAULT
ALTER TABLE table ALTER COLUMN field NULL
ALTER TABLE table ALTER COLUMN field NOT NULL
ALTER TABLE table ALTER COLUMN field COLLATE sqlcollation
CLEANUP

ALTER TABLE table CLEANUP
CONVERT

ALTER TABLE table CONVERT TO SHARDED KEY (identifier, ...) defer
ALTER TABLE table CONVERT TO SHARDED KEY (identifier, ...) 
  COSHARD WITH ( coshardtable ) defer
ALTER TABLE table CONVERT TO NONSHARDED defer

ALTER TABLE table CONVERT TO STORAGETYPE = ROW delData
ALTER TABLE table CONVERT COLUMNS field-commalist 
  TO STORAGETYPE = ROW delData

ALTER TABLE table CONVERT TO STORAGETYPE = COLUMNAR delData
ALTER TABLE table CONVERT COLUMNS field-commalist 
  TO STORAGETYPE = COLUMNAR delData
DROP or DELETE

ALTER TABLE table DROP COLUMN field 
  [RESTRICT | CASCADE] delData
ALTER TABLE table DROP FOREIGN KEY identifier
ALTER TABLE table DROP PRIMARY KEY    
ALTER TABLE table DROP CONSTRAINT identifier

ALTER TABLE table DELETE COLUMN field 
  [RESTRICT | CASCADE] delData
ALTER TABLE table DELETE FOREIGN KEY identifier
ALTER TABLE table DELETE PRIMARY KEY    
ALTER TABLE table DELETE CONSTRAINT identifier
MODIFY

ALTER TABLE table MODIFY oldfieldname RENAME newfieldname
ALTER TABLE table MODIFY field [datatype] 
  [DEFAULT ( default-spec )] 
  [CONSTRAINT identifier] [ NULL | NOT NULL ]
PARTITION

ALTER TABLE table MOVE PARTITION BETWEEN partID AND partID TO database
ALTER TABLE table MOVE PARTITION ID partID TO database
ALTER TABLE table MOVE PARITION ID BETWEEN partVal AND partVal TO database

ALTER TABLE table DROP PARTITION ID partID
ALTER TABLE table DROP ALL PARTITIONS

ALTER TABLE table TRUNCATE PARTITION ID partID
RENAME

ALTER TABLE table RENAME table
STATS

ALTER TABLE table DROP FIXED STATS
ALTER SCHEMA schema DROP FIXED STATS

ALTER TABLE table FIX STATS
ALTER SCHEMA schema FIX STATS

ALTER TABLE table SET RUNTIME IGNOREFIXEDSTATS = boolean
ALTER TABLE table SET RUNTIME SKIPAUTOMATICSTATSCOLLECTION = boolean
ALTER SCHEMA schema SET RUNTIME IGNOREFIXEDSTATS = boolean
ALTER SCHEMA table SET RUNTIME SKIPAUTOMATICSTATSCOLLECTION = boolean

Arguments

Argument Description
table The name of the table to be altered. The table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. Schema search path values are not used.
identifier A unique name assigned to a constraint. Must be a valid identifier.
field The name of the column to be altered (added, modified, deleted). Must be a valid identifier.
field-commalist The name of a column or a comma-separated list of columns. An field-commalist must be enclosed in parentheses, even when only a single column is specified. See SQL Identifiers.
datatype A valid InterSystems SQL data type. See Data Types.
default-spec A default data value automatically supplied for this field, if not overridden by a user-supplied data value. Allowed values are: a literal value; one of the following keyword options (NULL, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP); or an OBJECTSCRIPT expression. Do not use the SQL zero-length string as a default value. For further details, see CREATE TABLE.
modification-spec See ON UPDATE in CREATE TABLE.
COLLATE sqlcollation Optional — Specify one of the following SQL collation types: %EXACT, %MINUS, %MVR, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE. The default is the namespace default collation (%SQLUPPER, unless changed). %SQLSTRING, %SQLUPPER, and %TRUNCATE may be specified with an optional maximum length truncation argument, an integer enclosed in parentheses. The percent sign (%) prefix to these collation parameter keywords is optional. The COLLATE keyword is optional. For further details refer to Table Field/Property Definition Collation.
convert-option Specify whether to convert a sharded table to a non-sharded table or vice versa.
defer Always optional. Either DEFER or IMMEDIATE.
del-data Always optional. Either %DELDATA or %NODELDATA.
partID The ID of the partition you would like to alter. Can be found by querying INFORMATION_SCHEMA.TABLE_PARTITIONS
partVal The value in the partitioned column that should serve as either the upper or lower bound for the range.
partCol The column that the partition should modify.
partInterval An timescale (n MINUTE/HOUR/DAY/MONTH/YEAR, where n is an integer) or integer that specifies how large the partition interval should be.

Description

An ALTER TABLE statement modifies a table definition; it can add elements, remove elements, or modify existing elements. You can only perform one type of operation in each ALTER TABLE statement.

The ALTER TABLE DROP keyword and the ALTER TABLE DELETE keyword are synonyms.

To determine if a specified table exists in the current namespace, use the $SYSTEM.SQL.Schema.TableExists()Opens in a new tab method.

Privileges and Locking

The ALTER TABLE command is a privileged operation. The user must have %ALTER_TABLE administrative privilege to execute ALTER TABLE. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %ALTER_TABLE privileges.

The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'.

To determine if the current user has %ALTER privilege, invoke the %CHECKPRIV command. To determine if a specified user has %ALTER privilege, invoke the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method.

To assign the required administrative privilege, use the GRANT command with %ALTER_TABLE privilege; this requires the appropriate granting privileges. To assign the %ALTER object privilege, you can use:

  • The GRANT command with the %ALTER privilege. This requires the appropriate granting privileges.

  • The ALTER check box for the table on the SQL Tables tab in the Management Portal on the page for editing a role or user. This requires the appropriate granting 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:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, see %SYSTEM.SecurityOpens in a new tab.

  • ALTER TABLE cannot be used on a table projected from a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.

  • ALTER TABLE cannot be used on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

ALTER TABLE acquires a table-level lock on table. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the ALTER TABLE operation. When ALTER TABLE locks the corresponding class definition, it uses the SQL Lock Timeout setting for the current process.

To alter a table, the table cannot be locked by another process in either EXCLUSIVE MODE or SHARE MODE. Attempting to alter a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire exclusive table lock for table 'Sample.MyTest'.

ADD

ADD COLUMN Restrictions

ADD COLUMN can add a single column, or can add a comma-separated list of columns.

If you attempt to add a field to a table through an ALTER TABLE tablename ADD COLUMN statement:

  • If a column of that name already exists, the statement fails with an SQLCODE -306 error.

  • If the statement specifies a NOT NULL constraint on the column and there is no default value for the column, then the statement fails if data already exists in the table. This is because, after the completion of the DDL statement, the NOT NULL constraint is not satisfied for all the pre-existing rows. This generates the error code SQLCODE -304 (Attempt to add a NOT NULL field with no default value to a table which contains data).

  • If the statement specifies a NOT NULL constraint on the column and there is a default value for the column, the statement updates any existing rows in the table and assigns the default value for the column to the field. This includes default values such as CURRENT_TIMESTAMP.

  • If the statement DOES NOT specify a NOT NULL constraint on the column and there is a default value for the column, then there are no updates of the column in any existing rows. The column value is NULL for those rows.

To change this default NOT NULL constraint behaviors, refer to the COMPILEMODE=NOCHECK option of the SET OPTION command.

If you specify an ordinary data field named “ID” and the RowID field is already named “ID” (the default), the ADD COLUMN operation succeeds. ALTER TABLE adds the ID data column, and renames the RowId column as “ID1” to avoid duplicate names.

Adding an Integer Counter

If you attempt to add an integer counter field to a table through an ALTER TABLE tablename ADD COLUMN statement:

  • You can add an IDENTITY field to a table if the table does not have an IDENTITY field. If the table already has an IDENTITY field, the ALTER TABLE operation fails with an SQLCODE -400 error with a %msg such as the following: ERROR #5281: Class has multiple identity properties: 'Sample.MyTest::MyIdent2'. When you use ADD COLUMN to define this field, InterSystems IRIS populates existing data rows for this field using the corresponding RowID integer values.

    If CREATE TABLE defined a bitmap extent index and later you add an IDENTITY field to the table, and the IDENTITY field is not of type %BigInt, %Integer, %SmallInt, or %TinyInt with a MINVAL of 1 or higher, and there is no data in the table, the system automatically drops the bitmap extent index.

  • You can add one or more SERIAL (%Library.CounterOpens in a new tab) fields to a table. When you use ADD COLUMN to define this field, existing data rows are NULL for this field. You can use UPDATE to supply values to existing data rows that are NULL for this field; you cannot use UPDATE to change non-NULL values.

  • You can add a ROWVERSION field to a table if the table does not have a ROWVERSION field. If the table already has a ROWVERSION field, the ALTER TABLE operation fails with an SQLCODE -400 error with a %msg such as the following: ERROR #5320: Class 'Sample.MyTest' has more than one property of type %Library.RowVersion. Only one is allowed. Properties: MyVer,MyVer2. When you use ADD COLUMN to define this field, existing data rows are NULL for this field; you cannot update ROWVERSION values that are NULL.

ADD PRIMARY KEY When Already Exists

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 a 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), an ALTER TABLE ADD PRIMARY KEY causes InterSystems IRIS to remove the primary key index from the class definition, and then recreates this index using the specified primary key field(s).

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.

However, even if this option is set to allow the creation of a primary key when one already exists, you cannot recreate a primary key index if it is also the IDKEY index and the table contains data. Attempting to do so generates an SQLCODE -307 error.

ADD FOREIGN KEY Restrictions

For information on foreign keys, refer to Defining Foreign Keys and Foreign Key Referential Action Clause in the CREATE TABLE command, and to Using Foreign Keys.

By default, you cannot have two foreign keys with the same name. Attempting to do so generates an SQLCODE -311 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL ADD foreign key constraint when foreign key exists setting. The default is 0 (No), which is the recommended setting for this option. When 1 (Yes), you can add a foreign key through DDL even if one with the same name already exists.

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.

Your table definition should not have two foreign keys with different names that reference the same field-commalist field(s) and perform contradictory referential actions. In accordance with the ANSI standard, InterSystems SQL does not issue an error if you define two foreign keys that perform contradictory referential actions on the same field (for example, ON DELETE CASCADE and ON DELETE SET NULL). Instead, InterSystems SQL issues an error when a DELETE or UPDATE operation encounters these contradictory foreign key definitions.

An ADD FOREIGN KEY that specifies a non-existent foreign key field generates an SQLCODE -31 error.

An ADD FOREIGN KEY that references a non-existent parent key table generates an SQLCODE -310 error. An ADD FOREIGN KEY that references a non-existent field in an existing parent key table generates an SQLCODE -316 error. If you do not specify a parent key field, it defaults to the ID field.

Before issuing an ADD 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 ALTER TABLE is executed via Dynamic SQL or over a SQL driver connection.

An ADD FOREIGN KEY that references a field (or combination of fields) that can take non-unique values generates an SQLCODE -314 error, with additional details available through %msg.

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

An ADD FOREIGN KEY is constrained when data already exists in the table. To change this default constraint behavior, refer to the COMPILEMODE=NOCHECK option of the SET OPTION command.

When you define an ADD FOREIGN KEY constraint for a single field and the foreign key references the idkey of the referenced table, InterSystems IRIS converts the property in the foreign key into a reference property. This conversion is subject to the following restrictions:

  • The table must contain no data.

  • The property on the foreign key cannot be of a persistent class (that is, it cannot already be a reference property).

  • The data types and data type parameters of the foreign key field and the referenced idkey field must be the same.

  • The foreign key field cannot be an IDENTITY field.

ADD CONSTRAINT Restrictions

You can add a constraint to a comma-separated list of fields. For example, you can add the UNIQUE (FName,SurName) constraint, which establishes a UNIQUE constraint on the combined values of the two fields FName and Surname. Similarly, you can add a primary key constraint or a foreign key constraint on a comma-separated list of fields.

A constraint can be named or unnamed. If unnamed, InterSystems SQL generates a constraint name using the table name. For example, MYTABLE_Unique1 or MYTABLE_PKEY1.

The following example creates two unnamed constraints, adding both the unique constraint and the primary key constraint to comma-separated lists of fields:

 ALTER TABLE SQLUser.MyStudents 
    ADD UNIQUE (FName,SurName),PRIMARY KEY (Fname,Surname)

  • A field must exist to be used in a constraint. Specifying a non-existent field generates an SQLCODE -31 error.

  • The RowId field cannot be used in a constraint. Specifying the RowId (ID) field generates SQLCODE -31 error.

  • A stream field cannot be used in a constraint. Specifying a stream field generates an SQLCODE -400 error: “invalid index attribute”

  • A constraint can only be applied once to a field. Specifying the same constraint twice to a field generates an SQLCODE -400 error: “index name conflict”.

By using the optional CONSTRAINT identifier keyword clause, you can create a named constraint. A named constraint must be a valid identifier; constraint names are not case-sensitive. This provides a name for the constraint for future use. This is shown in the following example:

 ALTER TABLE SQLUser.MyStudents 
    ADD CONSTRAINT UnqFullName UNIQUE (FName,SurName)

You can specify multiple constraints as a comma-separated list; the constraint name is applied to the first constraint, the other constraints receive default names.

A constraint name must be unique for the table. Specifying the same constraint name twice to a field generates an SQLCODE -400 error: “index name conflict”.

ALTER COLUMN

ALTER COLUMN can modify the definition of a single column:

  • Rename the column using the syntax ALTER TABLE tablename ALTER COLUMN oldname RENAME newname. Renaming a column changes the SQL field name. It does not change the corresponding persistent class property name. ALTER COLUMN oldname RENAME newname replaces oldfield name references in trigger code and ComputeCode.

  • Change the column characteristics: data type, default value, NULL/NOT NULL, and collation type.

If the table contains data, you cannot change the data type of a column that contains data if this change would result in stream data being typed as non-stream data or non-stream data being typed as stream data. Attempting to do so results in an SQLCODE -374 error. If there is no existing data, this type of datatype change is permitted.

You can use ALTER COLUMN to add, change, or drop a field default value.

If the table contains data, you cannot specify NOT NULL for a column if that column contains null values; this results in an SQLCODE -305 error.

If you change the collation type for a column that contains data, you must rebuild all indexes for that column.

CLEANUP

If you specified %NODELDATA when converting a table between columnar and row-wise storage, you can clean up the latent stale data with CLEANUP. InterSystems strongly recommends cleaning up stale data as soon as you complete a table conversion.

If you issue an ALTER TABLE ... CLEANUP command when there is not stale data associated with the table, the command raises SQLCODE -165. If the cleanup process itself fails, the command raises SQLCODE -166.

Until you clean up stale data from one storage type conversion, you cannot attempt another conversion until the cleanup is resolved; an attempt to start another conversion raises SQLCODE -160.

CONVERT

CONVERT Table to Sharded or Non-Sharded Format

You may convert a non-sharded table to be sharded or vice versa with the CONVERT option. As a prerequisite, you must first enable sharding on your instance and deploy a sharded cluster. See Defining a Sharded Table for more details.

If you specify the IMMEDIATE option, the conversion is started immediately as an asynchronous background process. If you specify the DEFER option, the conversion is started the next time you explicitly rebalance the shards with an explicit call to $SYSTEM.Sharding.Rebalance or through the Management Portal.

While a table is in the process of being converted to either a sharded or non-sharded format, it is treated like a sharded table and may be queried as such.

Convert from Non-Sharded to Sharded

When converting a non-sharded table to be sharded, you must specify the column, or a comma-separated list of columns, to be used as the shard key. For information on choosing a shard key, see Choose a Shard Key. If you use the COSHARD option, you must instead specify the name of the shard key column that is used in cosharded joins with the table defined in the coshardtable.

Convert from Sharded to Non-Sharded

When converting a sharded table to be non-sharded, data stored across the shards is collected in the sharded cluster’s master namespace.

CONVERT Table to Columnar or Row-wise Storage

You may alter the storage type of a table, column, or set of columns with the CONVERT option. When the table conversion is executed, any data currently stored in the table is converted to the new storage type. While the table conversion is underway, you may still insert data with various DML operations (such as INSERT); however, no other process can compile the class under conversion until the conversion is complete.

If a column converted from ROW to COLUMNAR does not meet the following requirements, the command raises SQLCODE -159:

  • For columns with types that take a MAXLEN parameter, the MAXLEN attribute must not be greater than 300

  • The column must not be a stream.

  • The column must not be serial.

  • The column must not be a collection.

  • The column must not be multidimensional.

  • The column must not be %Library.Vector or %Library.List.

By default, the %DELDATA flag is included, causing the table to free up memory occupied by data stored in the old format. When converting from COLUMNAR to ROW, the columnar data is cleaned up synchronously as the end of the conversion; when converting from ROW to COLUMNAR, the system starts a background job to handle the cleanup, while the conversion immediately returns as successful. InterSystems recommends adhering to this behavior.

If the storage conversion completes successful, but the automatic cleanup of stale table data fails, the command raises SQLCODE -164; the failed cleanup is also logged to messages.log. Every hour, a background process checks for failed cleanups and automatically attempts the cleanup again. Each unique cleanup is only attempted once per day. After a week of failed daily cleanups, the system abandons the automatic cleanup process and a SEVERE message is logged to messages.log for manual inspection.

If you manually specify the %NODELDATA flag, the old data slots are not manually cleaned up (that is, are not marked as unallocated). To cleanup stale data, you must use ALTER TABLE table-name CLEANUP.

DROP or DELETE

DROP COLUMN Restrictions

DROP COLUMN can delete multiple column definitions, specified as a comma-separated list. Each listed column name must be followed by its RESTRICT or CASCADE (if unspecified, the default is RESTRICT) and %DELDATA or %NODELDATE (if unspecified, the default is %NODELDATA) options.

By default, deleting a column definition does not delete any data that has been stored in that column from the data map. To delete both the column definition and the data, specify the %DELDATA option.

Deleting a column definition does not delete the corresponding column-level privileges. For example, the privilege granted to a user to insert, update, or delete data on that column. This has the following consequences:

  • If a column is deleted, and then another column with the same name is added, users and roles will have the same privileges on the new column that they had on the old column.

  • Once a column is deleted, it is not possible to revoke object privileges for that column.

For these reasons, it is generally recommended that you use the REVOKE command to revoke column-level privileges from a column before deleting the column definition.

RESTRICT keyword (or no keyword): You cannot drop a column if that column appears in an index, or is defined in a foreign key constraint or other unique constraint. Attempting a DROP COLUMN for that column fails with an SQLCODE -322 error. RESTRICT is the default. See DROP INDEX.

CASCADE keyword: If the column appears in an index, the index will be deleted. There may be multiple indexes. If the column appears in a foreign key, the foreign key will be deleted. There may be multiple foreign keys.

You cannot drop a column if that column is used in COMPUTECODE or in a COMPUTEONCHANGE clause. Attempting to do so results in an SQLCODE -400 error.

DROP CONSTRAINT Restrictions

By default, you cannot drop a unique or primary key constraint if it is referenced by a foreign key constraint. Attempting to do so results in an SQLCODE -317 error. To change this default foreign key constraint behavior, refer to the COMPILEMODE=NOCHECK option of the SET OPTION command.

The effects of dropping a primary key constraint depend on the setting of the Are Primary Keys also ID Keys setting (as described above):

  • If the PrimaryKey index is not also the IDKey index, dropping the primary key constraint drops the index definition.

  • If the PrimaryKey index is also the IDKey index, and there is no data in the table, dropping the primary key constraint drops the entire index definition.

  • If the PrimaryKey index is also the IDKey index, and there is data in the table, dropping the primary key constraint just drops the PRIMARYKEY qualifier from the IDKey index definition.

DROP CONSTRAINT When Non-Existent

By default, InterSystems IRIS rejects an attempt to drop a field constraint on a field that does not have that constraint and issues an SQLCODE -315 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL DROP of non-existent constraint setting. The default is 0 (No), which is the recommended setting. If this option is set to 1 (Yes), an ALTER TABLE DROP CONSTRAINT causes InterSystems IRIS to perform no operation and issue no error message.

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.

MODIFY

MODIFY can modify the definitions of a single column or a comma-separated list of columns.

  • Rename the column using the syntax ALTER TABLE tablename MODIFY oldname RENAME newname. Renaming a column changes the SQL field name. It does not change the corresponding persistent class property name. MODIFY oldname RENAME newname replaces oldfield name references in trigger code and ComputeCode.

  • Change the column characteristics: data type, default value, and other characteristics.

If the table contains data, you cannot change the data type of a column that contains data to an incompatible data type:

  • A data type with a lower (less inclusive) data type precedence if this conflicts with existing data values. Attempting to do so results in an SQLCODE -104 error, with the %msg specifying which field and which data value caused the error.

  • A data type with a smaller MAXLEN or a MAXVAL/MINVAL if this conflicts with existing data values. Attempting to do so results in an SQLCODE -104 error, with the %msg specifying which field and which data value caused the error.

  • A data type change from a stream data type to a non-stream data type or a non-stream data type to a stream data type. Attempting to do so results in an SQLCODE -374 error. If there is no existing data, this type of datatype change is permitted.

You can use MODIFY to add or change a field default value. You cannot use MODIFY to drop a field default value.

If the table contains data, you cannot specify NOT NULL for a column if that column contains null values; this results in an SQLCODE -305 error. The syntax forms ALTER TABLE mytable MODIFY field1 NOT NULL and ALTER TABLE mytable MODIFY field1 CONSTRAINT nevernull NOT NULL perform the same operation. The optional CONSTRAINT identifier clause is a no-op provided for compatibility. InterSystems IRIS does not retain or use this field constraint name. Attempting to drop this field constraint by specifying this field constraint name results in an SQLCODE -315 error.

PARTITION

MOVE

The MOVE PARTITION option allows you to move a certain partition to a separate database. You may specify this partition by isolating a certain range of values or by using defined partition names. To find the defined partition names, query INFORMATION_SCHEMA.TABLE_PARTITIONS and INFORMATION_SCHEMA.TABLE_PARTITION_MAPPINGS.

The following command moves data from partitions covering dates within the year 2023 to the predefined data-2023 database.

ALTER TABLE Demo.Log MOVE PARTITION BETWEEN '2023-01-01' AND '2023-12-31' TO "data-23"

The following command moves data from the specified partition ID to the predefined data-2023 database.

ALTER TABLE Demo.Log MOVE PARTITION ID '202311010000' TO "data-2023"

DROP

The DROP PARTITION option allows you to drop either a subset or all partitions from a table, including all of the data it contained. This command is meant to be used only be administrators, as it skips journaling, locking, triggers, and does not take referential action.

The following command drops a specified partition ID from the table.

ALTER TABLE Demo.Log DROP PARTITION ID '201402010000'

TRUNCATE

The TRUNCATE PARTITION option is similar to the DROP PARTITION option, but the partitions are not dropped alongside the data. As a result, immediately after execution, the partitions still exist, but do not contain any data.

The following command drops the data in the specified partition ID from the table:

ALTER TABLE Demo.Log TRUNCATE PARTITION ID '201402010000'

RENAME

You can rename an existing table using the following syntax:

ALTER TABLE schema.TableName RENAME NewTableName

This operation renames the existing table in its existing schema. You can only change the table name, not the table schema. Specifying a schema name in the NewTableName results in an SQLCODE -1 error. Specifying the same table name for both old and new tables generates an SQLCODE -201 error.

Renaming a table changes the SQL table name. It does not change the corresponding persistent class name.

Renaming a table does not change references to the old table name in triggers.

If a view references the existing table name, attempting to rename the table will fail. This is because attempting to rename the table is an atomic operation that causes a recompile of the view, which generates an SQLCODE -30 error “Table 'schema.oldname' not found”.

STATS

DROP FIXED

The DROP FIXED operation allows you to drop a set of fixed statistics from the class definition, allows the use of collected statistics. This option can be applied at the schema level or at the table level.

ALTER TABLE Sample.People DROP FIXED STATS

FIX

The FIX operations allows you manually fix a set of collected statistics to the class definition, ensuring that those statistics are the ones used to create a query plan and replacing any existing fixed statistics. The option can be applied at the schema level or at the table level.

ALTER TABLE Sample.People FIX STATS

SET RUNTIME

The SET RUNTIME operation allows you to set two parameters at either the schema level or the table level. The first parameter, IGNOREFIXEDSTATS, configures whether collected statistics should override fixed statistics; by default, this is set to FALSE. The second parameter, SKIPAUTOMATICSTATSCOLLECTION, configures whether the schema or table should be excluded from the automatic collected statistics utility.

The following examples cause the system to ignore fixed statistics and the to stop automatically gathering collected statistics.

ALTER SCHEMA Sample SET RUNTIME IGNOREFIXEDSTATS = TRUE
ALTER SCHEMA Sample SET RUNTIME SKIPAUTOMATICSTATSCOLLECTION = FALSE

Examples

The following examples uses Embedded SQL programs to create a table, populate two rows, and then alter the table definition.

To demonstrate this, please run the first two Embedded SQL programs in the order shown. (It is necessary to use two embedded SQL programs here because embedded SQL cannot compile an INSERT statement unless the referenced table already exists.)

  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  &sql(DROP TABLE SQLUser.MyStudents)
      IF SQLCODE=0 { WRITE !,"Deleted table" }
      ELSE { WRITE "DROP TABLE error SQLCODE=",SQLCODE }
  &sql(CREATE TABLE SQLUser.MyStudents (
     FirstName VARCHAR(35) NOT NULL,
     LastName VARCHAR(35) NOT NULL)
     )
     IF SQLCODE=0 { WRITE !,"Created table" }
     ELSE { WRITE "CREATE TABLE error SQLCODE=",SQLCODE }
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  NEW SQLCODE,%msg
  &sql(INSERT INTO SQLUser.MyStudents (FirstName, LastName) 
    VALUES ('David','Vanderbilt'))
  IF SQLCODE=0 { WRITE !,"Inserted data in table"}
      ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
  &sql(INSERT INTO SQLUser.MyStudents (FirstName, LastName)
    VALUES ('Mary','Smith'))
  IF SQLCODE=0 { WRITE !,"Inserted data in table"}
     ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }

The following example uses ALTER TABLE to add the ColorPreference column. Because the column definition specifies a default, the system populates ColorPreference with the value 'Blue' for the two existing rows of the table:

   NEW SQLCODE,%msg
  &sql(ALTER TABLE SQLUser.MyStudents 
    ADD COLUMN ColorPreference VARCHAR(16) NOT NULL DEFAULT 'Blue')
  IF SQLCODE=0 {
    WRITE !,"Added a column",! }
  ELSEIF SQLCODE=-306 {
    WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
  ELSE { WRITE "SQLCODE error=",SQLCODE }

The following example uses ALTER TABLE to add two computed columns: FLName and LFName. For existing rows these columns have no value. For any subsequently inserted row a value is computed for each of these columns:

  NEW SQLCODE,%msg
  &sql(ALTER TABLE SQLUser.MyStudents 
    ADD COLUMN FLName VARCHAR(71) COMPUTECODE { SET {FLName}={FirstName}_" "_{LastName}} 
               COMPUTEONCHANGE (FirstName,LastName),
        COLUMN LFName VARCHAR(71) COMPUTECODE { SET {LFName}={LastName}_ "," _{FirstName}} 
               COMPUTEONCHANGE (FirstName,LastName) )
  IF SQLCODE=0 {
    WRITE !,"Added two computed columns",! }
  ELSE { WRITE "SQLCODE error=",SQLCODE }

See Also

ALTER USER (SQL)Next section
Previous sectionALTER MODEL (SQL)
FeedbackOpens in a new tab