Home / Questions / 440944
Accepted
Franco Torres
Asked: 2022-04-02 07:04:24 +0800 CST 2022-04-02 07:04:24 +0800 CST

How to change the data type of a column in firebird 2.5?

  • 772

Good morning, I'm using firebird 2.5 and I'm trying to change the data type of a column. First I drop the PK constraint, then I change the data type with the following code:

ALTER TABLE CABEZAMOVIMIENTOSEMPLEADOS DROP CONSTRAINT PK_CABEZAMOVIMIENTOSEMPLEADOS;

ALTER TABLE CABEZAMOVIMIENTOSEMPLEADOS
ALTER COLUMN NUMEROMOVIMIENTO
TYPE DOUBLE PRECISION

Then when I try to add the constraint again:

ALTER TABLE CABEZAMOVIMIENTOSEMPLEADOS ADD CONSTRAINT PK_CABEZAMOVIMIENTOSEMPLEADOS PRIMARY KEY (NUMEROMOVIMIENTO);

I get the following error:

can't format message 13:393 -- message file C:\Windows\firebird.msg not found. unsuccessful metadata update. Column: MOVENUMBER not defined as NOT NULL - cannot be used in PRIMARY KEY constraint >definition.

I try to solve it with the following code:

ALTER TABLE CABEZAMOVIMIENTOSEMPLEADOS ALTER COLUMN NUMEROMOVIMIENTO DOUBLE PRECISION NOT NULL
ALTER TABLE CABEZAMOVIMIENTOSEMPLEADOS ADD CONSTRAINT PK_CABEZAMOVIMIENTOSEMPLEADOS PRIMARY KEY (NUMEROMOVIMIENTO);

And now I get this error:

can't format message 13:896 -- message file C:\Windows\firebird.msg not found. Dynamic SQLError. SQL error code = -104. Token unknown - line 1, column 70. DOUBLE.

I don't know what I'm doing wrong, if you can help me I would appreciate it

sql

1 Answers

  1. Best Answer

    Firebird 2.5's SQL dialect does not support changing not nulla column's attribute. You have the following options:

    • The most used, which is the one I like the least, is to change the attribute in the data dictionary (NOTE, this will no longer work in later versions of Firebird).

          update rdb$relation_fields
       set RDB$NULL_FLAG = 0
     where WHERE RDB$FIELD_NAME = 'NUMEROMOVIMIENTO' 
       and RDB$RELATION_NAME = 'CABEZAMOVIMIENTOSEMPLEADOS';

      The engine, in this case, will not perform any verification on the data that already exists in the table, it is your responsibility to ensure that there really is no data with a value nullin the column when making the change.

      It is worth warning you to be careful when touching the data dictionary, as this can easily corrupt your database.

    • Create a new column, which has the attribute not null, and flip the information from the original column into it. The sequence would be something like the following:

      alter table CABEZAMOVIMIENTOSEMPLEADOS 
  add NUMEROMOVIMIENTO_TEMP double precision default 1 not null;
commit;
update CABEZAMOVIMIENTOSEMPLEADOS
   set NUMEROMOVIMIENTO_TEMP = NUMEROMOVIMIENTO; 
commit;
alter table CABEZAMOVIMIENTOSEMPLEADOS alter NUMEROMOVIMIENTO_TEMP drop default;
commit;
alter table CABEZAMOVIMIENTOSEMPLEADOS drop NUMEROMOVIMIENTO;
commit;
alter table CABEZAMOVIMIENTOSEMPLEADOS alter NUMEROMOVIMIENTO_TEMP to NUMEROMOVIMIENTO;
commit;

      Warning : I have written the code in the browser, it may have syntax errors and some commit may not be necessary. I recommend making a backup when you start and make sure you understand the steps before trying.

      Explanation : We create a temporary column, with attribute not nulland a default (otherwise, we could not create the column not nullin a table that already has data. Then we pass the information of the original column to the new column, we eliminate the default value of the new column, we remove the old column and rename the new column.

    • The last option is to create a type constraint checkon the column, to ensure that it cannot have null values:

      alter table CABEZAMOVIMIENTOSEMPLEADO add constraint ck_MiCheck check (NUMEROMOVIMIENTO is not null);
    • 1