How to change character column size?

You should use the ALTER TABLE statement. Let's suppose you have a column c1 in table t1, which is of type CHAR(80) and you wish to increase that to 90 characters:

ALTER TABLE t1 ALTER c1 TYPE char(90);

If you wish to reduce the size, or there are other database object that depend on that column, you need to do the following:

1. Create a new temporary column with desired size:

ALTER TABLE t1 ADD c_temp TYPE char(70);

2. Copy data to the new column:

UPDATE t1 SET c_temp = c1;

At this point, you might catch some data that does not fit the new, smaller size.

3. Drop all the dependencies on column c1
4. Drop column c1:


5. Rename the temporary column:

ALTER TABLE t1 ALTER c_temp TO c1;

6. Create all the dependencies back.

Droping and creating all the dependencies can be hard if there are many of them, so it is recommended that you use some administration tool that automate this. For example, in FlameRobin, go to table properties and click on "Generate Rebuild Script" option. There are also some commercial tools that do direct modifications of system tables, which can be dangerous. Please read FAQ #74 for additional info.

Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

All contents are copyright © 2007-2023 unless otherwise stated in the text.

Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

 Installation and setup
 Backup and restore
 Connectivity and API
 Errors and error codes