FirebirdSQL logo
 DOMAININDEX 

ALTER TABLE

Alters a table

Available in

DSQL, ESQL

Syntax
ALTER TABLE tablename
  <operation> [, <operation> ...]

<operation> ::=
    ADD <col_def>
  | ADD <tconstraint>
  | DROP colname
  | DROP CONSTRAINT constr_name
  | ALTER [COLUMN] colname <col_mod>
  | ALTER SQL SECURITY {INVOKER | DEFINER}
  | DROP SQL SECURITY
  | {ENABLE | DISABLE} PUBLICATION

<col_mod> ::=
    TO newname
  | POSITION newpos
  | <regular_col_mod>
  | <computed_col_mod>
  | <identity_col_mod>

<regular_col_mod> ::=
    TYPE {<datatype> | domainname}
  | SET DEFAULT {<literal> | NULL | <context_var>}
  | DROP DEFAULT
  | {SET | DROP} NOT NULL

<computed_col_mod> ::=
    [TYPE <datatype>] {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)

<identity_col_mod> ::=
    SET GENERATED {ALWAYS | BY DEFAULT} [<identity_mod_option>...]
  | <identity_mod_options>...
  | DROP IDENTITY

<identity_mod_options> ::=
    RESTART [WITH restart_value]
  | SET INCREMENT [BY] inc_value

!! See CREATE TABLE syntax for further rules !!
Table 1. ALTER TABLE Statement Parameters
Parameter Description

tablename

Name (identifier) of the table

operation

One of the available operations altering the structure of the table

colname

Name (identifier) for a column in the table.The maximum length is 63 characters.Must be unique in the table.

domain_name

Domain name

newname

New name (identifier) for the column.The maximum length is 63 characters.Must be unique in the table.

newpos

The new column position (an integer between 1 and the number of columns in the table)

other_table

The name of the table referenced by the foreign key constraint

literal

A literal value that is allowed in the given context

context_var

A context variable whose type is allowed in the given context

check_condition

The condition of a CHECK constraint that will be satisfied if it evaluates to TRUE or UNKNOWN/NULL

restart_value

The first value of the identity column after restart

inc_value

The increment (or step) value of the identity column;zero (0) is not allowed.

The ALTER TABLE statement changes the structure of an existing table.With one ALTER TABLE statement it is possible to perform multiple operations, adding/dropping columns and constraints and also altering column specifications.

Multiple operations in an ALTER TABLE statement are separated with commas.

Version Count Increments

Some changes in the structure of a table increment the metadata change counter (“version count”) assigned to every table.The number of metadata changes is limited to 255 for each table, or 32,000 for each view.Once the counter reaches this limit, you will not be able to make any further changes to the structure of the table or view without resetting the counter.

To reset the metadata change counter
You need to back up and restore the database using the gbak utility.