ALTER FOREIGN TABLE (SQL)

Alters a foreign table definition.

Synopsis

Change Column Name

ALTER FOREIGN TABLE table-name ALTER [ COLUMN ] old-name RENAME new-name
ALTER FOREIGN TABLE table-name MODIFY old-name RENAME new-name, old-name2 RENAME new-name2, ... 
ALTER FOREIGN TABLE table-name ALTER [ COLUMN ] old-name RENAME new-name VALUES ( external-name ) 
ALTER FOREIGN TABLE table-name MODIFY old-name RENAME new-name, old-name2 RENAME new-name2, ... VALUES ( newexternal-name, newexternal-name2, ... )

Change Datatypes

ALTER FOREIGN TABLE table-name ALTER col-name datatype
ALTER FOREIGN TABLE table-name MODIFY col-name datatype {, col-name datatype ...}

Arguments

Arguments	Description
table-name	The name for the foreign table that will be altered. A valid identifier. Must be the name of a foreign table that exists on a foreign server before this command is issued.
old-name	The name of the column within InterSystems IRIS that will be changed. A valid identifier. Must correspond with the name of a column that exists in the foreign table before this command is issued.
new-name	The new name of the column within InterSystems IRIS. A valid identifier.
external-name	The new name of the column in the external data source that projects data into the corresponding column in the RENAME clause.
col-name	The name of the column that will be converted to a new data type. A valid identifier. Must correspond with the name of a column that exists in the foreign table before this command is issued.
dataype	The new datatype of the column. Must be a valid SQL data type.

Description

The ALTER FOREIGN TABLE command modifies a foreign table definition. There are two types of alterations you may apply to a given table:

Change the column name(s) of a column or list of columns.
Change the data type(s) of a column or list of columns.

Change Column Names

You may use the ALTER FOREIGN TABLE command to change the column names of a single column or a list of columns in a foreign table.

There are two variations:

ALTER FOREIGN TABLE table-name ALTER [ COLUMN ] old-name RENAME new-name renames a column of the foreign table from old-name to new-name.
ALTER FOREIGN TABLE table-name MODIFY old-name RENAME new-name renames one or more columns of the foreign table from their old-name to their corresponding new-name.
ALTER FOREIGN TABLE table-name ALTER [ COLUMN ] old-name RENAME new-name VALUES ( external-name ) renames a column of the foreign table from old-name to new-name. This variation also changes the name of the column in the external data source that projects data into the specified column.
ALTER FOREIGN TABLE table-name MODIFY old-name RENAME new-name, old-name2 RENAME new-name2 VALUES ( external-name, external-name2 ) renames a series of columns of the foreign table from the old-name to the corresponding new-name. This variation also changes the names of the columns in the external data source that projects data into the specified columns.

Note:

InterSystems does not recommend changing the name of a column or set of columns with the ALTER FOREIGN TABLE command. Instead, because a foreign table is merely a projection of data from another source, if you intend to make significant changes to the external data source, you should drop the foreign table, edit the database or .csv file, and then recreate the foreign table.

Change Column Data Types

You may use the ALTER FOREIGN TABLE command to convert the data types of a column or a list of columns in a foreign table. The new data type(s) must be valid InterSystems SQL data type(s).

You may not change the data type of a column if the 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 a SQLCODE -374 error.

There are two variations:

ALTER FOREIGN TABLE table-name ALTER col-name datatype changes the data type of a single column.
ALTER FOREIGN TABLE table-name MODIFY col-name datatype {, col-name datatype ...} changes the data type(s) of one or more columns. You may specify a different data type for each column.

Examples

The following example change the names of the LastName column on a foreign table called Sample.Person. The example shows both the ALTER and MODIFY forms of the command.

ALTER FOREIGN TABLE Sample.Person ALTER COLUMN LastName RENAME Surname
ALTER FOREIGN TABLE Sample.Person MODIFY LastName RENAME FamilyName, FirstName RENAME GivenName

The following example changes the data type of the Amount column on a foreign table called Sample.Account. The example shows both the ALTER and MODIFY forms of the commands.

ALTER FOREIGN TABLE Sample.Person ALTER Amount INTEGER
ALTER FOREIGN TABLE Sample.Person MODIFY Amount INTEGER