From: Kyle <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Subject: Getting FK relationships from information_schema
Date: Mon, 07 Jun 2004 17:57:12 -0600
Message-ID: <40C500D8.9060009@actarg.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com
Subject: Re: Getting FK relationships from information_schema
In-Reply-To: <40C500D8.9060009@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox