Generate foreign keys

The other day I had to drop a parent table, which would also remove all its child relationships. I created this script to reverse engineer the foreign key relationships a table would have.

select ‘alter table ‘||rtrim(creator)||’.’||tbname||
‘ add constraint ‘||rtrim(substr(concat(concat(reftbname,’_’),tbname),1,18))||
‘ foreign key (‘||strip(replace(replace(replace(fkcolnames,’ ‘,'<>’),’><‘,”),'<>’,’,’),BOTH,’,’)||’) references ‘
||rtrim(reftbcreator)||’.’||reftbname||
case
when deleterule = ‘R’ then ‘ on delete restrict’
when deleterule = ‘N’ then ‘ on delete set null’
when deleterule = ‘C’ then ‘ on delete cascade’
else ‘ on delete no action’
end
||
case
when updaterule = ‘R’ then ‘ on update restrict’
when updaterule = ‘N’ then ‘ on update set null’
when updaterule = ‘C’ then ‘ on update cascade’
else ‘ on update no action’
end
||
‘;’
from sysibm.sysrels
where reftbname = ‘EMPLOYEE’ or tbname = ‘EMPLOYEE’
with ur;