Getting FK relationships from information_schema

From: Kyle <kyle(at)actarg(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Getting FK relationships from information_schema
Date: 2004-06-07 23:57:12
Message-ID: 40C500D8.9060009@actarg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I'm trying to get my application to deduce foreign key relationships
automatically so it can perform appropriate joins for the user. I'm new
to information_schema and having problems getting what I want. Here is
a test script to be run on a database called "test."
------------------------------------------------------------------------------------
-- Demonstrate issues with information_schema

create table empl (
empl_pk int4 primary key,
name varchar
);

create table empl_addr (
empl_k int4 references empl,
type varchar,
address varchar,
primary key (empl_k, type)
);

create table doc (
doc_pk int4 primary key,
name varchar
);

create table empl_doc (
empl_k int4,
doc_k int4,

primary key (empl_k,doc_k),
foreign key (empl_k) references empl,
foreign key (doc_k) references doc
);

create table doc_empl (
doc_k int4,
empl_k int4,

primary key (doc_k,empl_k),
foreign key (doc_k) references doc,
foreign key (empl_k) references empl
);

-- Show info about the tables' primary keys
select
tc.table_name,
tc.constraint_type,
cu.column_name,
cu.ordinal_position

from
information_schema.key_column_usage cu,
information_schema.table_constraints tc

where cu.constraint_name = tc.constraint_name
and cu.table_name = tc.table_name

and tc.constraint_type = 'PRIMARY KEY'

and tc.table_catalog = 'test'
and tc.table_schema = 'public'
order by 2
;

-- Show info about the tables' foreign keys
select
tc.table_name,
tc.constraint_type,
cu.column_name,
cu.ordinal_position

from
information_schema.key_column_usage cu,
information_schema.table_constraints tc

where cu.constraint_name = tc.constraint_name
and cu.table_name = tc.table_name

and tc.constraint_type = 'FOREIGN KEY'

and tc.table_catalog = 'test'
and tc.table_schema = 'public'
order by 2
;

-- Show constraints for our table
select
constraint_name,
table_name,
constraint_type, is_deferrable, initially_deferred

from information_schema.table_constraints

order by 2,3,1
;

-- Show foreign key constraints
select
-- *
constraint_name,
unique_constraint_name,
match_option,update_rule,delete_rule

from information_schema.referential_constraints

order by 2,1
;
------------------------------------------------------------------------------------
This produces the following output:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------
empl | PRIMARY KEY | empl_pk | 1
empl_addr | PRIMARY KEY | empl_k | 1
empl_addr | PRIMARY KEY | type | 2
doc | PRIMARY KEY | doc_pk | 1
empl_doc | PRIMARY KEY | empl_k | 1
empl_doc | PRIMARY KEY | doc_k | 2
doc_empl | PRIMARY KEY | doc_k | 1
doc_empl | PRIMARY KEY | empl_k | 2
(8 rows)

table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------
empl_addr | FOREIGN KEY | empl_k | 1
empl_doc | FOREIGN KEY | empl_k | 1
empl_doc | FOREIGN KEY | doc_k | 1
doc_empl | FOREIGN KEY | doc_k | 1
doc_empl | FOREIGN KEY | empl_k | 1
(5 rows)

constraint_name | table_name | constraint_type | is_deferrable |
initially_deferred
-----------------+------------+-----------------+---------------+--------------------
doc_pkey | doc | PRIMARY KEY | NO | NO
$1 | doc_empl | FOREIGN KEY | NO | NO
$2 | doc_empl | FOREIGN KEY | NO | NO
doc_empl_pkey | doc_empl | PRIMARY KEY | NO | NO
empl_pkey | empl | PRIMARY KEY | NO | NO
$1 | empl_addr | FOREIGN KEY | NO | NO
empl_addr_pkey | empl_addr | PRIMARY KEY | NO | NO
$1 | empl_doc | FOREIGN KEY | NO | NO
$2 | empl_doc | FOREIGN KEY | NO | NO
empl_doc_pkey | empl_doc | PRIMARY KEY | NO | NO
(10 rows)

constraint_name | unique_constraint_name | match_option | update_rule |
delete_rule
-----------------+------------------------+--------------+-------------+-------------
$1 | doc_pkey | NONE | NO ACTION |
NO ACTION
$2 | doc_pkey | NONE | NO ACTION |
NO ACTION
$1 | empl_pkey | NONE | NO ACTION |
NO ACTION
$1 | empl_pkey | NONE | NO ACTION |
NO ACTION
$2 | empl_pkey | NONE | NO ACTION |
NO ACTION
(5 rows)

I can determine all the primary key fields nicely, and I can tell what
fields are foreign keys. The problem is, I can't determine where the
foreign keys are pointing. The problem is, the constraint names ($1,
$2, etc.) are not unique so I don't know how to join the third query
into the fourth.

For example, if I want to know about the foreign keys for empl_doc, I
can determine from query 3 that there are two foreign key constraints on
that table and they are called $1 and $2. From query 4, I should be
able to see what primary key constraints they point to, except there are
3 $1's and 2 $2's in that table so I can't really see where they point.
(Notice that the third and fourth records in the last query are identical!)

Should I be looking somehow else in information_schema to see where
foreign keys link? Or am I correct that either:

1. The automatically generated FK constraint_name should be guaranteed
to be unique; or
2. There should be an additional column in the last query somewhere to
tell me what table the FK reference is coming from.

Kyle

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Brian G. Huber 2004-06-08 05:40:14 Cursor returned from procedure ignores setFetchSize() on CallableStatement
Previous Message Marcus Whitney 2004-06-07 18:24:53 Re: pl/pgsql and transaction locks