Take the 2-minute tour ×

This probably is a bit complicated but is there anyway or already a script out there that could show you all foreign key constraints tied to a single table row.

What I mean by this is say you have the following DB structure:

column a
column b

column c
column d (foreign key constraint to 1.a)

column e 
column f (foreign key constraint to 2.c)

column g (foreign key constraint to 3.e)
column h

Then, you have 2 rows in Table 1. One of the rows is constrained through table 2, then further to table 3, BUT not further to table 4 (IDs tied throughout tables 1-3).

I would like to simply query one of the rows in Table 1 and have it tell me that for that row there are ties that go to Table 2, and then those rows have ties to Table 3. Using this 'query' on the second row in Table 1 would simply just return nothing as there are no foreign keys that are tying that row down.

Something like this would be immensely useful when it comes to tracking down what tables/rows are currently using a particular starting row.


share|improve this question
And why don't you show us some sensible table and column names? –  ypercube Jan 8 '13 at 17:52
I don't really get how this is a row level task. Aren't you just looking for a graph of the foreign key constraints and how they connect together? –  mattdodge Jan 8 '13 at 17:54
Well, yes, you could just pull recursively all the foreign keys to that table but ultimately there may be several hundred rows in the table that don't end up having any foreign keys on them. Meaning if you just did a "DELETE FROM TABLE1 WHERE a = 1", it would delete without a foreign key error. But I want all the ones where you do get a foreign key error if you were to delete it, and so on and so forth recursively down all the foreign keys. –  copystart Jan 8 '13 at 18:04

1 Answer 1

I think what you're looking for can be accomplished by:

SELECT a, t2=COUNT(d), t3 = COUNT(f), t4 = COUNT(g)
FROM [1] LEFT JOIN [2] ON 1.a=2.d
    LEFT JOIN [3] ON 2.c = 3.f 
    LEFT JOIN [4] ON 4.g = 3.e
share|improve this answer
Hm, yes, something simple might work like this. Now I just have to see about dynamically creating the select by querying for all the foreign keys recursively through all the tables. –  copystart Jan 8 '13 at 18:18

Your Answer


