Caché SQL Reference
ALTER TABLE
 [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Modifies a table.
Synopsis
ALTER TABLE table alter-table-action

where alter-table-action is one of the following:
     ADD add-action
     DROP drop-action
     DELETE drop-action
     ALTER [COLUMN] identifier alter-column-action
     MODIFY modification-spec

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

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

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

modification-spec ::=
     identifier [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
table The name of the table to be altered.
identifier The name of the column to be modified. For further details on valid identifiers, see the “Identifiers” chapter of Using Caché SQL.
datatype A valid Caché 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). The %ALPHAUP, %STRING, and %UPPER collation types are deprecated and should not be used. %SQLSTRING, %SQLUPPER, %STRING, 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 Caché 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 operation in each ALTER TABLE statement. The ALTER TABLE DROP statement and the ALTER TABLE DELETE statement 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. Prior to using ALTER TABLE it is necessary for your process to have either %ALTER_TABLE administrative privilege or an %ALTER object privilege for the specified table. Failing to do so results in an SQLCODE -99 error (Privilege Violation). 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(      )
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
If you attempt to add a field to a table through an ALTER TABLE tablename ADD COLUMN statement:
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.
If you attempt to add an integer counter field to a table through an ALTER TABLE tablename ADD COLUMN statement:
ALTER COLUMN Restriction
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.
If you change the collation type for a column that contains data, you must rebuild all indices for that column.
DROP COLUMN Restrictions
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.
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:
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.
If the column to be deleted 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.
ADD PRIMARY KEY Restrictions
You cannot add a primary key constraint to an existing field if that field contains non-unique data or permits NULL 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 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, Caché 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 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 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 identifier-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 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.
An ADD FOREIGN KEY that references a field (or combination of fields) that can take non-unique values fails with an SQLCODE -314 error, with additional details available through %msg.
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:
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):
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 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 example uses two embedded SQL programs to create a table, populate two rows, and then alter the table definition. The ALTER TABLE command creates the ColorPreference column and populates it with the value 'Blue' for the two pre-existing rows of the table.
To demonstrate this, please run the 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 Sample.PTest)
      IF SQLCODE=0 { WRITE !,"Deleted table" }
      ELSE { WRITE "DROP TABLE error SQLCODE=",SQLCODE }
  &sql(CREATE TABLE Sample.PTest (
     Id      INT NOT NULL,
     Name    VARCHAR(35),
     DOB     DATE,
     CONSTRAINT PTestPK PRIMARY KEY (Id) )
     )
     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 Sample.PTest (Id, Name, DOB) 
    VALUES (1, 'David Vanderbilt', 46639))
  IF SQLCODE=0 { WRITE !,"Inserted data in table"}
  ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
  &sql(INSERT INTO Sample.PTest (Id, Name, DOB) 
    VALUES (2, 'Mary Smith', 49759))
  IF SQLCODE=0 { WRITE !,"Inserted data in table"}
  ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
  &sql(ALTER TABLE Sample.PTest 
    ADD COLUMN ColorPreference %String NOT NULL DEFAULT 'Blue')
  IF SQLCODE=0 {
    WRITE !,"Altered table, SQLCODE=",SQLCODE }
  ELSEIF SQLCODE=-306 {
    WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
  ELSE { WRITE "SQLCODE error=",SQLCODE }
 
To view the data, go to the Management Portal, select the Globals option for the SAMPLES namespace. Scroll to “Sample.PTestD” and click the Data option.
See Also