From InterBase

Go Up to Statement and Function Reference (Language Reference Guide)

Changes a table by adding, dropping, or modifying columns or integrity constraints. Available in gpre, DSQL, and isql.

To alter a global temporary table, see: "Altering a global temporary table" in the Data Definition Guide.
 ALTER TABLE <table> operation [, operation ];
operation = ADD col_def
| ADD tconstraint
| ALTER [COLUMN] column_name alt_col_clause
| DROP col
| DROP CONSTRAINT constraint
alt_col_clause = TO new_col_name
| TYPE new_col_data_type
| POSITION new_col_position
col_def = col {data_type | COMPUTED [BY] (expr) | domain}
[DEFAULT {literal | NULL | USER}]
[COLLATE collation]
data_type =
| (DATE | TIME | TIMESTAMP} [array_dim]
| {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim]
[array_dim] [CHARACTER SET charname]
[VARYING] [(int)] [array_dim]
| BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
[CHARACTER SET charname]
| BLOB [(seglen [, subtype])]array_dim = [[x:]y [, [x:]y ]]
expr = A valid SQL expression that results in a single value.
col_constraint = [CONSTRAINT constraint]
| REFERENCES other_table [(other_col [, other_col ])]
| CHECK (search_condition)}
tconstraint = [CONSTRAINT constraint]
{{PRIMARY KEY | UNIQUE} (col [, col ])
| FOREIGN KEY (col [, col ])
REFERENCES other_table [(other_col [, other_col ])]
| CHECK (search_condition)}
search_condition = val operator {val | (select_one)}
| val [NOT] BETWEEN val AND val
| val [NOT] LIKE val [ESCAPE val]
| val [NOT] IN (val [, val ] | select_list)
| val IS [NOT] NULL
| val {&gt;= | &lt;=}
| val [NOT] {= | &lt; | &gt;}
| {ALL | SOME | ANY} (select_list)
| EXISTS (select_expr)
| SINGULAR (select_expr)
| val [NOT] CONTAINING val
| val [NOT] STARTING [WITH] val
| (search_condition)
| NOT search_condition
| search_condition OR search_condition
| search_condition AND search_condition
val = { col [array_dim] | :variable
| constant | expr | function
| udf ([val [, val ]])
| NULL | USER | RDB$DB_KEY | ? }
[COLLATE collation]
constant = num | 'string' | charsetname 'string'
function = COUNT (* | [ALL] val | DISTINCT val)
| SUM ([ALL] val | DISTINCT val)
| AVG ([ALL] val | DISTINCT val)
| MAX ([ALL] val | DISTINCT val)
| MIN ([ALL] val | DISTINCT val)
| CAST (val AS data_type)
| UPPER (val)
| GEN_ID (generator, val)
operator = {= | &lt; | &gt; | &lt;= | &gt;= | !&lt; | !&gt; | &lt;&gt; | !=}
select_one = SELECT on a single column; returns exactly one value.
select_list = SELECT on a single column; returns zero or more values.
select_expr = SELECT on a list of values; returns zero or more values.
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Notes on ALTER TABLE syntax:

  • The column constraints for referential integrity were new in InterBase 5.
  • You cannot specify a COLLATE clause for Blob columns.
  • When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:
my_array = varchar(6)[5,5]
  • Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 20 and ends at 30:
my_array = integer[20:30]
Argument Description


Name of an existing table to modify.


Action to perform on the table. Valid options are:

  •  ADD a new column or table constraint to a table
  •  DROP an existing column or constraint from a table


Description of a new column to add.

  • Must include a column name and <data_type>.
  • Can also include default values, column constraints, and a specific collation order.


Name of the column to add or drop; column name must be unique within the table.


Data type of the column; see Data Types (Language Reference Guide).


Modifies column names, data types, and positions. Can also be used with ENCRYPT and DECRYPT options to encrypt and decrypt a column. For more information about encrypting databases and columns, see “Encrypting Your Data” in the Data Definition Guide.


Specifies that the value of the column’s data is calculated from expr at runtime and is therefore not allocated storage space in the database.

  • <expr> can be any arithmetic expression valid for the data types in the expression.
  • Any columns referenced in <expr> must exist before they can be used in <expr>.
  • <expr> cannot reference Blob columns.
  • <expr> must return a single value, and cannot return an array.


Name of an existing domain.


Specifies a default value for column data; this value is entered when no other entry is made; possible values are:

  • <literal>: Inserts a specified string, numeric value, or date value.
  • NULL: Enters a NULL value; this is the default DEFAULT.
  • USER: Enters the user name of the current user; column must be of compatible text type to use the default.

Defaults set at column level override defaults set at domain level.

CONSTRAINT <constraint>

Name of a column or table constraint; the constraint name must be unique within the table.


Specifies the kind of column constraint; valid options are UNIQUE, PRIMARY KEY, CHECK, and REFERENCES.

CHECK <search_condition>

An attempt to enter a new value in the column fails if the value does not meet the <search_condition>.


Specifies that the column values are derived from column values in another table; if you do not specify column names, InterBase looks for a column with the same name as the referencing column in the referenced table.


Used with REFERENCES: Changes a foreign key whenever the referenced primary key changes; valid options are:

  • [Default] NO ACTION: Does not change the foreign key; may cause the primary key update to fail due to referential integrity checks.
  • CASCADE: For ON DELETE, deletes the corresponding foreign key; for ON UPDATE, updates the corresponding foreign key to the new value of the primary key.
  • SET NULL: Sets all the columns of the corresponding foreign key to NULL.
  • SET DEFAULT: Sets every column of the corresponding foreign key to its default value in effect when the referential integrity constraint is defined; when the default for a foreign column change after the referential integrity constraint is defined, the change does not have an effect on the default value used in the referential integrity constraint.


