FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 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.

docnext count = 25

The SET NOT NULL and DROP NOT NULL Clauses

The SET NOT NULL clause adds a NOT NULL constraint on an existing table column.Contrary to definition in CREATE TABLE, it is not possible to specify a constraint name.

Note

The successful addition of the NOT NULL constraint is subject to a full data validation on the table, so ensure that the column has no nulls before attempting the change.

An explicit NOT NULL constraint on domain-based column overrides domain settings.In this scenario, changing the domain to be nullable does not extend to a table column.

Dropping the NOT NULL constraint from the column if its type is a domain that also has a NOT NULL constraint, has no observable effect until the NOT NULL constraint is dropped from the domain as well.

The COMPUTED [BY] or GENERATED ALWAYS AS Clauses

The data type and expression underlying a computed column can be modified using a COMPUTED [BY] or GENERATED ALWAYS AS clause in the ALTER TABLE ALTER [COLUMN] statement.Conversion of a regular column to a computed one and vice versa is not permitted.

Changing Identity Columns

For identity columns (SET GENERATED {ALWAYS | BY DEFAULT}) it is possible to modify several properties using the following clauses.

Identity Type

The SET GENERATED {ALWAYS | BY DEFAULT} changes an identity column from ALWAYS to BY DEFAULT and vice versa.It is not possible to use this to change a regular column to an identity column.

RESTART

The RESTART clause restarts the sequence used for generating identity values.If only the RESTART clause is specified, then the sequence resets to the initial value specified when the identity column was defined.If the optional WITH restart_value clause is specified, the sequence will restart with the specified value.

Note

In Firebird 3.0, RESTART WITH restart_value would also change the configured initial value to restart_value.This was not compliant with the SQL standard, so since Firebird 4.0, RESTART WITH restart_value will only restart the sequence with the specified value.Subsequent RESTARTs (without WITH) will use the START WITH value specified when the identity column was defined.

It is currently not possible to change the configured start value.

SET INCREMENT

The SET INCREMENT clause changes the increment of the identity column.

DROP IDENTITY

The DROP IDENTITY clause will change an identity column to a regular column.

Note

It is not possible to change a regular column to an identity column.

Changing SQL Security

Using the ALTER SQL SECURITY or DROP SQL SECURITY clauses, it is possible to change or drop the SQL Security property of a table.After dropping SQL Security, the default value of the database is applied at runtime.

Note

If the SQL Security property is changed for a table, triggers that do not have an explicit SQL Security property will not see the effect of the change until the next time the trigger is loaded into the metadata cache.

Replication Management

To stop replicating a table, use the DISABLE PUBLICATION clause.To start replicating a table, use the ENABLE PUBLICATION clause.

The change in publication status takes effect at commit.

Attributes that Cannot Be Altered

The following alterations are not supported:

  • Changing the collation of a character type column

Who Can Alter a Table?

The ALTER TABLE statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

