Re: fields and foreign keys

[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/