Specifies that a column cannot contain a NULL value.

  • If a table already has rows, a new column cannot be NOT NULL.
  • NOT NULL is a column attribute only.


Drops the specified table constraint.


Description of the new table constraint; constraints can be PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK.

COLLATE <collation>

Establishes a default sorting behavior for the column; see Character Sets and Collation Orders for more information.

Description: ALTER TABLE modifies the structure of an existing table. A single ALTER TABLE statement can perform multiple adds and drops.

  • A table can be altered by its creator, the SYSDBA user, and any users with operating system superuser privileges.
  • ALTER TABLE fails if the new data in a table violates a PRIMARY KEY or UNIQUE constraint definition added to the table. Dropping or altering a column fails if any of the following are true:
  • The column is part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint.
  • The column is used in a CHECK constraint.
  • The column is used in the <value> expression of a computed column.
  • The column is referenced by another database object such as a view.
When a column is dropped, all data stored in it is lost.


  • Referential integrity constraints include optional ON UPDATE and ON DELETE clauses. They define the change to be made to the referencing column when the referenced column is updated or deleted.
  • To delete a column referenced by a computed column, you must drop the computed column before dropping the referenced column. To drop a column referenced in a FOREIGN KEY constraint, you must drop the constraint before dropping the referenced column. To drop a PRIMARY KEY or UNIQUE constraint on a column that is referenced by FOREIGN KEY constraints, drop the FOREIGN KEY constraint before dropping the PRIMARY KEY or UNIQUE key it references.
  • You can create a FOREIGN KEY reference to a table that is owned by someone else only if that owner has explicitly granted you the REFERENCES privilege on that table using GRANT. Any user who updates your foreign key table must have REFERENCES or SELECT privileges on the referenced primary key table.
  • You can add a check constraint to a column that is based on a domain but be aware that changes to tables that contain CHECK constraints with subqueries may cause constraint violations.
  • Naming column constraints is optional. If you do not specify a name, InterBase assigns a system-generated name. Assigning a descriptive name can make a constraint easier to find for changing or dropping, and more descriptive when its name appears in a constraint violation error message.
  • When creating new columns in tables with data, do not use the UNIQUE constraint. If you use the NOT NULL constraint on a table with data, you should also specify a default value.

Example: The following isql statement adds a column to a table and drops a column:


This statement results in the loss of all data in the dropped CURRENCY column.

The next isql statement changes the name of the LARGEST_CITY column to BIGGEST_CITY:


NO RESERVE SPACE for Database and User Tables

This feature is useful if you have very, large databases (VLDB) with tables that are archival in nature. An archival table means that the rows of a table are infrequently or never UPDATED or DELETED; have complex queries, such as aggregates and analytics that process a high percentage of rows; and where indexes are rebuilt and the database is backed and/or restored frequently. These database operations could see a performance improve of 20% or more with a savings in storage space.

By default, InterBase reserves a small amount of space in each data page of a table to optimize UPDATE and DELETE operations on resident rows. This reserve space can amount to 20% or more of the total space occupied by all of the rows of the table. Some tables archive historical data or data that are UPDATED infrequently or not at all and their rows may never be deleted. Database operations that process most or all of the rows, such as backup, restore, index creation, aggregate computation have always suffered performance penalties proportional to this reservation overhead.

For this reason, a CREATE/ALTER TABLE clause is introduced that prevents space reservation and maximizes row packing for the most efficient fill ratio. At the database level, it has been possible to restore a database with the -USE_ALL_SPACE switch so that no space is reserved for any table. To change the storage behavior in a like manner for new or existing databases, the same clause is introduced for CREATE/ALTER DATABASE.

User Interface To effect the new storage behavior, a non-standard SQL clause is added:

Clause is presented before the secondary file specification.

Clause is presented in any order with other SET elements.


Clause is presented in any order with other ADD, DROP, ALTER elements.


This causes newly INSERTED rows to not reserve space on their data page for a DELETE record version stub, as would normally be the case. Over many row insertions, a decrease in storage size should be observed relative to what the table size would be in the absence of this feature. The optional NO keyword when used with ALTER TABLE toggles the behavior to the alternate state of the current storage behavior for the table.

The NO RESERVE storage modifier is preserved across database backup and restore. This state is stored as flag bit 64 (0x100) of RDB$RELATIONS.RDB$FLAGS for the user's table entry in the system table RDB$RELATIONS.

The clause is displayed by ISQL's SHOW TABLE command following the enumeration of a table's column definitions. It is also visible using ISQL's Extract (-x) command in a syntax-correct manner for the CREATE TABLE output of the respective table listing. The state for database-wide storage behavior is stored in a like manner for the RDB$DATABASE entry in RDB$RELATIONS.


A temporary table can be altered in the same way as a permanent base table although there is no official support to toggle the behavior of the ON COMMIT clause. The specification offers an ALTER TABLE syntax to toggle that behavior.


RESTRICT will report an error if there are dependencies by other temporary tables on the current table scope. CASCADE will automatically propagate this table scope change to other temporary tables to maintain compliance. The default action is RESTRICT.

For example, assume that TT1 is a temporary table with ON COMMIT PRESERVE and has a foreign reference to temporary table TT2 which is also ON COMMIT PRESERVE. If an attempt is made to modify TT2 to ON COMMIT DELETE, an error is raised because an ON COMMIT PRESERVE table is not allowed by the SQL standard to have a referential constraint on an ON COMMIT DELETE table. RESTRICT returns this error while CASCADE would also alter TT1 to have ON COMMIT DELETE. Thus, CASCADE implements transitive closure when ON COMMIT behavior is modified.

This specification of ALTER TABLE extension does not allow a table to be toggled between temporary and persistent.

See Also

Advance To: