ALTER TABLE in BBj SQL

Description

In BBj 7.0 and higher, the ALTER TABLE statement is a powerful SQL command that a developer can use to make changes to the structure of their database tables, directly from any BBj, ODBC, or JDBC application. Before the introduction of ALTER TABLE to the BBj SQL engine, developers were required to make modifications to their tables by means of a GUI application or custom program that modified the dictionary files directly. Using a GUI interface meant that changes had to be done manually, while the custom program method left open the possibility for database corruption if the developer did not change something correctly.

 

Warning

Modifying a table using ALTER TABLE makes changes to both the data dictionary definition for the table as well as the data file containing the records. For some BBx applications, this can be problematic because the application code is dependent on a specific file structure, including record size, template layout, key structure and order, etc. Be sure that you understand your data file structure and the code that will access the data file directly (if there is such code) before using ALTER TABLE.

Overview

ALTER TABLE gives the developer the power to modify several characteristics of tables in their database. These are:

  • Adding a new column

  • Changing a column name

  • Changing a column data type and/or size

  • Dropping a column

  • Dropping a constraint (ESQL only)

All of these features require BBj to rewrite the data file in order to make changes the record size or layout with the exception of changing a column name. Depending on the size of the data file, this process can take some time.

Adding a New Column

When BBj adds a new column to a table, it places the column at the end of the record. It increases the size of the record by the amount required for the new column based on its data type and size specification. The following example adds a column called SKU to the ITEM table in the Chile Company database:

ALTER TABLE item ADD COLUMN sku VARCHAR(30)

Changing a Column Data Type and/or Size

Changing the data type or size of a column changes the amount of data that can be stored in the column and/or the type of data. This will usually change the record size of the data file. The following example changes the size of the LAST_NAME column on the CUSTOMER table in Company database to a length of 55 and the type to VARCHAR:

ALTER TABLE customer ALTER COLUMN last_name VARCHAR(55)

Changing a Column Name

Renaming a column changes the name of the column from one name to another. The following example renames the WT_UNIT column in the Chile Company database to WEIGHT_UNIT:

ALTER TABLE item RENAME COLUMN wt_unit TO weight_unit

Dropping a Column

When BBj drops a column from a table, it decreases the size of the record by the amount specified in that column's definition based on its data type and size specification. The following example drops a column called SKU from the ITEM table in the Chile Company database:

ALTER TABLE item DROP COLUMN sku

Dropping a Column Constraint – ESQL Only

ESQL tables are the only type of table that supports constraints such as not null, foreign key, etc. The following example drops a column constraint called MY_TABLE_FK from the MY_TABLE table:

ALTER TABLEmy_tableDROP CONSTRAINT my_table_fk