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.