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 |
timestampwithouttime 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 |
timestampwithouttime 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
ASrefer_to_col
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc NATURAL JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
INNERJOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON tc.CONSTRAINT_NAME=rc.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEAS 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
