Обсуждение: Tracking back foreign keys?

In one of my apps, I have an "images manager" that's become unmanageable. I'd
like to be able to easily track key dependencies.

Let's say I have a set of tables like this:

create table Customers  (
id serial unique not null primary key,
name varchar not null,
address varchar not null
image integer references images(id)
);

create table Vendors (
id serial unique not null primary key,
name varchar not null,
vendor_account varchar,
picture integer references images(id)
);

create table Images (
id serial unique not null,
filename varchar not null,
mime varchar not null
);

I know that in the images table I have lots of cruft, "dead wood", but when I
delete from images, is there a "nice" way of finding out what dependencies
there are?

Something like

Select pg_table.name from pg_table where pg_field references images.id

?

How else do I put it? The output I'd like would be something like
images.id / tablename / table.primary key
11 / Vendors / 14
12 / Customers / 9

Can this be done?

-Ben

--
"I kept looking around for somebody to solve the problem.
Then I realized I am somebody"
   -Anonymous

On Sun, Dec 26, 2004 at 09:43:59 -0800,
  Benjamin Smith <ben@charterworks.net> wrote:
>
> Something like
>
> Select pg_table.name from pg_table where pg_field references images.id
>
> ?
>
> How else do I put it? The output I'd like would be something like
> images.id / tablename / table.primary key
> 11 / Vendors / 14
> 12 / Customers / 9
>
> Can this be done?

You can use joins to get this information. To find orphaned images you
can use outer joins. To combine information from several tables in one
query you can union the queries for each table.