CODEWITHSUNDEEP
sqlfirebirdfirebird-3.0
Gxbrielramires
Gxbrielramires

Reputation: 21

Change the size of a varchar in the RDB$FIELDS table in Firebird

I am trying to change the varchar size of the CODE field of my FAIXA_VALORES table, however when trying to change it with the following code:

update RDB$FIELDS set
    RDB$FIELD_LENGTH = 50,
    RDB$CHARACTER_LENGTH = 50
    where RDB$FIELD_NAME = 'RDB$14392222'

But it returns me the following error:

UPDATE operation is not allowed for system table RDB$FIELDS.

I've tried with alter table and it doesnt allowed me too. Someone can help me?

Upvotes: 2

Views: 3007

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109000

Since Firebird 3, direct modification of the system tables is no longer allowed (with a very small number of exceptions); see Changes to System Tables and System Tables are Now Read-only. And even before Firebird 3, this practice was not recommended

To change the size of a column, you need to use ALTER TABLE, for example

alter table YOUR_TABLE
  alter column YOUR_COLUMN type varchar(50)

There are a number of restrictions to such a change. For example, it is not possible to alter a column to be shorter than its current definition. So altering a VARCHAR(60) to a VARCHAR(50) is not possible. If you need to do that, then you need to add a new column, copy the contents from old to new (with modifications if necessary), drop the old column and rename the new column:

alter table YOUR_TABLE add NEW_COLUMN varchar(50);
commit;
update YOUR_TABLE set NEW_COLUMN = substring(YOUR_COLUMN from 1 for 50);
commit;
alter table drop YOUR_COLUMN;
alter table alter column NEW_COLUMN to YOUR_COLUMN;
commit;

Upvotes: 5

Related Questions