pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINFORMATION_SCHEMA and foreign keys
3+ messages / 3 participants
[nested] [flat]
* INFORMATION_SCHEMA and foreign keys
@ 2004-09-05 22:56 Troels Arvin <troels@arvin.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Troels Arvin @ 2004-09-05 22:56 UTC (permalink / raw)
To: 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 | 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INFORMATION_SCHEMA and foreign keys
@ 2004-09-05 23:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Troels Arvin <troels@arvin.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2004-09-05 23:29 UTC (permalink / raw)
To: Troels Arvin <troels@arvin.dk>; +Cc: pgsql-sql
On Mon, 6 Sep 2004, Troels Arvin wrote:
> 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.
[...]
> 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)?
Possibly, yes. You'd be better off if you named your constraints rather
than letting the system name them for you, but in general you can't rely
on someone else doing that. This is a side effect of allowing table
unique constraint names rather than schema unique constraint names (as a
side note you would need to constrain schema in those joins even if we
did schema unique names).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INFORMATION_SCHEMA and foreign keys
@ 2004-09-06 02:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-09-06 02:13 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Troels Arvin <troels@arvin.dk>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Mon, 6 Sep 2004, Troels Arvin wrote:
>> 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)?
> Possibly, yes. You'd be better off if you named your constraints rather
> than letting the system name them for you, but in general you can't rely
> on someone else doing that. This is a side effect of allowing table
> unique constraint names rather than schema unique constraint names (as a
> side note you would need to constrain schema in those joins even if we
> did schema unique names).
FWIW, the default constraint name creation rules have been rejiggered in
8.0 so that it's much more likely that generated names will be unique
schema-wide (see ChooseConstraintName). But we do not make any attempt
to positively guarantee this --- in particular, the user can still pick
nonunique constraint names, and databases reloaded from existing dumps
are likely to still have lots of "$1" etc.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-09-06 02:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-05 22:56 INFORMATION_SCHEMA and foreign keys Troels Arvin <troels@arvin.dk>
2004-09-05 23:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-09-06 02:13 ` Tom Lane <tgl@sss.pgh.pa.us>
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