To: pgsql-sql@postgresql.org
From: Troels Arvin <troels@arvin.dk>
Subject: INFORMATION_SCHEMA and foreign keys
Date: Mon, 06 Sep 2004 00:56:56 +0200
Lines: 122
Message-ID: <pan.2004.09.05.22.56.56.521040@arvin.dk>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.14.2.91 (As She Crawled Across the Table)

Hello,

For a table with a foreign key, I need to find out which columns are being
referred to in the foreing key.

Example setup: Create two ("master") tables, and two ("slave") tables
which refer to a master table:

CREATE TABLE MASTER_A (
  fullname VARCHAR(50) NOT NULL,
  birthday TIMESTAMP NOT NULL,
  email VARCHAR(50),
  PRIMARY KEY(fullname,birthday)
);
CREATE TABLE MASTER_B (
  fullname VARCHAR(50) NOT NULL,
  birthday TIMESTAMP NOT NULL,
  email VARCHAR(50),
  PRIMARY KEY(fullname,birthday)
);

CREATE TABLE SLAVE_A (
  fullname VARCHAR(50) NOT NULL,
  birthday TIMESTAMP NOT NULL,
  phone VARCHAR(30),
  FOREIGN KEY(fullname,birthday) REFERENCES MASTER_A
);
CREATE TABLE SLAVE_B (
  fullname VARCHAR(50) NOT NULL,
  birthday TIMESTAMP NOT NULL,
  phone VARCHAR(30),
  FOREIGN KEY(fullname,birthday) REFERENCES MASTER_B
);

\d slave_a gives:
               Table "public.slave_a"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 fullname | character varying(50)       | not null birthday | timestamp
 without time zone | not null phone    | character varying(30)       |
Foreign-key constraints:
    "$1" FOREIGN KEY (fullname, birthday) REFERENCES master_a(fullname,
    birthday)

\d slave_b gives:
               Table "public.slave_b"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 fullname | character varying(50)       | not null birthday | timestamp
 without time zone | not null phone    | character varying(30)       |
Foreign-key constraints:
    "$1" FOREIGN KEY (fullname, birthday) REFERENCES master_b(fullname,
    birthday)

Note that the constraint name for both foreign keys is "$1".

Now, I tried a query like this, which I hoped to use to find out which
columns in the "slave_a" table were part of which foreign key in
"master_a":

SELECT
  tc.CONSTRAINT_NAME AS consnam,
  ccu.COLUMN_NAME AS colnam,
  UNIQUE_CONSTRAINT_NAME AS ucnam,
  ref_ccu.COLUMN_NAME AS refer_to_col
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
  NATURAL JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
  INNER JOIN
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
      ON tc.CONSTRAINT_NAME=rc.CONSTRAINT_NAME
  INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ref_ccu
      ON UNIQUE_CONSTRAINT_NAME=ref_ccu.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME='slave_a'
  AND
    CONSTRAINT_TYPE='FOREIGN KEY';

The result:

 consnam |  colnam  |     ucnam     | refer_to_col
---------+----------+---------------+--------------
 $1      | fullname | master_a_pkey | fullname
 $1      | birthday | master_a_pkey | fullname
 $1      | fullname | master_a_pkey | birthday
 $1      | birthday | master_a_pkey | birthday
 $1      | fullname | master_b_pkey | fullname
 $1      | birthday | master_b_pkey | fullname
 $1      | fullname | master_b_pkey | birthday
 $1      | birthday | master_b_pkey | birthday

The query returns double the numer of rows, compared to what I wanted. The
problem seems to stem from PostgreSQL's naming of constraints without
explicit name: They seem to be named $1, $2, etc, and the default names
are reused.

My INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS looks like this:

SELECT CONSTRAINT_NAME,UNIQUE_CONSTRAINT_NAME FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

 constraint_name | unique_constraint_name
-----------------+------------------------
 $1              | master_b_pkey
 $1              | master_a_pkey

Note, again, that the CONSTRAINT_NAMEs are not unique, although they
affect two different tables. Hence, there doesn't seem to be a way to map
a specifict referential constraint to a specific primary/unique constraint
in the table being referred to.

Is my only way forward to drop using the INFORMATION_SCHEMA and work with
the pg_catalog if I want to determine which columns are being referred to
in a (set of) foreign key column(s)?

(Thanks to readers who got this far.)

-- 
Greetings from Troels Arvin, Copenhagen, Denmark