It is necessary to change the field type in hundreds of bases, but the primary key is hung on this field, respectively, it must be deleted before the change. However, the names of these keys in different databases are different. In some it is named, in some automatically assigned. In general, do not guess.

Having a little in the system tables, I found out that all primary keys are collected in RDB $ Relation_Constraint, where in the RDB $ Constraint_name field the desired key name is specified, and in the RDB $ relation_name field, the desired table.

I tried to clear the table of primary keys by executing a query (I wrote from memory, could be slightly sealed in the field names:

execute block as declare variable tblname varchar(32); declare variable pkname varchar(32); begin for select rdb$relation_name, rdb$constraint_name from rdb$relation_constraint where trim(rdb$relation_name) = 'MYTABLE' into :tblname, :pkname do begin execute statament 'alter table '|| trim(:tblname) || ' drop constraint ' || trim(:pkname); end end

with this implementation, an error occurs:

 this operation is not defined for system tables. unsuccessful metadata update. ERASE RDB$RELATION_CONSTRAINT failed. action cancelled by trigger (2) to preserve data integrity. Column used in a PRIMARY constraint must be NOT NULL

At the same time, if we take the request that is formed in the line execute statement '...', then it is executed correctly.

    1 answer 1

    Add an additional condition in WHERE AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'

    The rdb$relation_constraint table itself contains all restrictions, incl. and NOT NULL , which, judging by the error, you are trying to delete before the primary key.

    • Yes, you are right, in the reverse order, the deletion is correct. - pincher1519