Retrieving foreign key information via jdbc

From: Michael Stephenson <mstephenson(at)tirin(dot)openworld(dot)co(dot)uk>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Retrieving foreign key information via jdbc
Date: 2002-11-08 17:29:24
Message-ID: Pine.LNX.4.30.0211081655330.13258-100000@tirin.openworld.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,

Using the postgres jdbc driver is there any way it's possible to retrieve
the details of the foreign key created in:

CREATE TABLE "table_a" (
"c" character varying(10) NOT NULL UNIQUE,
"a" int,
"b" int,
Constraint "table_a_pkey" Primary Key ("a", "b")
);

CREATE TABLE "table_b" (
"c" character varying(10) NOT NULL references table_a(c)
);

I tried using getImportedKeys, but the implementation seems to be written
assuming that all foreign keys reference a primary key in another table
and returns incorrect results in the case above (see results below).

Whether this key should be returned or not seems a little unclear to me
after reading the apidocs, but even if I was sure from the look I've had
at the code I don't know enough about the postgres system tables to fix
it..

Thanks for your time,

Michael

PKTABLE_CAT : null
PKTABLE_SCHEM : null
PKTABLE_NAME : table_a
PKCOLUMN_NAME : c
FKTABLE_CAT : null
FKTABLE_SCHEM : null
FKTABLE_NAME : table_b
FKCOLUMN_NAME : c
KEY_SEQ : 1
UPDATE_RULE : 3
DELETE_RULE : 3
FK_NAME : <unnamed>\000table_b\000table_a\000UNSPECIFIED\000c\000c\000
PK_NAME : table_a_pkey
DEFERRABILITY : 7
********************************************************************************
PKTABLE_CAT : null
PKTABLE_SCHEM : null
PKTABLE_NAME : table_a
PKCOLUMN_NAME :
FKTABLE_CAT : null
FKTABLE_SCHEM : null
FKTABLE_NAME : table_b
FKCOLUMN_NAME :
KEY_SEQ : 2
UPDATE_RULE : 3
DELETE_RULE : 3
FK_NAME : <unnamed>\000table_b\000table_a\000UNSPECIFIED\000c\000c\000
PK_NAME : table_a_pkey
DEFERRABILITY : 7

Web Applications Developer
Open World Ltd, The Old Malthouse, Clarence Street, Bath, BA1 5NS.
Tel: +44 1225 444950 Fax: +44 1225 336738
http://www.openworld.org/

CONFIDENTIALITY NOTICE
The information contained in this message is confidential, intended only for
the use of the individual or the entity named as recipient. If the reader of
this message is not that recipient, you are notified that any dissemination,
distribution or copy of this message is strictly prohibited. If you have
received this message in error, please immediately notify us by telephone on
the number above. Your co-operation is appreciated.

Browse pgsql-jdbc by date

  From Date Subject
Next Message Jens Carlberg 2002-11-08 17:49:46 Re: possible memory leak??
Previous Message Aaron Mulder 2002-11-08 16:48:50 Re: PostgresqlDataSource