The Firebird 5.0 Language Reference | ALTER TABLE | Renaming A Column: The TO Clause

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;

`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
);

Renaming A Column: The TO Clause

Renaming a Column: the TO Clause

Changing the Data Type of a Column: the TYPE Clause

docnext count = 7

Changing the Position of a Column: the POSITION Clause

The DROP DEFAULT and SET DEFAULT Clauses

DROP TABLE