how to find foreign key details (column, that is)
Hello all,
suppose I know that there are several tables with foreign
keys pointing to
demographics.identity.pk
With the help of pg_constraint I can get a list of *tables*
which hold those foreign keys (schema = demographics, tbl = identity,
col = pk):
select
%(schema)s as referenced_schema,
%(tbl)s as referenced_table,
%(col)s as referenced_column,
conrelid::regclass as foreign_table,
confkey as referenced_column_list
from
pg_constraint
where
contype = 'f'
and
confrelid = (
select oid from pg_class where relname = %(tbl)s and relnamespace = (
select oid from pg_namespace where nspname = %(schema)s
)
) and
(
select attnum
from pg_attribute
where
attrelid = (select oid from pg_class where relname = %(tbl)s and relnamespace = (
select oid from pg_namespace where nspname = %(schema)s
))
and
attname = %(col)s
) = any(confkey)
I cannot, however, for the life of it find out how to learn
the *column* the foreign key is on. IOW I can find out that
clinical.allergy
has a foreign key to
demographics.identity.pk
but I cannot find out that the column representing the
foreign key is
clinical.allergy.fk_identity
How can I go about this ?
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
I cannot, however, for the life of it find out how to learn
the *column* the foreign key is on.
pg_constraint.conkey has the referencing columns' numbers.
regards, tom lane
On Sun, Dec 14, 2008 at 02:37:51PM -0500, Tom Lane wrote:
Subject: Re: [GENERAL] how to find foreign key details (column, that is)
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
I cannot, however, for the life of it find out how to learn
the *column* the foreign key is on.pg_constraint.conkey has the referencing columns' numbers.
Tom, thanks, as usual awesome support on the 3rd Sunday in
Advent ...
This is what my 8.3 manual says:
conkey │ int2[] │ pg_attribute.attnum │ If a table constraint, list of columns which the constraint constrains │
From that I wouldn't have figured it'd apply to foreign keys
as well. So I assume it is fair to say that "foreign keys
are one type of table constraint", right ?
Thanks again,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
This is what my 8.3 manual says:
conkey │ int2[] │ pg_attribute.attnum │ If a table constraint, list of columns which the constraint constrains │
From that I wouldn't have figured it'd apply to foreign keys
as well. So I assume it is fair to say that "foreign keys
are one type of table constraint", right ?
Right. I think what the comment is actually trying to point out is that
conkey isn't relevant to domain constraints, which also appear in
pg_constraint.
regards, tom lane
Tom Lane wrote:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
This is what my 8.3 manual says:
conkey ��� int2[] ��� pg_attribute.attnum ��� If a table constraint, list of columns which the constraint constrains ���
From that I wouldn't have figured it'd apply to foreign keys
as well. So I assume it is fair to say that "foreign keys
are one type of table constraint", right ?Right. I think what the comment is actually trying to point out is that
conkey isn't relevant to domain constraints, which also appear in
pg_constraint.
Can someone come up with better documention wording for conkey? I
can't:
http://developer.postgresql.org/pgdocs/postgres/catalog-pg-constraint.html
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Wed, Jan 21, 2009 at 01:49:44PM -0500, Bruce Momjian wrote:
Tom Lane wrote:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
This is what my 8.3 manual says:
conkey │ int2[] │ pg_attribute.attnum │ If a table constraint, list of columns which the constraint constrains │
From that I wouldn't have figured it'd apply to foreign keys
as well. So I assume it is fair to say that "foreign keys
are one type of table constraint", right ?Right. I think what the comment is actually trying to point out is that
conkey isn't relevant to domain constraints, which also appear in
pg_constraint.Can someone come up with better documention wording for conkey? I
can't:http://developer.postgresql.org/pgdocs/postgres/catalog-pg-constraint.html
How about adding a second line:
"Note: since foreign keys are table constraints, applies to those, too."
or
"If a foreign key, list of columns referencing the target table"
(note, "referencing" as opposed to "referenced by" as in confkey)
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert wrote:
On Wed, Jan 21, 2009 at 01:49:44PM -0500, Bruce Momjian wrote:
Tom Lane wrote:
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
This is what my 8.3 manual says:
conkey ? int2[] ? pg_attribute.attnum ? If a table constraint, list of columns which the constraint constrains ?
From that I wouldn't have figured it'd apply to foreign keys
as well. So I assume it is fair to say that "foreign keys
are one type of table constraint", right ?Right. I think what the comment is actually trying to point out is that
conkey isn't relevant to domain constraints, which also appear in
pg_constraint.Can someone come up with better documentation wording for conkey? I
can't:http://developer.postgresql.org/pgdocs/postgres/catalog-pg-constraint.html
How about adding a second line:
"Note: since foreign keys are table constraints, applies to those, too."
or
"If a foreign key, list of columns referencing the target table"
(note, "referencing" as opposed to "referenced by" as in confkey)
I went a different direction with the documentation; I simplified the
wording and made it more explicit; attached and applied.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +