What tables reference a given table with a FK constraint?
Jacob Salomon asked how to find all tables with foreign keys referencing a given table, and the columns involved, for a purge program. Mike Walker pointed to sysreferences and gave queries; Doug Lawry and Art Kagel shared their own, with Art's myschema query. Art explained mapping partN index columns to syscolumns, with negative values meaning descending. Jacob built a view of index parts with column names.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Y'all.
I recall that decades ago (I think before 2005) I posted this question:
Given a table xyz, how do I find all the tables that have a foreign key constraint REFERENCES xyz and via what columns. Essentially, what I get in dbaccess when I choose Info -> (Table) -> cOnstraints -> Reference -> referenceD. I do recall at the time someone responded that I had to use some undocumented system catalog to find this.
I am writing a purge program and some of the rows could not be dropped because there is a detail row still referencing the row I'm trying to drop. A former employee here solved this by writing a template script and producing a slightly different script to purge the table at leach layer of the dependency chain, bottom up, because there are so many layers of such dependencies. I did that myself at a previous job, coding a different routine for each layer of a specific set of files.
But I was trying to write my purge program to be more general. If possible. Otherwise I will be writing boiler-plate programs or utilities to purge each layer in the correct bottoms-up order. Not a task for a lazybones like me. .
Ideas, anyone? Hasn't someone invented this wheel already? (Sadly, the script I wrote then seem to have stayed behind when I left B&N.)
Thanks much for help here.
-- Jacob S
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
sysreferences is probably what you are looking for. -- Shows the PK of the given table select tabname[1,30] table, c.constrname[1,30] constraint_name from systables t, sysconstraints c where t.tabname = "<tablename>" and c.constrtype = "P" and c.tabid = t.tabid; -- Shows the referencing tables & FK constraints select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name from systables t, sysreferences r, sysconstraints c, systables t2 where r.ptabid = t.tabid and t.tabname = "<tablename>" and r.constrid = c.constrid and c.tabid = t2.tabid; ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
Mike, I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end. I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table. And recursively, because I see as many as 4 layers of dependency in the one of the tables I'm going after. If it gets too complicated I may have to give up in the face of diminishing returns. No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!) So let's continue along this path and see if it gets too hairy. Thanks again for that GREAT solution. I have, of course, saved it. I will want to wrap in both Perl and shell scripts to parametrize it. -- Jacob S ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------
Hi Jacob. Here's mine: https://www.oninitgroup.com/faq-items/foreign-key-dependencies Regards, Doug This e-mail (and any files or other attachments transmitted with it) is intended solely for the attention of the addressee(s). Unauthorised use, disclosure, storage, copying or distribution of any part of this e-mail is not permitted. If you are not the intended recipient please destroy the e-mail, remove any copies from your system and inform the sender immediately by return. Oninit Consulting Ltd does not accept any liability or responsibility for any damage caused by any malware transmitted by this e-mail or for changes made to this e-mail after it was sent. All communications to or from Oninit Consulting Ltd may be automatically logged, monitored and/or recorded in order to secure the effective operation of the system and for other lawful purposes. The views or opinions contained within this e-mail may not necessarily reflect those of Oninit Consulting Ltd.
Jacob:
Here's mine. This is how myschema does it with the -F option:
To get the tables referencing a named table:
SELECT st.tabname, st.owner, rt.tabname, rt.owner, sr.primary, sr.ptabid,
sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner,
si.idxname, si.tabid, si.part1, si.part2, si.part3,
si.part4, si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12, si.part13,si.part14, si.part15, si.part16, rc.tabid, os.state, os2.state
FROM "informix".systables st, "informix".sysconstraints sc,
"informix".sysindexes si, "informix".sysreferences sr,
"informix".systables rt, "informix".sysconstraints rc,
"informix".sysobjstate os, "informix".sysobjstate os2
WHERE st.tabid = sc.tabid
AND st.tabtype != 'Q'
AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*'
AND rt.tabid = sr.ptabid
AND sc.constrid = sr.constrid
AND sc.tabid = si.tabid
AND sc.idxname = si.idxname
AND sc.constrtype = 'R'
AND st.tabname MATCHES ?
AND os.tabid = st.tabid AND os.name = sc.constrname AND os.objtype = 'C'
AND os2.tabid = st.tabid AND os2.name = si.idxname AND os2.objtype = 'I'
AND sr.primary = rc.constrid
AND rt.tabname = ?;
The columns listed are from the referencing table. To get the referenced table's joining columns change:
AND sc.tabid = si.tabid
to
AND sr.tabid = si.tabid
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Thanks MUCH Doug and Art.
Since this will be going into a Perl utility, a cut-down version of Art's version, however intimidating, might be more suited to my purpose.
However, Art, without comments I am a bit confused. I see that both st and rt are aliases for systables. One is being use for the referenced table and the other for the referncee table. Based on my knowledge that the table I'm working with has 7 referencing tables, I see that rt.tabname is the name of the referenced table..
In order to run this in dbaccess I would need to provide valued for the "?" placeholders. For the second ? placeholder I substituted my table. But clause "AND st.tabname MATCHES ?" i had to comment out; I could not think of a matching pattern, nor do I see a need for it in my situation.
And, after cutting down the number of columns retrieved, I see the column numbers but not hte more-useful (for me) column names. I will play with that a bit and post simpler (again, for my purposes) version, but with column names. As it happens, I don't believe ANY of out PK/FK constraints in my servers are more than 3 columns so I commented those out of consideration.
This is GREAT, Art.
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
Jacob: So, to get the column names, you have to map the colno's retrieved from the "partN"'s to the table's syscolumns records. I don't do that in the same query in myschema mainly because that would take an already complex 8 table join up to 24 tables (having to join syscolumns up to 16 times as outer joins). Myschema looks up the non-zero "partN" numbers individually. Note that if a particular "partN" in the target index is negative then it is a DECending key element and the actual colno is -1 * partN. Fortunately you do not have to worry about any functional key elements in a foreign key constraint index or its referenced primary or unique key constraint index. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Art,
You reply made recall something I did nearly 30 years ago, at Garpac: I actually created a view that would all parts of every index with their column names. Here is the first 3 parts; I have determined that in my environment there are indexes with as many as 9 columns. I commented out columns we don't need to see but left them in as comments for reference. Here goes:
select t.tabname,
i.idxname,
--t.tabid,
1 ixpartn,
i.part1 ixcolno,
--c.colno,c.colname
From systables t, sysindexes i, syscolumns c
where i.tabid = t.tabid
and c.tabid = t.tabid
and c.colno = abs(i.part1)
-- and t.tabname = "?" -- Finally mention name of relevant table!
union
select t.tabname,
i.idxname,
--t.tabid,
2 ixpartn,
i.part2 ixcolno,
--c.colno,c.colname
From systables t, sysindexes i, syscolumns c
where i.tabid = t.tabid
and c.tabid = t.tabid
and c.colno = abs(i.part2)
and i.part2 > 0 -- All subsequent index parts need this check
-- and t.tabname = "?" -- Finally mention name of relevant table!
union
select t.tabname,
i.idxname,
--t.tabid,
3 ixpartn,
i.part3 ixcolno,
--c.colno,c.colname
From systables t, sysindexes i, syscolumns c
where i.tabid = t.tabid
and c.tabid = t.tabid
and c.colno = abs(i.part3)
and i.part3 > 0 -- All subsequent index parts need this check
-- and t.tabname = "?" -- Finally mention name of relevant table!
-- And so on, far all remaining parts of all indexes.
order by tabname, idxname, ixpartn
;
And so on, I don't blame Art for chickening out of this. But as a view, it would have only5 columns. Nobody here wants me creating exotic views, however, a not difficult item to add to your excellent query.
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------