From: Troels Arvin <troels@arvin.dk>
To: pgsql-sql@postgresql.org
Subject: INFORMATION_SCHEMA and foreign keys
Date: Mon, 06 Sep 2004 00:56:56 +0200
Message-ID: <pan.2004.09.05.22.56.56.521040@arvin.dk> (raw)
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
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: troels@arvin.dk
Subject: Re: INFORMATION_SCHEMA and foreign keys
In-Reply-To: <pan.2004.09.05.22.56.56.521040@arvin.dk>
* 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