1

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Gxbrielramires
  • 11
  • 3
  • Just wanna update RDB$LENGHT varchar lenght but Firebird doesnt allow UPDATES in RDB$FIELDS system table. Can u help me? – Gxbrielramires Mar 01 '21 at 19:10
  • You really need to read the Firebird 3 release notes. Direct updates to the system tables are [no longer allowed](https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#_changes_to_system_tables) ([2](https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-compat-systables)). You need to use [`ALTER TABLE`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-ddl-tbl-alter) instead. – Mark Rotteveel Mar 01 '21 at 19:14
  • Also see [How change a column type in firebird3](https://stackoverflow.com/questions/48267131/how-change-a-column-type-in-firebird3) – Mark Rotteveel Mar 01 '21 at 19:16

1 Answers1

4

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;
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197