Is there a way in PG 7.3, given a field, to find out what other tables &
records are linked to it via a foreign key? EG:
create table cities (id serial primary key,
title varchar not null);
insert into cities(title) values ('San Fransisco');
insert into cities(title) values ('Los Angeles');
create table stores (id serial primary key,
city integer not null references cities(id),
title varchar);
insert into stores(city, title) values (1, 'North City');
insert into stores(city, title) values (2, 'Central District');
insert into stores (city, title) values (1, 'Beachfront");
Given the above, and I wanted to know all the tables/records that relate to id
1, San Fransisco, and get a result something like:
table | primary key
stores | 1
stores | 3
Does such functionality exist in PG? Isn't it already doing essentially this
when attempting to delete a record with other records linked to it?
Currently, I do this by attempting to delete a record in a transaction, and
trap the error - it's a terrible way to do this, and sometimes I'm already in
a transaction.
-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978