How do I find all references from other tables to a specific row?

Question

So I have an address table that is shared among various other tables, such as schools, parks, churches, etc. Schools, parks, etc all foreign key to a row in address.

What I'm wondering is, if I have a specific row in the address table, is there a way to find out which row in which table points to it. So basically just this:

SELECT * FROM schools WHERE address_id = 1

But that would mean I would have to know that the address in row 1 is connected to a school. But what if I don't know that? It could be 1 of 10 other tables...

Answer 1

You're going to have to query each of the other tables.

I would do it as a UNION query:

  SELECT id, "schools" as whichTable from schools where address_id=1
  UNION
  SELECT id, "parks" as whichTable from parks where address_id=1
  UNION
  ...

so that you only have to run one query and get back the results as a single dataset that you work with.

If you have a list of tables (or a table of tables), you could generate the query programmaticly -- that would save you having to update your query when the tables are changed.

Answer 2

What you are looking for - assuming you have an ISO compliant RDBMS - is the INFORMATION_SCHEMA tableset (actually viewset, but hey).

You can find dependent tables like so:

select 
    cons.CONSTRAINT_NAME 'ConstraintName',
    keys.TABLE_NAME 'PKTable',
    keys.COLUMN_NAME 'PKColumn',
    cols.TABLE_NAME 'FKTable',
    cols.COLUMN_NAME 'FKColumn'
from INFORMATION_SCHEMA.REFERENTI cons
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cols on cols.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys on keys.CONSTRAINT_NAME = cons.UNIQUE_CONSTRAINT_NAME

NOTE: This is a very rough, untested query. You get the idea, though.