Обсуждение: 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. + Index: doc/src/sgml/catalogs.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v retrieving revision 2.194 diff -c -c -r2.194 catalogs.sgml *** doc/src/sgml/catalogs.sgml 4 Feb 2009 21:30:41 -0000 2.194 --- doc/src/sgml/catalogs.sgml 7 Feb 2009 14:52:38 -0000 *************** *** 1983,1989 **** <entry><structfield>conkey</structfield></entry> <entry><type>int2[]</type></entry> <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry> ! <entry>If a table constraint, list of columns which the constraint constrains</entry> </row> <row> --- 1983,1989 ---- <entry><structfield>conkey</structfield></entry> <entry><type>int2[]</type></entry> <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry> ! <entry>If a table constraint (including a foreign key), list of the constrained columns</entry> </row> <row>