IBM DB2 - The Ultimate Database for Cloud, Analytics & Mobile
Please, can you somebody help me. I need to find a foreign keys and it's parent keys(primary keys) and its name.
For example, foreign_key1 references primary_key1
there is a query
SELECT A.CREATOR||'.'||A.TBNAME||'.'||B.COLNAME AS FOREIGN_KEY, A.RELNAME, A.REFTBNAME
FROM SYSIBM.SYSRELS A,
WHERE A.CREATOR = B.CREATOR
AND B.CREATOR in (SCHEMA)
AND A.TBNAME = B.TBNAME
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLSEQ
And i need also name of the primary key, please
Primary key exist via keyseq in sysibm.syscolumns
with the following query you can perhaps find other informations like colonne of the primary key and referenced columns in the children tables
WITH BASE AS
WHERE DBNAME = ?),
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME)
ORDER BY CO.KEYSEQ)) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS "COLUMNS"
FROM SYSIBM.SYSCOLUMNS CO
INNER JOIN BASE BA ON CO.TBCREATOR = BA.CREATOR
AND CO.TBNAME = BA.NAME
WHERE KEYSEQ > 0
GROUP BY CO.TBCREATOR,
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.COLNAME)
ORDER BY CO.colSEQ)) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS "COLUMNS"
FROM SYSIBM.SYSFOREIGNKEYS CO
INNER JOIN BASE BA ON CO.CREATOR = BA.CREATOR
AND CO.tbNAME = BA.NAME
GROUP BY CO.CREATOR,
SELECT TRIM(RE.REFTBCREATOR)||'.'||TRIM(RE.REFTBNAME) AS PARENT_TABLE,
KP.COLUMNS AS PARENT_COLUMNS,
TRIM(RE.CREATOR)||'.'||TRIM(RE.TBNAME) AS CHILDREN_TABLE,
Kc.COLUMNS AS Children_COLUMNS,
WHEN DELETERULE = 'C' THEN 'Cascade'
WHEN DELETERULE = 'R' THEN 'Restrict'
WHEN DELETERULE = 'N' THEN 'Set Null'
ELSE 'No Action'
END AS DELETERULE
FROM SYSIBM.SYSRELS AS RE
INNER JOIN BASE AS BA ON RE.CREATOR = BA.CREATOR
AND RE.TBNAME = BA.NAME
INNER JOIN KEY_PAR AS KP ON KP.TBCREATOR = RE.REFTBCREATOR
AND KP.TBNAME = RE.REFTBNAME
INNER JOIN KEY_CHI AS KC ON KC.CREATOR = RE.CREATOR
AND KC.TBNAME = RE.TBNAME
ORDER BY REFTBCREATOR,
The columns that are part of the PK can be found in the SYSIBM.SYSCOLUMNS table (in the column KEYSEQ), the name of the primary key can be found in SYSIBM.SYSTABCONST (in the column CONSTNAME)
The query can look like this :
SELECT SUBSTR(PK.CONSTNAME, 1, 18) AS PK_NAME,
SUBSTR(FK.RELNAME, 1, 18) AS FK_NAME,
CASE WHEN REL.DELETERULE = 'A' THEN 'NO ACTION'
WHEN REL.DELETERULE = 'C' THEN 'CASCADE'
WHEN REL.DELETERULE = 'N' THEN 'SET NULL'
WHEN REL.DELETERULE = 'R' THEN 'RESTRICT'
END AS DELETERULE,
SUBSTR(FK.CREATOR, 1, 8) AS FK_CREATOR_TAB,
SUBSTR(REL.REFTBCREATOR, 1, 8) AS PAR_CREATOR_TAB,
SUBSTR(FK.TBNAME, 1, 18) AS FK_TABNAME,
SUBSTR(REL.REFTBNAME, 1, 18) AS PAR_TABNAME,
SUBSTR(FK.COLNAME, 1, 18) AS FK_COLNAME,
SUBSTR(COL.NAME, 1, 18) AS PK_COLNAME,
COLSEQ AS FK_COLSEQ
FROM SYSIBM.SYSFOREIGNKEYS FK,
WHERE FK.CREATOR = 'QQ'
AND FK.RELNAME = REL.RELNAME
AND REL.REFTBCREATOR = COL.TBCREATOR
AND REL.REFTBNAME = COL.TBNAME
AND FK.COLSEQ = COL.KEYSEQ
AND COL.TBCREATOR = PK.TBCREATOR
AND COL.TBNAME = PK.TBNAME
AND PK.TYPE = 'P'
--ORDER BY FOREIGN KEY NAME
--ORDER BY 2, 10
--ORDER BY DEPENDENT TABLE, FOREIGN KEY NAME
--ORDER BY 6, 4, 2, 10
-- ORDER BY PARENT TABLE, DEPENDENT TABLE, FOREIGN KEY NAME
ORDER BY 7, 5, 6, 4, 2, 10
RI parent key does not have to be a primary key, it may be a unique key as well. Parent key = primary key assumption will cater for most (usual), but not all cases. E.g. you may notice it would miss some catalog RI.
If you'd like to build on your join solution, you could add SYSINDEXES and SYSKEYS in which may be more universal and work with both Primary and Unique constrains in parents. Get the name of either primary index (UNIQUERULE='P') or unique index supporting unique constraint in RI (UNIQUERULE='R') and matching corresponding SYSRELS columns. Then get columns of that IX. You may still find this missing some RI where for some reason indexes supporting primary keys are not marked as 'P'. I'm not sure why that's the case, possibly some historical reason or tables created a long time ago. There may even be some of it that on your catalog. So verify your results against plain SYSFOREIGNKEYS.
I've put together an example below with some comments. It should perform reasonably and get the results with a caveat as described above. But it's not something I use or tested, I've just put it together now as an example, so test it before you trust it :)
Also if you're looking for performance, more formatting/functionality and arguably better readability, check out Recursive CTEs. They are very nice and can be very quick. You can find great examples to get you started in IDUG archives e.g. "Recursive SQL – Unleash the Power!" by Suresh Sane.
SELECT F.RELNAME c_tab
, F.TBNAME c_tab
, F.COLNAME c_col
, F.COLSEQ c_col_seq
, R.REFTBNAME p_tab
, R.IXNAME p_index_unique
, I.name p_index_pk
, K.colname p_col
FROM SYSIBM.SYSFOREIGNKEYS F
, SYSIBM.SYSRELS R
, SYSIBM.SYSindexes I
, SYSIBM.SYSKEYS K
-- Pre-join filters
WHERE F.CREATOR = 'SYSIBM' -- <f> FILTER
-- join predicates
AND f.creator = r.creator -- <f>+<r> join
AND f.tbname = r.tbname
AND f.relname = r.relname
-- So which IX is supporting parent key?
AND ( i.tbname = r.reftbname -- <r>+<i> join
and i.creator = r.reftbcreator
and ( ( i.uniquerule = 'P' -- parent_key = pri
and R.IXNAME = '' -- cartesian warning
or ( i.uniquerule = 'R' -- parent_key = unique
and i.name = R.IXNAME
and i.creator = R.IXOWNER
-- You'll get your key cols from syskeys.
AND i.name = k.ixname -- <i>+<k> join
AND i.creator = k.ixcreator
-- Dont end up with a cartesian product on <i>+<k>
AND f.colseq = k.colseq
order by F.relname, F.COLSEQ
* Sorry about code formatting. This WYSIWYG won't win any prizes for that ;)
Yes very good. Could include extra Stage 1 join predicate:
AND I.UNIQUERULE IN ('P','R')
I like as many Boolean term Stage 1 predicates as possible.
I am surprised that the original question did not ask for the Foreign Key Indexes or lack of support for Foreign Key by an Index. All my FK queries are more oriented to that. The index used for the parent key seems less important, although there certainly has to be one.