Skip to main content
Next section

ALTER TABLE

Modifies a table.

Synopsis

ALTER TABLE table alter-action

where alter-action is one of the following:
     ADD  [(] add-action {,add-action} [)] |
     DROP drop-action |
     DELETE drop-action |
     ALTER [COLUMN] field alter-column-action |
     MODIFY modification-spec

add-action ::= 
     [CONSTRAINT identifier]
     [(] FOREIGN KEY (field-commalist) 
          REFERENCES table (field-commalist)
          [referential-action] [)]
     |
     [(] UNIQUE (field-commalist)  [)] 
     |
     [(] PRIMARY KEY (field-commalist) [)] 
     | 
     DEFAULT [(] default-spec [)] FOR field
     |
     [COLUMN] [(] field datatype  [sqlcollation] 
           [%DESCRIPTION string]
           [DEFAULT [(] default-spec [)] ]
           [ON UPDATE update-spec ]
           [UNIQUE] [NOT NULL]
           [REFERENCES table (field-commalist) ]
           [)]

drop-action ::= 
     FOREIGN KEY identifier |
     PRIMARY KEY |
     CONSTRAINT identifier |
     [COLUMN] field [RESTRICT | CASCADE] 

alter-column-action ::= 
     datatype | 
     [SET] DEFAULT [(] default-spec [)]  |  DROP DEFAULT | 
     NULL |  NOT NULL | 
     COLLATE sqlcollation

modification-spec ::=
     field [datatype] 
          [DEFAULT [(] default-spec [)]]
          [CONSTRAINT identifier] [NULL] [NOT NULL]

sqlcollation ::=
     { %ALPHAUP | %EXACT | %MINUS | %MVR | %PLUS | %SPACE |   
        %SQLSTRING [(maxlen)] | %SQLUPPER [(maxlen)] |
        %STRING [(maxlen)] |  %TRUNCATE[(maxlen)] | %UPPER }

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. For further details on valid identifiers, see the “Identifiers” chapter of Using InterSystems SQL.
datatype A valid InterSystems SQL data type. For a list of valid data types, see the SQL reference material at the end of this manual.
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.
COLLATE sqlcollation Optional — Specify one of the following SQL collation types: %EXACT, %MINUS, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE, or %MVR. The default is the namespace default collation (%SQLUPPER, unless changed). %SQLSTRING, %SQLUPPER, and %TRUNCATE may be specified with an optional maximum length truncation argument, an integer enclosed in parentheses. The percent sign (%) prefix to these collation parameter keywords is optional. The COLLATE keyword is optional. For further details refer to Table Field/Property Definition Collation in the “Collation” chapter of Using InterSystems SQL.

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.

An ALTER TABLE ADD statement can add multiple columns and/or constraints to a table. You specify the keyword ADD once, followed by a comma-separated list. You can use a comma-separated list to add multiple new columns to a table, add a list of constraints to existing columns, or both add new columns and add constraints to existing columns.

The ALTER TABLE ALTER COLUMN statement can alter the definition of a column. It cannot alter multiple columns.

The ALTER TABLE MODIFY statement can only operate on a single column.

The ALTER TABLE DROP statement can drop a column from a table or drop a constraint from a field or group of fields. DROP can only operate on a single column or constraint. 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.TableExists() 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 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'.

You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can determine if a specified user has %ALTER privilege by invoking the $SYSTEM.SQL.CheckPriv() method. You can use the GRANT command to assign %ALTER_TABLE or %ALTER privileges, if you hold appropriate granting privileges. In embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )
Copy code to clipboard

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.Security in the InterSystems Class Reference.

ALTER TABLE cannot be used on a table created by defining 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'>

The ALTER TABLE statement 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.

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.

  • 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, Caché 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.Counter) 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.

ALTER COLUMN Restrictions

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 indices for that column.

MODIFY column Restrictions

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

DROP COLUMN Restrictions

Deleting a column definition does not delete any data that has been stored in that column from the data map.

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.

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. See DROP INDEX.

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.

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)  
Copy code to clipboard
  • 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)  
Copy code to clipboard

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

ADD PRIMARY KEY Restrictions

A primary key value is required and unique. Therefore, adding a primary key constraint to an existing field or combination of fields makes each of these fields a required field. If you add a primary key constraint to a list of existing fields, the combined values of these fields must be unique. You cannot add a primary key constraint to an existing field if that field permits NULL values. You cannot add a primary key constraint to a field (or list of fields) if that field (or list of fields) contain non-unique values.

If you add a primary key constraint to an existing field, the field may also be automatically defined as an IDKey index. This depends on whether data is present and upon a configuration setting established in one of the following ways:

  • The SQL SET OPTION PKEY_IS_IDKEY statement.

  • The $SYSTEM.SQL.SetDDLPKeyNotIDKey() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings().

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Are Primary Keys Created through DDL not ID Keys. If set to “Yes” (1), when a Primary Key constraint is specified through DDL it does not automatically become the IDKey index in the class definition. If “No” (0), it does become the IDKey index. Setting this value to “No” can give better performance, but means that the Primary Key fields cannot be updated.

The default is “Yes” (1). If this option is set to “No” (0), and the field does not contain data, the primary key index is also defined as the IDKey index. If this option is set to “No”, and the field does contain data, the IDKey index is not defined.

If CREATE TABLE defined a bitmap extent index and later you use ALTER TABLE to add a primary key that is also the IDKey, the system automatically drops the bitmap extent index.

ADD PRIMARY KEY When Already Exists

What happens when you try to add a primary key to a table that already has a defined primary key is configuration-dependent. By default, Caché rejects an attempt to define a primary key when one already exists and issues an SQLCODE -307 error. You can set this behavior as follows:

  • The $SYSTEM.SQL.SetDDLNo307() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Suppress SQLCODE=-307 Errors setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow Create Primary Key Through DDL When Key Exists.

The default is “No” (0). This is the recommended setting for this option.

If this option is set to “Yes” (1), an ALTER TABLE ADD PRIMARY KEY causes Caché to remove the primary key index from the class definition, and then recreates this index using the specified primary key field(s).

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

By default, you cannot have two foreign keys with the same name. Attempting to do so generates an SQLCODE -311 error. This option is configurable using:

  • The $SYSTEM.SQL.SetDDLNo311() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Suppress SQLCODE=-311 Errors setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow DDL ADD Foreign Key Constraint When Foreign Key Exists.

The default is “No” (0). This is the recommended setting for this option. When “Yes” (1), you can add a foreign key through DDL even if one with the same name already exists. When “No” (0), this action generates an SQLCODE -311 error.

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, Caché 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, Caché 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 xDBC.

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.

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, Caché 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.

For further information on foreign keys, refer to the CREATE TABLE command, and to the “Using Foreign Keys” chapter in Using Caché SQL.

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

What happens when you try to drop a field constraint on a field that does not have that constraint depends on a configuration setting.

  • The $SYSTEM.SQL.SetDDLNo315() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Suppress SQLCODE=-315 Errors setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow DDL DROP of Non-constraint.

The default is “No” (0). By default, Caché rejects an attempt to drop a constraint that does not exist and issues an SQLCODE -315 error. However, if this option is set to “Yes” (1), an ALTER TABLE DROP CONSTRAINT causes Caché to perform no operation and not issue an error message.

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 }
Copy code to clipboard
  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 }
Copy code to clipboard

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 }
Copy code to clipboard

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 }
Copy code to clipboard

See Also