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?
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 |