At 08:22 AM 8/08/2009, you wrote:

>Hi, I have a doubt (maybe this was already discussed)
>
>I create this simple table in FB 2.1
>
>CREATE TABLE "Configuracion" (
> "Item" VARCHAR(32) NOT NULL,
> "ValorTexto" VARCHAR(120) NOT NULL,
> "ValorReal" DOUBLE PRECISION NOT NULL,
> "ValorEntero" INTEGER NOT NULL
>);
>
>ALTER TABLE "Configuracion"
> ADD PRIMARY KEY ("Item");
>
>when I want to insert some records I notice that one has a longer text for field "Item" so
>I decided to change the column size from VARCHAR(32) to VARCHAR(64) but the following
>error arises:
>
>SQL Error: unsuccessful metadata update MODIFY RDB$RELATION_FIELDS failed action
>cancelled by trigger (1) to preserve data integrity Cannot update index segment used by an
>Integrity Constraint. Error Code: -607. This operation is not defined for system tables.
>The SQL:
>
>ALTER TABLE "Configuracion"
> ALTER COLUMN "Item" TYPE VARCHAR(64);
>
>The table isn't has no relationships with any other and I imagine that the "constraint" is
>the primary index. Is there a way to modify table columns without errors because, for
>e.g., converting an INTEGER column to DOUBLE PRECISION gives errors too.
>
>To resume, it is almost impossible to make changes to columns, at least, via alter table.
>How can I circumvent this?

You don't have to "circumvent" it. Provided the data type change is allowable, simply drop the constraint (PK in this case), commit that, then apply the data type change.

Be aware that using the same identifier ("Item") for both a column and a constraint is not good practice. An identifier such as "PK_Item" would be useful here.

In this case, be careful to do

ALTER TABLE "Configuracion"
DROP CONSTRAINT "Item"

In doing *any* of these changes, be certain that you are logged in as SYSDBA or the owner of the table AND that no other users are logged in.

Altering the data type of a column from INTEGER to DOUBLE PRECISION is not allowed, since the engine stores and operates on these two data types in quite different ways.

./heLen