Re: Locating ( FKs ) References to a Primary Key - Mailing list pgsql-sql

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 SELECTn.nspname AS schema,cl.relname AS table_name,a.attname AS
column_name,ct.connameAS 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 = cl.oid AND cl.relkind =
'r')
JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind
= 'r')
JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
af.attnum = 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
> [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
>
>
> Hi to all,
>
> Is there any means by which one can get all Foreign Keys
> (References) that 'point' to a certain Primary Key for a given table ?
>
> For instance, let's consider those three tables:
>
> (NOTE: table contents here are not deeply thought of...)
>
> // employees table
> create table emp
> (id serial primary key,
> first_name varchar not null,
> last_name varchar not null,
> .....................etc.);
>
> // employee address
> create table emp_address
> (emp_id integer references emp (id),
> city integer references city (id),
> primary key (emp_id, city),
> comments varchar not null);
>
> // employee categories ()
> create table emp_categories
> (emp_id integer references emp (id),
> institution integer references institutions (id),
> unique (emp_id, institution),
> category integer references categories (id),
> primary key (emp_id, institution, category),
> description varchar not null);
>
>
> So, can we issue a query that gets all references to emp.id ?
> which should yield here:
>     emp_address.emp_id
> and emp_categories.emp_id
>
>
> Thanks in advance,
> Roger Tannous.
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any
actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you
receivedthis in error, please contact the sender and delete the material from any computer