The Firebird 5.0 Language Reference | ALTER TABLE

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;

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

The DROP CONSTRAINT Clause

The DROP CONSTRAINT Clause

The ALTER [COLUMN] Clause

Renaming a Column: the TO Clause

Changing the Data Type of a Column: the TYPE Clause

Changing the Position of a Column: the POSITION Clause

The DROP DEFAULT and SET DEFAULT Clauses

DROP TABLE