Examples Using ALTER TABLE

  1. Adding the CAPITAL column to the COUNTRY table.

    ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25);

  2. Adding the CAPITAL column with the NOT NULL and UNIQUE constraint and deleting the CURRENCY column.

    ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,
  DROP CURRENCY;

  3. Adding the CHK_SALARY check constraint and a foreign key to the JOB table.

    ALTER TABLE JOB
  ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
  ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);

  4. Setting default value for the MODEL field, changing the type of the ITEMID column and renaming the MODELNAME column.

    ALTER TABLE STOCK
  ALTER COLUMN MODEL SET DEFAULT 1,
  ALTER COLUMN ITEMID TYPE BIGINT,
  ALTER COLUMN MODELNAME TO NAME;

  5. Restarting the sequence of an identity column.

    ALTER TABLE objects
  ALTER ID RESTART WITH 100;

  6. Changing the computed columns NEW_SALARY and SALARY_CHANGE.

    ALTER TABLE SALARY_HISTORY
  ALTER NEW_SALARY GENERATED ALWAYS AS
    (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
  ALTER SALARY_CHANGE COMPUTED BY
    (OLD_SALARY * PERCENT_CHANGE / 100);
See also

[fblangref50-ddl-tbl-create], [fblangref50-ddl-tbl-drop], [fblangref50-ddl-domn-create]

The ADD Clause

With the ADD clause you can add a new column or a new table constraint.The syntax for defining the column and the syntax of defining the table constraint correspond with those described for [fblangref50-ddl-tbl-create] statement.

Effect on Version Count

  • Each time a new column is added, the metadata change counter is increased by one

  • Adding a new table constraint does not increase the metadata change counter

Caution
Points to Be Aware of

  1. Adding a column with a NOT NULL constraint without a DEFAULT value will fail if the table has existing rows.When adding a non-nullable column, it is recommended either to set a default value for it, or to create it as nullable, update the column in existing rows with a non-null value, and then add a NOT NULL constraint.

  2. When a new CHECK constraint is added, existing data is not tested for compliance.Prior testing of existing data against the new CHECK expression is recommended.

  3. Although adding an identity column is supported, this will only succeed if the table is empty.Adding an identity column will fail if the table has one or more rows.

The DROP Clause

The DROP colname clause deletes the specified column from the table.An attempt to drop a column will fail if anything references it.Consider the following items as sources of potential dependencies:

  • column or table constraints

  • indexes

  • stored procedures, functions and triggers

  • views

Effect on Version Count

  • Each time a column is dropped, the table’s metadata change counter is increased by one.

The DROP CONSTRAINT Clause

The DROP CONSTRAINT clause deletes the specified column-level or table-level constraint.

A PRIMARY KEY or UNIQUE key constraint cannot be deleted if it is referenced by a FOREIGN KEY constraint in another table.It will be necessary to drop that FOREIGN KEY constraint before attempting to drop the PRIMARY KEY or UNIQUE key constraint it references.

Effect on Version Count

  • Deleting a column constraint or a table constraint does not increase the metadata change counter.

The ALTER [COLUMN] Clause

With the ALTER [COLUMN] clause, attributes of existing columns can be modified without the need to drop and re-add the column.Permitted modifications are:

  • change the name (does not affect the metadata change counter)

  • change the data type (increases the metadata change counter by one)

  • change the column position in the column list of the table (does not affect the metadata change counter)

  • delete the default column value (does not affect the metadata change counter)

  • set a default column value or change the existing default (does not affect the metadata change counter)

  • change the type and expression for a computed column (does not affect the metadata change counter)

  • set the NOT NULL constraint (does not affect the metadata change counter)

  • drop the NOT NULL constraint (does not affect the metadata change counter)

  • change the type of an identity column, or change an identity column to a regular column

  • restart an identity column

  • change the increment of an identity column

Renaming a Column: the TO Clause

The TO keyword with a new identifier renames an existing column.The table must not have an existing column that has the same identifier.

It will not be possible to change the name of a column that is included in any constraint: primary key, unique key, foreign key, or CHECK constraints of the table.

Renaming a column will also be disallowed if the column is used in any stored PSQL module or view.

Changing the Data Type of a Column: the TYPE Clause

The keyword TYPE changes the data type of an existing column to another, allowable type.A type change that might result in data loss will be disallowed.As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

Changing the Position of a Column: the POSITION Clause

The POSITION keyword changes the position of an existing column in the notional “left-to-right” layout of the record.

Numbering of column positions starts at 1.

  • If a position less than 1 is specified, an error message will be returned

  • If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.

The DROP DEFAULT and SET DEFAULT Clauses

The optional DROP DEFAULT clause deletes the current default value for the column.

  • If the column is based on a domain with a default value, the default value will revert to the domain default

  • An error will be raised if an attempt is made to delete the default value of a column which has no default value or whose default value is domain-based

The optional SET DEFAULT clause sets a default value for the column.If the column already has a default value, it will be replaced with the new one.The default value applied to a column always overrides one inherited from a domain.

DROP TABLE

Drops a table

Available in

DSQL, ESQL

Syntax
DROP TABLE tablename
Table 1. DROP TABLE Statement Parameter
Parameter Description

tablename

Name (identifier) of the table

The DROP TABLE statement drops (deletes) an existing table.If the table has dependencies, the DROP TABLE statement will fail with an error.

When a table is dropped, all its triggers and indexes will be deleted as well.

Who Can Drop a Table?

The DROP TABLE statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the DROP ANY TABLE privilege

Example of DROP TABLE

Dropping the COUNTRY table.
DROP TABLE COUNTRY;
See also

[fblangref50-ddl-tbl-create], [fblangref50-ddl-tbl-alter], [fblangref50-ddl-tbl-recreate]

RECREATE TABLE

Drops a table if it exists, and creates a table

Available in

DSQL

Syntax
RECREATE [GLOBAL TEMPORARY] TABLE tablename
  [EXTERNAL [FILE] 'filespec']
  (<col_def> [, {<col_def> | <tconstraint>} ...])
  [{<table_attrs> | <gtt_table_attrs>}]

See the CREATE TABLE section for the full syntax of CREATE TABLE and descriptions of defining tables, columns and constraints.

RECREATE TABLE creates or recreates a table.If a table with this name already exists, the RECREATE TABLE statement will try to drop it and create a new one.Existing dependencies will prevent the statement from executing.

Example of RECREATE TABLE

Creating or recreating the COUNTRY table.
RECREATE TABLE COUNTRY (
  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL
);
See also

[fblangref50-ddl-tbl-create], [fblangref50-ddl-tbl-drop]