[SQL] Getting FK relationships from information_schema

Mon, 07 Jun 2004 17:05:05 -0700

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to