Message-ID: <40C500D8.9060009@actarg.com>
Date: Mon, 07 Jun 2004 17:57:12 -0600
From: Kyle <kyle@actarg.com>
Organization: Action Target Inc.
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Getting FK relationships from information_schema
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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