Change delete rule on an existing foreign key in Firebird 3.0 [CORE5397]

[firebird-automations]

Submitted by: Nico Speleers (nico)

I want to change a delete rule to CASCADE but I always get message UPDATE operation is not allowed for system table RDB$Indices. No problem in firebird 2.5.4.

[firebird-automations]

Commented by: @pavel-zotov

ALTER TABLE has no option to alter constraint(s), atleast nowadays ( http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-alter )
One may too this obly by deleting and re-creating FK, like this:

recreate table tmain(
id int primary key
,pid int,
constraint tmain_fk foreign key (pid) references tmain(id) on delete cascade
);

show table tmain;

alter table tmain
drop constraint tmain_fk
,add constraint tmain_fk foreign key (pid) references tmain(id) on delete set null
;

show table tmain;

[firebird-automations]

Commented by: Nico Speleers (nico)

ALTER TABLE T1
DROP CONSTRAINT FK_ LANGUAGEID;

ALTER TABLE T1
ADD CONSTRAINT FK_LANGUAGEID
FOREIGN KEY (LANGUAGEID)
REFERENCES LANGUAGES(ID)
ON DELETE CASCADE;

/* Comment for index (FK_ADDFLDCOL_LANGUAGEID) */
UPDATE RDB$INDICES
SET RDB$DESCRIPTION = 'Link to a language'
WHERE RDB$INDEX_NAME = 'FK_LANGUAGEID';

Actually, I can change the delete rule to CASCADE if there is no comment for that index. We work with FB Maestro and if I change a delete rule of a foreign key with comment I get an error-message on the update of the comment.
So if a want to change my comment on a FK I get the same error-message.

[firebird-automations]

Commented by: @pavel-zotov

===
UPDATE RDB$INDICES
SET RDB$DESCRIPTION ...

Anyone who wants to migrate to FB 3.0 must forget about direct modifications of rdb$ tables. It's prohibited in FB 3.0.
Remove (temply, with storing to some variable) any comments from index and then change FK rule, after this restore comment for this index - IMO this is the only solution.

[firebird-automations]

Commented by: Nico Speleers (nico)

Could you give me an example how to restore comment on an index ?

[firebird-automations]

Commented by: @pavel-zotov

// IMO this question is for FB surrort mailing list, not for tracker.

OK, try this (be sure that you have no file 'tmp.sql' in working dir - it will be replaced):

shell del .\tmp.sql 2>nul;
set list on;

recreate table tmain(
id int primary key,
pid int,
constraint tmain_fk
foreign key(pid) references tmain(id) on delete cascade
using index tmain_fk_idx
);
comment on index tmain_fk_idx is 'foo';
commit;

show table tmain;

select cast(rdb$description as varchar(8192)) as previous_index_descr
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx');

out .\tmp.sql;
select 'comment on index tmain_fk_idx is ''' || cast(rdb$description as varchar(8192)) ||'''; commit;' as " "
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx');
out;

comment on index tmain_fk_idx is null;
commit;

alter table tmain
drop constraint tmain_fk
,add constraint tmain_fk foreign key(pid) references tmain(id) on delete set null
using index tmain_fk_idx
;

in .\tmp.sql;

show table tmain;

select cast(rdb$description as varchar(8192)) as restored_index_descr
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx');

shell del .\tmp.sql 2>nul;

Another solution uses rdb$set/get_context functions but it can be applied only when length of comment text <= 255:

set bail on;
set list on;

recreate table tmain(
id int primary key,
pid int,
constraint tmain_fk
foreign key(pid) references tmain(id) on delete cascade
using index tmain_fk_idx
);
comment on index tmain_fk_idx is 'foo';
commit;

set term ^;
execute block as
begin
rdb$set_context( 'USER_SESSION','INDEX_DESCR',
(
select cast(rdb$description as varchar(255)) as previous_index_descr
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx')
)
);
end
^
set term ;^
commit;

show table tmain;

select cast(rdb$description as varchar(255)) as previous_index_descr
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx');

comment on index tmain_fk_idx is null;
commit;

alter table tmain
drop constraint tmain_fk
,add constraint tmain_fk foreign key(pid) references tmain(id) on delete set null
using index tmain_fk_idx
;
commit;

set term ^;
execute block as
begin
execute statement 'comment on index tmain_fk_idx is ''' || rdb$get_context('USER_SESSION','INDEX_DESCR') ||'''';
end
^
set term ;^
commit;

show table tmain;

select cast(rdb$description as varchar(255)) as restored_index_descr
from rdb$indices
where rdb$index_name=upper('tmain_fk_idx');

[firebird-automations]

Commented by: Nico Speleers (nico)

What's the reason that I can change comment of an index and comment of a foreign key cannot be changed ?