Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Locating ( FKs ) References to a Primary Key
Date: Wed, 17 Aug 2005 15:38:40 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CC6B@mail.aveo.aveopharma.com>
Thread-Topic: [SQL] Locating ( FKs ) References to a Primary Key
Thread-Index: AcWjYYPZWxcN0rhEQxeKhKwGIgqW9QAAFIeg
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Roger Motorola" <roger77_lb@yahoo.com>,
	<pgsql-sql@postgresql.org>

I have a couple of views I always add to 'information_schema' to help
with these sorts of things.

Here's the one for foreign keys:

CREATE VIEW information_schema.foreign_key_tables AS SELECT
	n.nspname AS schema,
	cl.relname AS table_name,
	a.attname AS column_name,
	ct.conname AS key_name,
	nf.nspname AS foreign_schema,
	clf.relname AS foreign_table_name,
	af.attname AS foreign_column_name,
	pg_get_constraintdef(ct.oid) AS create_sql

FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class cl ON (a.attrelid =3D cl.oid AND cl.relkind =3D
'r')
JOIN pg_catalog.pg_namespace n ON (n.oid =3D cl.relnamespace)
JOIN pg_catalog.pg_constraint ct ON (a.attrelid =3D ct.conrelid AND
ct.confrelid !=3D 0 AND ct.conkey[1] =3D a.attnum)
JOIN pg_catalog.pg_class clf ON (ct.confrelid =3D clf.oid AND clf.relkind=

=3D 'r')
JOIN pg_catalog.pg_namespace nf ON (nf.oid =3D clf.relnamespace)
JOIN pg_catalog.pg_attribute af ON (af.attrelid =3D ct.confrelid AND
af.attnum =3D ct.confkey[1])
;
GRANT SELECT ON information_schema.foreign_key_tables TO PUBLIC;


Searching the 'foreign_*' fields for your schema/table/column will give
you all the tables that reference it in a foreign key constraint.  I
also provide the SQL used to create the constraint, since the purpose of
this is to drop and then recreate dependencies when reloading a single
table.

Hope that gets you started,
Dmitri

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org=20
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Roger Motorola
> Sent: Wednesday, August 17, 2005 3:07 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Locating ( FKs ) References to a Primary Key
>=20
>=20
> Hi to all,=20
>=20
> Is there any means by which one can get all Foreign Keys=20
> (References) that 'point' to a certain Primary Key for a given table ?
>=20
> For instance, let's consider those three tables:
>=20
> (NOTE: table contents here are not deeply thought of...)
>=20
> // employees table
> create table emp
> (id serial primary key,=20
> first_name varchar not null,=20
> last_name varchar not null,=20
> .....................etc.);
>=20
> // employee address
> create table emp_address
> (emp_id integer references emp (id),=20
> city integer references city (id),=20
> primary key (emp_id, city),=20
> comments varchar not null);
>=20
> // employee categories ()
> create table emp_categories
> (emp_id integer references emp (id),=20
> institution integer references institutions (id),
> unique (emp_id, institution),=20
> category integer references categories (id),=20
> primary key (emp_id, institution, category),=20
> description varchar not null);
>=20
>=20
> So, can we issue a query that gets all references to emp.id ?=20
> which should yield here:
>     emp_address.emp_id=20
> and emp_categories.emp_id
>=20
>=20
> Thanks in advance,=20
> Roger Tannous.
>=20
>=20
> 	=09
> ____________________________________________________
> Start your day with Yahoo! - make it your home page=20
> http://www.yahoo.com/r/hs=20
> =20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>=20
The information transmitted is intended only for the person or entity to =
which it is addressed and may contain confidential and/or privileged mate=
rial. Any review, retransmission, dissemination or other use of, or takin=
g of any action in reliance upon, this information by persons or entities=
 other than the intended recipient is prohibited. If you received this in=
 error, please contact the sender and delete the material from any comput=
er