Techniques for Changing Table Columns
The examples here describe how to change table columns, including:
• Renaming a column
• Inserting a column
• Changing the data type of a column
There are no direct equivalents for changing columns in VDBA or in a single SQL statement.
Note: Renaming a column in a table or changing its data type does not change anything else that is dependent on the column. You need to update any objects, such as reports, forms, and programs, which are dependent on the old column name or data type. In addition, all of the procedures shown here require that you drop the original table, at which point certain other dependent objects, such as integrities, views, indexes, and grants, are also dropped. You must recreate these objects.
IMPORTANT! We recommend that you back up your tables before changing them. If a problem occurs, you can then restore the original table and repeat the procedure. For additional information, see the chapter “Performing Backup and Recovery.”
Example: Rename a Column
The following example renames two columns, name and addr, to employee and address. The salary column is not renamed.
Using SQL:
1. Create a temporary table with all columns from the original table, renaming the desired columns. For example:
create table temp as
select name as employee, addr as address, salary
from test;
2. Drop the original table:
drop test;
3. Re-create the original table with the columns from the temporary table:
create table test as
select * from temp;
4. Drop the temporary table:
drop temp;
In VDBA, follow these steps to rename such columns:
1. Assuming the table test already exists with columns name, addr, and salary, create a temporary table named temp.
2. Enable Create Table As Select in the Create Table dialog.
3. In the Select Statement edit control, enter the following select statement to rename the desired columns:
select name as employee, addr as address, salary
from test
4. Drop the original table, test.
5. Create a new table named test.
6. Enable Create Table As Select in the Create Table dialog.
7. In the Select Statement edit control, enter:
select * from temp
8. Drop the temporary table, temp.
Be sure to update any objects, such as reports, forms, and programs that are dependent on the old column name, and recreate integrities, views, indexes, grants, and other dependent objects that were destroyed when the original table was dropped.
Example: Insert a Column
When you add a column to an existing table (using the Alter Table dialog in VDBA), the column is placed after the last previously existing column in the table. To insert a new column between existing columns, you must follow a procedure like that for renaming a column.
The following example illustrates inserting a new column, newcol, in the middle of an existing table with previously defined columns.
Using SQL:
1. Create a temporary table containing the existing columns from the original table and the new columns. If the column named “newcol” is to be located in the middle of the table structure, you must list all the columns individually. For example:
create table temp as
select col1, col2, varchar(' ')
as newcol, col3, col4 from test;
2. Drop the original table.
drop test;
3. Rename the temporary table with the name of the original table using a CREATE TABLE AS statement with a subselect statement.
create table test as
select * from temp;
4. Drop the temporary table.
drop temp;
In VDBA, follow these steps to insert a new column, newcol, into table test:
1. Create a temporary table named temp.
2. Enable Create Table As Select in the Create Table dialog.
3. In the Select Statement edit control, enter the following SELECT statement, inserting the new column:
select col1, col2, varchar(' ') as newcol,
col3, col4 from test
4. Drop the original table, test.
5. Create a new table named test.
6. Enable Create Table As Select in the Create Table dialog.
7. In the Select Statement edit control, enter:
select * from temp
8. Drop the temporary table, temp.
Be sure to recreate integrities, views, indexes, grants, and other dependent objects that were destroyed when the original table was dropped in Step 4.
Note: To rearrange the current column order of a table without adding new columns, use this same procedure, selecting the columns in the desired order in Step 3.