What is Altering Tables statement - Firebird

The ALTER TABLE statement is used for changing the structure of a table: adding, changing, or dropping columns or constraints. One statement can encompass several changes, if required. To submit an ALTER TABLE query, you need to be logged in as the table’s creator (owner), SYSDBA or (on POSIX) the Superuser.

Alterations to each table or to its triggers are reference -counted. Any one table can be altered at most 255 times before you must back up and restore the database. However, the reference count is not affected by switching a trigger on and off using ALTER TRIGGER triggername ACTIVE | INACTIVE

Preparing to Use ALTER TABLE

Before modifying or dropping columns or attributes in a table, you need to do three things:

  1. Make sure you have the proper database privileges.
  2. Save the existing data.
  3. Drop any dependency constraints on the column.

Modifying Columns in a Table

Existing columns in tables can be modified in a few respects, namely

  • The name of the column can be changed to another name not already used in the table.
  • The column can be “moved” to a different position in the left-to-right column order.
  • Conversions from non-character to character data are allowed, with some restrictions.

Syntax

Use the following syntax pattern for ALTER TABLE:

Examples

Here we change the name of a column from EMP_NO to EMP_NUM:

Next, the left-to-right position of the column—known as its degree —is moved:

This time, the data type of EMP_NUM is changed from INTEGER to VARCHAR(20):

Restrictions on Altering Data Type

Firebird does not let you alter the data type of a column or domain in a way that might result in data loss.

  • The new column definition must be able to accommodate the existing data. If, for example, the new data type has too few bytes or the data type conversion is not supported, an error is returned and the change cannot proceed.
  • When number types are converted to character types, each number type is subject to a minimum length in bytes, according to type.
  • Conversions from character data to non-character data are not allowed.
  • Columns of BLOB and ARRAY types cannot be converted.

Any changes to the field definitions may require the indexes to be rebuilt.

Dropping Columns

The owner of a table can use ALTER TABLE to drop (remove) a column definition and its data from a table. Dropping a column causes all data stored in it to be lost. The drop takes effect immediately unless another transaction is accessing the table. In this event, the other transaction continues uninterrupted and Firebird postpones the drop until the table is no longer in use.

Before attempting to drop a column, be aware of the dependencies that could prevent the operation from succeeding. It will fail if the column

  • Is part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint
  • Is involved in a CHECK constraint (there may be table-level CHECK constraints on the column in addition to any imposed by its domain)
  • Is used in a view, trigger, or stored procedure

Dependencies must be removed before the column drop can proceed. Columns involved in PRIMARY KEY and UNIQUE constraints cannot be dropped if they are referenced by FOREIGN KEY constraints. In this event, drop the FOREIGN KEY constraint before dropping the PRIMARY KEY or UNIQUE key constraint and column it references. Finally, you can drop the column.

This is the syntax:

For example, the following statement drops the column JOB_GRADE from the EMPLOYEE table:

To drop several columns with a single statement:

Dropping Constraints

A correct sequence must be followed when dropping constraints, since both PRIMARY KEY and CHECK constraints are likely to have dependencies.

UNIQUE KEY and PRIMARY KEY Constraints

When a primary key or unique constraint is to be dropped, it will be necessary first to find and drop any foreign key (FK) constraint that references it. If it is a unique key, the FK declaration actually names the columns of the unique constraint, for example:

If the referenced key is the primary key, the name of the primary key column is optional in FK declarations and is often omitted. For example, looking at the ../samples/ employee.gdb database:

Dropping a foreign key constraint is usually straightforward:

After that, it becomes possible to drop the primary key (PK) constraint on the EMP_NO column of the EMPLOYEE table:

CHECK Constraints

Any CHECK conditions that were added during table definition can be removed without complications. CHECK conditions inherited from a domain are more problematic.

To be free of the domain’s constraints, it will be necessary to perform an ALTER TABLE ALTER COLUMN...TYPE operation to change the column to another data type or domain.

Adding a Column

One or more columns can be added to a table in a single statement, using the ADD clause. Each ADD clause includes a full column definition, which follows the same syntax as column definitions in CREATE TABLE. Multiple ADD clauses are separated with commas.

This is the syntax:

The following statement adds a column, EMP_NO, to the EMPLOYEE table using the EMPNO domain:

Example

Here we add two columns, EMAIL_ID and LEAVE_STATUS, to the EMPLOYEE table:

Including Integrity Constraints

Integrity constraints can be included for columns that you add to the table. For example, a UNIQUE constraint could have been included for the EMAIL_ID column in the previous statement:

or

Adding New Table Constraints

The ADD CONSTRAINT clause can be included to add table-level constraints relating to new or existing columns.

This is the syntax:

Example

For example, to add a UNIQUE constraint to the EMPLOYEE table, you might use this statement:

When ALTER TABLE Is Not Enough

Sometimes, you need to make a change to a column that cannot be achieved with ALTER TABLE. Examples might be where a column that is storing international language items in character set NONE needs to be changed to another character set to correct your design error, or a telephone number, originally defined by someone as an integer, needs to be stored as an 18-character column instead.

In the first case, it is not possible to change the character set of a column, so you need a workaround that both preserves the data and makes it available in the correct character set. In the second case, simply changing the data type of the telephone number column will not work if we already have existing integer data in the column. We want to keep the actual numbers, but we have to convert them to strings. That cannot be done in the current structure, because an integer column cannot store a string.

The workaround entails creating a temporary column in your table, with the correct attributes, and “parking” the data there while you drop and re-create the original column.

  1. Add a temporary column to the table that has a definition with the new attributes you need.
  2. Copy the data from the column to be changed to the temporary column, “massaging” it appropriately (e.g., applying a character set “introducer” to convert the text data to the correct character set or, in our example, casting it appropriately).
  3. After verifying that the data in the temporary column has been changed as planned, drop the old column.
  4. Create a “new” column with the same name as the one you just dropped that has the same attributes as the temporary column.
  5. Copy the massaged data to the newly re-created column.
  6. After verifying that the data in the re-created column is correct, drop the temporary column. If you wish, you can also move the re-created column back into its old position.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics