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:
Modifying Columns in a Table
Existing columns in tables can be modified in a few respects, namely
Use the following syntax pattern for ALTER TABLE:
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.
Any changes to the field definitions may require the indexes to be rebuilt.
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
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:
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:
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:
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:
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:
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.