Thread: fields and foreign keys

fields and foreign keys

From
"mrix"
Date:
Hello!

I'd like to know how can i get list of fields and corresponding foreign
keys (referenced table and field).

Thanks!


Re: fields and foreign keys

From
Michael Fuhr
Date:
On Thu, Jun 23, 2005 at 01:34:28AM -0700, mrix wrote:
>
> I'd like to know how can i get list of fields and corresponding foreign
> keys (referenced table and field).

For individual tables, client interfaces usually have a way to show
the table definition.  In psql, for example, you can use "\d tablename".
If you want to see all foreign key constraints for all tables then
you could query the pg_constraint system catalog.

http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html

You can get an idea of what query to issue by viewing the hidden commands
that psql executes (run "psql -E" or execute "\set ECHO_HIDDEN" and then
execute "\d tablename").  Here's an example:

SELECT conrelid::regclass, conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f';

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: fields and foreign keys

From
"mrix"
Date:
Yeah, thanks!
But I've already tried this approach, and it's not quite i need.
Because as i understand foreign keys are built on indexes, so i get
*index* and corresponding foreign key definition.
Well then i have to find out what filed this index "belongs to"
(assuming 1 field - 1 index)


Re: fields and foreign keys

From
Michael Fuhr
Date:
On Thu, Jun 23, 2005 at 10:11:15PM -0700, mrix wrote:
>
> But I've already tried this approach, and it's not quite i need.
> Because as i understand foreign keys are built on indexes, so i get
> *index* and corresponding foreign key definition.
> Well then i have to find out what filed this index "belongs to"
> (assuming 1 field - 1 index)

The query I posted shows the relation (table) that contains the
foreign key constraint, the constraint name (not an index name),
and the constraint definition, which includes the names of the
referring column(s) and the referred-to table and column(s).  For
example:

CREATE TABLE foo (id integer PRIMARY KEY);
CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo);

SELECT conrelid::regclass, conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f';
 conrelid |    conname     |          pg_get_constraintdef
----------+----------------+----------------------------------------
 bar      | bar_fooid_fkey | FOREIGN KEY (fooid) REFERENCES foo(id)
(1 row)

If this isn't what you're looking for, then please post an example
that shows what you're trying to do.  That is, something like "given
the following table definitions, I'm looking for a query that will
give me this output...."

What problem are you trying to solve?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: fields and foreign keys

From
Michael Fuhr
Date:
[Please copy the mailing list on replies.]

On Fri, Jun 24, 2005 at 05:52:46PM +0300, Marik wrote:
>
> But what i really need is field this constraint belongs to then...
> I'd like to have such result:
> CREATE TABLE foo (id integer PRIMARY KEY);
> CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo);
>
> SELECT <theSQL_goes_here>
>
>  conrelid |    fieldname     |          pg_get_constraintdef
> ----------+----------------+----------------------------------------
>  bar      | fooid | FOREIGN KEY (fooid) REFERENCES foo(id)
>
> or better this:
>
>  conrelid |    fieldname     |          ref_table | ref_field
> ----------+----------------+----------------------------------------
>  bar      | fooid                | foo                   | id

You can get the column names by joining pg_constraint and pg_attribute.

http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html
http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html

In PostgreSQL 7.4 and later you can query the Information Schema.
Here are some of the views that should be useful:

http://www.postgresql.org/docs/8.0/static/infoschema-key-column-usage.html
http://www.postgresql.org/docs/8.0/static/infoschema-constraint-column-usage.html
http://www.postgresql.org/docs/8.0/static/infoschema-referential-constraints.html
http://www.postgresql.org/docs/8.0/static/infoschema-table-constraints.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/