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:

ALTER TABLE t1 DROP 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.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous