Thread: fields and foreign keys
Hello! I'd like to know how can i get list of fields and corresponding foreign keys (referenced table and field). Thanks!
On Thu, Jun 23, 2005 at 01:34:28AM -0700, mrix wrote: > > I'd like to know how can i get list of fields and corresponding foreign > keys (referenced table and field). For individual tables, client interfaces usually have a way to show the table definition. In psql, for example, you can use "\d tablename". If you want to see all foreign key constraints for all tables then you could query the pg_constraint system catalog. http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html You can get an idea of what query to issue by viewing the hidden commands that psql executes (run "psql -E" or execute "\set ECHO_HIDDEN" and then execute "\d tablename"). Here's an example: SELECT conrelid::regclass, conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE contype = 'f'; -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Yeah, thanks! But I've already tried this approach, and it's not quite i need. Because as i understand foreign keys are built on indexes, so i get *index* and corresponding foreign key definition. Well then i have to find out what filed this index "belongs to" (assuming 1 field - 1 index)
On Thu, Jun 23, 2005 at 10:11:15PM -0700, mrix wrote: > > But I've already tried this approach, and it's not quite i need. > Because as i understand foreign keys are built on indexes, so i get > *index* and corresponding foreign key definition. > Well then i have to find out what filed this index "belongs to" > (assuming 1 field - 1 index) The query I posted shows the relation (table) that contains the foreign key constraint, the constraint name (not an index name), and the constraint definition, which includes the names of the referring column(s) and the referred-to table and column(s). For example: CREATE TABLE foo (id integer PRIMARY KEY); CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo); SELECT conrelid::regclass, conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE contype = 'f'; conrelid | conname | pg_get_constraintdef ----------+----------------+---------------------------------------- bar | bar_fooid_fkey | FOREIGN KEY (fooid) REFERENCES foo(id) (1 row) If this isn't what you're looking for, then please post an example that shows what you're trying to do. That is, something like "given the following table definitions, I'm looking for a query that will give me this output...." What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
[Please copy the mailing list on replies.] On Fri, Jun 24, 2005 at 05:52:46PM +0300, Marik wrote: > > But what i really need is field this constraint belongs to then... > I'd like to have such result: > CREATE TABLE foo (id integer PRIMARY KEY); > CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo); > > SELECT <theSQL_goes_here> > > conrelid | fieldname | pg_get_constraintdef > ----------+----------------+---------------------------------------- > bar | fooid | FOREIGN KEY (fooid) REFERENCES foo(id) > > or better this: > > conrelid | fieldname | ref_table | ref_field > ----------+----------------+---------------------------------------- > bar | fooid | foo | id You can get the column names by joining pg_constraint and pg_attribute. http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html In PostgreSQL 7.4 and later you can query the Information Schema. Here are some of the views that should be useful: http://www.postgresql.org/docs/8.0/static/infoschema-key-column-usage.html http://www.postgresql.org/docs/8.0/static/infoschema-constraint-column-usage.html http://www.postgresql.org/docs/8.0/static/infoschema-referential-constraints.html http://www.postgresql.org/docs/8.0/static/infoschema-table-constraints.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/