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.

Important: 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_datatype
| POSITION new_col_position
col_def = col {datatype | COMPUTED [BY] (expr) | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL]
[col_constraint]
[COLLATE collation]
datatype =
{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 datatype)
| 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.

Important: 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. See <constraint_def> in Table 1.5 and the Description for ALTER TABLE.
• 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.

The ALTER TABLE statement

Argument	Description
<table>	Name of an existing table to modify.
<operation>	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
<col_def>	Description of a new column to add. Must include a column name and <datatype>. Can also include default values, column constraints, and a specific collation order.
<col>	Name of the column to add or drop; column name must be unique within the table.
<datatype>	Data type of the column; see Data Types (Language Reference Guide).
ALTER[COLUMN]	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.
COMPUTED[BY]<expr>	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.
<domain>	Name of an existing domain.
DEFAULT	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 overrides defaults set at domain level.
CONSTRAINT <constraint>	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 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>.
REFERENCES	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.
ON DELETE | ON UPDATE	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 changes after the referential integrity constraint is defined, the change does not have an effect on the default value used in the referential integrity constraint.
NOT NULL	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.
DROP CONSTRAINT	Drops the specified table constraint.
<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.

Important: When a column is dropped, all data stored in it is lost.

Constraints:

• 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. The values for these cascading referential integrity options are given in Table 1.5, “The ALTER TABLE statement,” on page 20.
• 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:

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;

See Also

• ALTER DOMAIN
• CREATE DOMAIN
• CREATE TABLE
• Embedded SQL Guide
• Data Definition Guide