Finding FOREIGN KEY constraints via information_schema

From: Harald Fuchs <use_reply_to(at)protecting(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Finding FOREIGN KEY constraints via information_schema
Date: 2005-04-19 14:43:20
Message-ID: pumzruddyv.fsf@srv.protecting.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm trying to find out which columns of which tables reference which
columns of which tables by querying the information_schema. I found
the referencing columns in key_column_usage and the referenced columns
in constraint_column_usage - fine so far.

Now consider the following:

CREATE TABLE t1 (
id1 INT NOT NULL,
id2 INT NOT NULL,
PRIMARY KEY (id1, id2)
);

CREATE TABLE t2 (
id1 INT NOT NULL,
id2 INT NOT NULL,
CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1 (id1, id2)
);

PostgreSQL groks that, and pg_dump correctly generates

ALTER TABLE ONLY t2
ADD CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1(id1, id2);

My problem is that, while key_column_usage knows the ordinal_position,
constraint_column_usage doesn't. How can I find out that it's really
REFERENCES t1(id1, id2)
and not
REFERENCES t1(id2, id1)
instead?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-04-19 14:53:00 Re: substring and POSIX re's
Previous Message Tom Lane 2005-04-19 14:16:27 Re: Simplified (a-la [G|N]DBM) DB access