pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting FK relationships from information_schema
6+ messages / 3 participants
[nested] [flat]
* Getting FK relationships from information_schema
@ 2004-06-07 23:57 Kyle <kyle@actarg.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kyle @ 2004-06-07 23:57 UTC (permalink / raw)
To: pgsql-sql
I'm trying to get my application to deduce foreign key relationships
automatically so it can perform appropriate joins for the user. I'm new
to information_schema and having problems getting what I want. Here is
a test script to be run on a database called "test."
------------------------------------------------------------------------------------
-- Demonstrate issues with information_schema
create table empl (
empl_pk int4 primary key,
name varchar
);
create table empl_addr (
empl_k int4 references empl,
type varchar,
address varchar,
primary key (empl_k, type)
);
create table doc (
doc_pk int4 primary key,
name varchar
);
create table empl_doc (
empl_k int4,
doc_k int4,
primary key (empl_k,doc_k),
foreign key (empl_k) references empl,
foreign key (doc_k) references doc
);
create table doc_empl (
doc_k int4,
empl_k int4,
primary key (doc_k,empl_k),
foreign key (doc_k) references doc,
foreign key (empl_k) references empl
);
-- Show info about the tables' primary keys
select
tc.table_name,
tc.constraint_type,
cu.column_name,
cu.ordinal_position
from
information_schema.key_column_usage cu,
information_schema.table_constraints tc
where cu.constraint_name = tc.constraint_name
and cu.table_name = tc.table_name
and tc.constraint_type = 'PRIMARY KEY'
and tc.table_catalog = 'test'
and tc.table_schema = 'public'
order by 2
;
-- Show info about the tables' foreign keys
select
tc.table_name,
tc.constraint_type,
cu.column_name,
cu.ordinal_position
from
information_schema.key_column_usage cu,
information_schema.table_constraints tc
where cu.constraint_name = tc.constraint_name
and cu.table_name = tc.table_name
and tc.constraint_type = 'FOREIGN KEY'
and tc.table_catalog = 'test'
and tc.table_schema = 'public'
order by 2
;
-- Show constraints for our table
select
constraint_name,
table_name,
constraint_type, is_deferrable, initially_deferred
from information_schema.table_constraints
order by 2,3,1
;
-- Show foreign key constraints
select
-- *
constraint_name,
unique_constraint_name,
match_option,update_rule,delete_rule
from information_schema.referential_constraints
order by 2,1
;
------------------------------------------------------------------------------------
This produces the following output:
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------
empl | PRIMARY KEY | empl_pk | 1
empl_addr | PRIMARY KEY | empl_k | 1
empl_addr | PRIMARY KEY | type | 2
doc | PRIMARY KEY | doc_pk | 1
empl_doc | PRIMARY KEY | empl_k | 1
empl_doc | PRIMARY KEY | doc_k | 2
doc_empl | PRIMARY KEY | doc_k | 1
doc_empl | PRIMARY KEY | empl_k | 2
(8 rows)
table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------
empl_addr | FOREIGN KEY | empl_k | 1
empl_doc | FOREIGN KEY | empl_k | 1
empl_doc | FOREIGN KEY | doc_k | 1
doc_empl | FOREIGN KEY | doc_k | 1
doc_empl | FOREIGN KEY | empl_k | 1
(5 rows)
constraint_name | table_name | constraint_type | is_deferrable |
initially_deferred
-----------------+------------+-----------------+---------------+--------------------
doc_pkey | doc | PRIMARY KEY | NO | NO
$1 | doc_empl | FOREIGN KEY | NO | NO
$2 | doc_empl | FOREIGN KEY | NO | NO
doc_empl_pkey | doc_empl | PRIMARY KEY | NO | NO
empl_pkey | empl | PRIMARY KEY | NO | NO
$1 | empl_addr | FOREIGN KEY | NO | NO
empl_addr_pkey | empl_addr | PRIMARY KEY | NO | NO
$1 | empl_doc | FOREIGN KEY | NO | NO
$2 | empl_doc | FOREIGN KEY | NO | NO
empl_doc_pkey | empl_doc | PRIMARY KEY | NO | NO
(10 rows)
constraint_name | unique_constraint_name | match_option | update_rule |
delete_rule
-----------------+------------------------+--------------+-------------+-------------
$1 | doc_pkey | NONE | NO ACTION |
NO ACTION
$2 | doc_pkey | NONE | NO ACTION |
NO ACTION
$1 | empl_pkey | NONE | NO ACTION |
NO ACTION
$1 | empl_pkey | NONE | NO ACTION |
NO ACTION
$2 | empl_pkey | NONE | NO ACTION |
NO ACTION
(5 rows)
I can determine all the primary key fields nicely, and I can tell what
fields are foreign keys. The problem is, I can't determine where the
foreign keys are pointing. The problem is, the constraint names ($1,
$2, etc.) are not unique so I don't know how to join the third query
into the fourth.
For example, if I want to know about the foreign keys for empl_doc, I
can determine from query 3 that there are two foreign key constraints on
that table and they are called $1 and $2. From query 4, I should be
able to see what primary key constraints they point to, except there are
3 $1's and 2 $2's in that table so I can't really see where they point.
(Notice that the third and fourth records in the last query are identical!)
Should I be looking somehow else in information_schema to see where
foreign keys link? Or am I correct that either:
1. The automatically generated FK constraint_name should be guaranteed
to be unique; or
2. There should be an additional column in the last query somewhere to
tell me what table the FK reference is coming from.
Kyle
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting FK relationships from information_schema
@ 2004-06-08 05:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle <kyle@actarg.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-06-08 05:53 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Kyle <kyle@actarg.com> writes:
> I'm trying to get my application to deduce foreign key relationships
> automatically so it can perform appropriate joins for the user. I'm new
> to information_schema and having problems getting what I want.
> ...
> I can determine all the primary key fields nicely, and I can tell what
> fields are foreign keys. The problem is, I can't determine where the
> foreign keys are pointing. The problem is, the constraint names ($1,
> $2, etc.) are not unique so I don't know how to join the third query
> into the fourth.
Hmm, this is messy :-(. The SQL spec requires constraint names to be
unique within a schema. Postgres doesn't require them to be unique even
within a table. We were aware that there were some compatibility issues
there, but I hadn't realized that the information_schema design is
fundamentally dependent on the assumption of schema-wide uniqueness for
these names.
For a number of reasons (backwards compatibility being the hardest to
argue with), adopting the spec's restriction on constraint names seems
unlikely to happen. You could of course follow it within your own
database designs, but I don't foresee Postgres enforcing it on
everyone.
In the short run I think your only answer is to dig deeper than
information_schema and look directly at the Postgres catalogs.
In the long run it'd be nice to have a cleaner answer, but I'm not
sure what it ought to look like. Can we get away with adding
implementation-specific columns to information_schema tables?
If not, what other alternatives are there?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting FK relationships from information_schema
@ 2004-06-08 15:13 Kyle <kyle@actarg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kyle @ 2004-06-08 15:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Tom Lane wrote:
>Kyle <kyle@actarg.com> writes:
>
>
>>The problem is, the constraint names ($1,
>>$2, etc.) are not unique so I don't know how to join the third query
>>into the fourth.
>>
>>
>
>Hmm, this is messy :-(. The SQL spec requires constraint names to be
>unique within a schema. Postgres doesn't require them to be unique even
>within a table.
>
I think this is only an issue when the user relies on postgres to choose
a constraint name automatically. Seems like a reasonable approach would
be to have postgres choose a name for the constraint that happens to be
unique in the schema (like tablename_fkey_$1). Then if the user wants
to make named constraints that all have the same name, he can (and
information_schema will be less useful) or he can rely on the
automatically generated names to be a bit more descriptive in
information_schema.
>Can we get away with adding
>implementation-specific columns to information_schema tables?
>If not, what other alternatives are there?
>
>
>
Another possible approach: Does the constraint name showing up in
information_schema really have to be the _real_ name? Or can we just
make a name consisting of the table name appended to the internal
postgres constraint name.
I think I like this less than the first idea.
Kyle
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting FK relationships from information_schema
@ 2004-06-09 05:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle <kyle@actarg.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-06-09 05:04 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Kyle <kyle@actarg.com> writes:
> I think this is only an issue when the user relies on postgres to choose
> a constraint name automatically. Seems like a reasonable approach would
> be to have postgres choose a name for the constraint that happens to be
> unique in the schema (like tablename_fkey_$1).
We have discussed changing the default names of FK constraints before.
I have no problem with doing something like the above --- any objection
out there?
(Of course, this is only a long-term fix for your original problem,
as it'll take a good long while for any such naming change to propagate
to Joe's-Corner-Bar's database.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting FK relationships from information_schema
@ 2004-06-09 06:36 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2004-06-09 06:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Kyle <kyle@actarg.com>; +Cc: pgsql-sql
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > I think this is only an issue when the user relies on postgres to
> > choose a constraint name automatically. Seems like a reasonable
> > approach would be to have postgres choose a name for the constraint
> > that happens to be unique in the schema (like tablename_fkey_$1).
>
> We have discussed changing the default names of FK constraints
> before. I have no problem with doing something like the above --- any
> objection out there?
I think it's a good idea. It will also make the error messages of the
kind "foreign key $1 violated" a bit clearer by default.
There will, however, be complaints that the constraint names are not
automatically renamed with the table; but we are used to those by now.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Getting FK relationships from information_schema
@ 2004-06-10 06:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-06-10 06:32 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Kyle <kyle@actarg.com>; pgsql-sql
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> We have discussed changing the default names of FK constraints
>> before. I have no problem with doing something like the above --- any
>> objection out there?
> I think it's a good idea. It will also make the error messages of the
> kind "foreign key $1 violated" a bit clearer by default.
Right ho --- I'm working on a patch that will choose default names
of the form table_column_checkN and table_column_fkeyN, similar to
what we already do for index constraints.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-10 06:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-07 23:57 Getting FK relationships from information_schema Kyle <kyle@actarg.com>
2004-06-08 05:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-08 15:13 ` Kyle <kyle@actarg.com>
2004-06-09 05:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-09 06:36 ` Peter Eisentraut <peter_e@gmx.net>
2004-06-10 06:32 ` 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