ALTER TABLE
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
| [ON COMMIT {PRESERVE | DELETE} ROWS [RESTRICT | CASCADE]]
| [SET [NO] RESERVE SPACE]
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}]
[NOT NULL]
[col_constraint]
[COLLATE collation]
data_type =
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[array_dim]
| (DATE | TIME | TIMESTAMP} [array_dim]
| {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim]
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
[array_dim] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
[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 …]]
| BOOLEAN
expr = A valid SQL expression that results in a single value.
col_constraint = [CONSTRAINT constraint]
{ UNIQUE
| PRIMARY KEY
| REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK (search_condition)}
tconstraint = [CONSTRAINT constraint]
{{PRIMARY KEY | UNIQUE} (col [, col …])
| FOREIGN KEY (col [, col …])
REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| 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 {>= | <=}
| val [NOT] {= | < | >}
| {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 = {= | < | > | <= | >= | !< | !> | <> | !=}
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]
- For the full syntax of search_condition, see CREATE TABLE.
Argument | Description |
---|---|
<table> |
Name of an existing table to modify. |
<operation> |
Action to perform on the table. Valid options are:
|
<col_def> |
Description of a new column to add.
|
<col> |
Name of the column to add or drop; column name must be unique within the table. |
<data_type> |
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.
|
<domain> |
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:
Defaults set at column level override defaults set at domain level. |
|
Name of a column or table constraint; the constraint name must be unique within the table. |
<constraint_def> |
Specifies the kind of column constraint; valid options are |
|
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
|
|
Specifies that a column cannot contain a
|
|
Drops the specified table constraint. |
<table_constraint> |
Description of the new table constraint; constraints can be |
|
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 aPRIMARY KEY
orUNIQUE
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
, orFOREIGN 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.
- The column is part of a
When a column is dropped, all data stored in it is lost.
Constraints:
- Referential integrity constraints include optional
ON UPDATE
andON 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 aPRIMARY KEY
orUNIQUE
constraint on a column that is referenced byFOREIGN KEY
constraints, drop theFOREIGN KEY
constraint before dropping thePRIMARY KEY
orUNIQUE
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 theREFERENCES
privilege on that table usingGRANT
. Any user who updates your foreign key table must haveREFERENCES
orSELECT
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:
ALTER TABLE COUNTRY
ADD CAPITAL VARCHAR(25),
DROP CURRENCY;
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
:
ALTER TABLE COUNTRY ALTER LARGEST_CITY 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.
ALTER DATABASE ... SET [NO] RESERVE SPACE
Clause is presented in any order with other ADD, DROP, ALTER elements.
ALTER TABLE <table name> ... SET [NO] RESERVE SPACE
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.
ON COMMIT
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.
ALTER TABLE <table> ON COMMIT {PRESERVE | DELETE} ROWS [ {RESTRICT | CASCADE} ]
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.