Date: Sun, 5 Sep 2004 16:29:58 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Troels Arvin <troels@arvin.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: INFORMATION_SCHEMA and foreign keys
In-Reply-To: <pan.2004.09.05.22.56.56.521040@arvin.dk>
Message-ID: <20040905162116.K38304@megazone.bigpanda.com>
References: <pan.2004.09.05.22.56.56.521040@arvin.dk>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Mon, 6 Sep 2004, Troels Arvin wrote:

> The query returns double the numer of rows, compared to what I wanted. The
> problem seems to stem from PostgreSQL's naming of constraints without
> explicit name: They seem to be named $1, $2, etc, and the default names
> are reused.
[...]
> Note, again, that the CONSTRAINT_NAMEs are not unique, although they
> affect two different tables. Hence, there doesn't seem to be a way to map
> a specifict referential constraint to a specific primary/unique constraint
> in the table being referred to.
>
> Is my only way forward to drop using the INFORMATION_SCHEMA and work with
> the pg_catalog if I want to determine which columns are being referred to
> in a (set of) foreign key column(s)?

Possibly, yes.  You'd be better off if you named your constraints rather
than letting the system name them for you, but in general you can't rely
on someone else doing that.  This is a side effect of allowing table
unique constraint names rather than schema unique constraint names (as a
side note you would need to constrain schema in those joins even if we
did schema unique names).