Subject Re: [firebird-support] Change field type
Author Helen Borrie
At 10:02 PM 7/09/2006, you wrote:
>FB 1.5.3
>I made a mistake when I originally designed a table column and
>specified "Smallint" rather than "Integer".
>Now its coming back to haunt me and I have to change it to Integer.
>Unfortunately I have a bunch of dependent objects so the only way I
>can think of to correct the column type is to add another temporary
>column of the correct type, change all the dependent objects to the
>new temp field, alter the column type of the old field, change all the
>dependent object back to the original field.

You'll come to grief doing it this way around, picking here, picking there.

You'll need to do drop all of the dependencies before you begin (just
the dependencies, not the objects themselves, except for PSQL
modules...and make certain you have the up-to-date sources for those
stored somewhere safe.) It's pretty likely you're going to need to
repair them as well, since any references to that smallint column
are hard-wired in the compiled modules.

create the temp column
pump in the data from the existing column
verify the data in the new column
drop the old column
create a new column using the old name
pump in data from tempcolumn and verify
drop temp column

After that, go back and recreate the constraints, triggers and SPs.

>Is there some easier way to do this?

Not that I know of.