INFORMATION_SCHEMA and foreign keys - Mailing list pgsql-sql

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