{"id":440,"date":"2009-09-03T01:41:31","date_gmt":"2009-09-02T19:41:31","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=440"},"modified":"2016-01-07T11:31:11","modified_gmt":"2016-01-07T05:31:11","slug":"generate-foreign-keys","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=440","title":{"rendered":"Generate foreign keys"},"content":{"rendered":"

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.<\/p>\n

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

select ‘alter table ‘||rtrim(creator)||’.’||tbname||
\n‘ add constraint ‘||rtrim(substr(concat(concat(reftbname,’_’),tbname),1,18))||
\n‘ foreign key (‘||strip(replace(replace(replace(fkcolnames,’ ‘,'<>’),’><‘,”),'<>’,’,’),BOTH,’,’)||’) references ‘
\n||rtrim(reftbcreator)||’.’||reftbname||
\ncase
\nwhen deleterule = ‘R’ then ‘ on delete restrict’
\nwhen deleterule = ‘N’ then ‘ on delete set null’
\nwhen deleterule = ‘C’ then ‘ on delete cascade’
\nelse ‘ on delete no action’
\nend
\n||
\ncase
\nwhen updaterule = ‘R’ then ‘ on update restrict’
\nwhen updaterule = ‘N’ then ‘ on update set null’
\nwhen updaterule = ‘C’ then ‘ on update cascade’
\nelse ‘ on update no action’
\nend
\n||
\n‘;’
\nfrom sysibm.sysrels
\nwhere reftbname = ‘EMPLOYEE’ or tbname = ‘EMPLOYEE’
\nwith ur;<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3],"tags":[],"class_list":["post-440","post","type-post","status-publish","format-standard","hentry","category-db2"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=440"}],"version-history":[{"count":1,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/440\/revisions"}],"predecessor-version":[{"id":441,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/440\/revisions\/441"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}