Re: Multi column foreign keys.

Hello all,

I've got the following tables :

CREATE TABLE Student
(
Student_ID serial PRIMARY KEY,
First_Name varchar(50),
Last_Name varchar(50),
UNIQUE (First_Name, Last_Name)
);

CREATE TABLE Address
(
First_Name varchar(50),
Last_Name varchar(50),
Address varchar(50),
PRIMARY KEY (First_Name, Last_Name),
FOREIGN KEY (First_Name, Last_Name) REFERENCES student (First_Name,Last_Name)
);

And some code like :

Driver driver = (Driver)Class.forName("org.postgresql.Driver").newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:postgresql:test";
Connection con = DriverManager.getConnection(url, "mof", "");
DatabaseMetaData meta = con.getMetaData ();
ResultSet metaRS = meta.getImportedKeys(null, null, "address");

while (metaRS.next())
System.out.println("FK_C == " + metaRS.getString("FKCOLUMN_NAME"));

Which only gives me :

FK_C == first_name

How am I supposed to find out about "Last_Name" ?

Mof.

On Tue, 9 Dec 2003, Mofeed Shahin wrote:

> Hello all,
>
> I've got the following tables :
>
> CREATE TABLE Student
> (
> Student_ID serial PRIMARY KEY,
> First_Name varchar(50),
> Last_Name varchar(50),
> UNIQUE (First_Name, Last_Name)
> );
>
> CREATE TABLE Address
> (
> First_Name varchar(50),
> Last_Name varchar(50),
> Address varchar(50),
> PRIMARY KEY (First_Name, Last_Name),
> FOREIGN KEY (First_Name, Last_Name) REFERENCES student (First_Name,Last_Name)
> );
>
> And some code like :
>
> Driver driver = (Driver)Class.forName("org.postgresql.Driver").newInstance();
> DriverManager.registerDriver(driver);
> String url = "jdbc:postgresql:test";
> Connection con = DriverManager.getConnection(url, "mof", "");
> DatabaseMetaData meta = con.getMetaData ();
> ResultSet metaRS = meta.getImportedKeys(null, null, "address");
>
> while (metaRS.next())
> System.out.println("FK_C == " + metaRS.getString("FKCOLUMN_NAME"));
>
> Which only gives me :
>
> FK_C == first_name
>
> How am I supposed to find out about "Last_Name" ?
>

It is a known issue that the driver does not properly support retrieving
foreign key information that is based on an UNIQUE constraint instead of a
PRIMARY KEY. I expected it to not return any results, but in your case
you do have a primary key on the student table and it is half-matching
that. Take a look at metaRS.getString("PK_NAME") and you'll see
student_pkey not student_first_name_key.

I believe that it is now possible to retrieve this information in the 7.4
series using a combination of pg_constraint and pg_depend, but it is not
backwards compatible in the sense that if a 7.2 database was upgraded to
7.4 it won't have all of the constraint and dependency information that it
should if it was created new using 7.4. Perhaps I will take another look
at this and see what is actually involved.

For now your options are:

- make the primary key of student be first_name,last_name
- make the address table have student_id instead of first_name,last_name
- not use getImportedKeys, getExportedKeys, or getCrossReference
- fix the above methods yourself

Kris Jurka

Ummm Thanks for that.
The problem is that its not my database.
I'm just writing a bridge between JDBC, and another format, and therefore
I am expect to handle all sorts of weird table setups....

Mof.

On Tue, 9 Dec 2003 04:03 pm, Kris Jurka wrote:
> On Tue, 9 Dec 2003, Mofeed Shahin wrote:
> > Hello all,
> >
> > I've got the following tables :
> >
> > CREATE TABLE Student
> > (
> > Student_ID serial PRIMARY KEY,
> > First_Name varchar(50),
> > Last_Name varchar(50),
> > UNIQUE (First_Name, Last_Name)
> > );
> >
> > CREATE TABLE Address
> > (
> > First_Name varchar(50),
> > Last_Name varchar(50),
> > Address varchar(50),
> > PRIMARY KEY (First_Name, Last_Name),
> > FOREIGN KEY (First_Name, Last_Name) REFERENCES student
> > (First_Name,Last_Name) );
> >
> > And some code like :
> >
> > Driver driver =
> > (Driver)Class.forName("org.postgresql.Driver").newInstance();
> > DriverManager.registerDriver(driver);
> > String url = "jdbc:postgresql:test";
> > Connection con = DriverManager.getConnection(url, "mof", "");
> > DatabaseMetaData meta = con.getMetaData ();
> > ResultSet metaRS = meta.getImportedKeys(null, null, "address");
> >
> > while (metaRS.next())
> > System.out.println("FK_C == " + metaRS.getString("FKCOLUMN_NAME"));
> >
> > Which only gives me :
> >
> > FK_C == first_name
> >
> > How am I supposed to find out about "Last_Name" ?
>
> It is a known issue that the driver does not properly support retrieving
> foreign key information that is based on an UNIQUE constraint instead of a
> PRIMARY KEY. I expected it to not return any results, but in your case
> you do have a primary key on the student table and it is half-matching
> that. Take a look at metaRS.getString("PK_NAME") and you'll see
> student_pkey not student_first_name_key.
>
> I believe that it is now possible to retrieve this information in the 7.4
> series using a combination of pg_constraint and pg_depend, but it is not
> backwards compatible in the sense that if a 7.2 database was upgraded to
> 7.4 it won't have all of the constraint and dependency information that it
> should if it was created new using 7.4. Perhaps I will take another look
> at this and see what is actually involved.
>
> For now your options are:
>
> - make the primary key of student be first_name,last_name
> - make the address table have student_id instead of first_name,last_name
> - not use getImportedKeys, getExportedKeys, or getCrossReference
> - fix the above methods yourself
>
> Kris Jurka

On Tue, 9 Dec 2003, Mofeed Shahin wrote:

> Ummm Thanks for that.
> The problem is that its not my database.
> I'm just writing a bridge between JDBC, and another format, and therefore
> I am expect to handle all sorts of weird table setups....

Here's a patch that should fix your problem if you are running a 7.4 or
better server. A prebuilt binary for the 1.4 jdk is available here:

http://www.ejurka.com/pgsql/

This jar file has a different default port compiled in so you must be
certain to specify the port you want to connect to if you try and use it.

Kris Jurka

Oh wow, thanks alot for that Kris,

Some questions though:
When I do
DatabaseMetaData metaData = connection.getMetaData ();
ResultSet tableResultSet = metaData.getTables ("", null, null, new
String[]{"TABLE"})

I'm actually getting alot more tables than I wanted. I seem to be getting all
the system tables, which I don't want.
I also get messages like :
"Column is_insertable_into in table views has unknown type code 1111"

Is this something to do with upgrading from 7.3 to 7.4 ?

Mof.

On Tue, 9 Dec 2003 09:02 pm, you wrote:
> On Tue, 9 Dec 2003, Mofeed Shahin wrote:
> > Ummm Thanks for that.
> > The problem is that its not my database.
> > I'm just writing a bridge between JDBC, and another format, and therefore
> > I am expect to handle all sorts of weird table setups....
>
> Here's a patch that should fix your problem if you are running a 7.4 or
> better server. A prebuilt binary for the 1.4 jdk is available here:
>
> http://www.ejurka.com/pgsql/
>
> This jar file has a different default port compiled in so you must be
> certain to specify the port you want to connect to if you try and use it.
>
> Kris Jurka

On Wed, 10 Dec 2003, Mofeed Shahin wrote:

> Some questions though:
> When I do
> DatabaseMetaData metaData = connection.getMetaData ();
> ResultSet tableResultSet = metaData.getTables ("", null, null, new
> String[]{"TABLE"})
>
> I'm actually getting alot more tables than I wanted. I seem to be getting all
> the system tables, which I don't want.

With 7.4 came the information_schema which I believe is what you are
seeing. The attached patch should fix that.

> I also get messages like :
> "Column is_insertable_into in table views has unknown type code 1111"

This message is probably from your own code after doing
DatabaseMetaData.getColumns() on information_schema.views. The DATA_TYPE
column is returning java.sql.Types.OTHER which you don't know how to
handle. Perhaps we need to look at how domains are handled here and it
should return the base type's code, but as this problem is in the
information schema you shouldn't have to deal with in.

Kris Jurka

On Wed, 10 Dec 2003 01:31 pm, you wrote:
> On Wed, 10 Dec 2003, Mofeed Shahin wrote:
> > Some questions though:
> > When I do
> > DatabaseMetaData metaData = connection.getMetaData ();
> > ResultSet tableResultSet = metaData.getTables ("", null, null, new
> > String[]{"TABLE"})
> >
> > I'm actually getting alot more tables than I wanted. I seem to be getting
> > all the system tables, which I don't want.
>
> With 7.4 came the information_schema which I believe is what you are
> seeing. The attached patch should fix that.

Once again, thanks alot. That worked very well.

Will these patches be applied to the standard jdbc driver ?

> > I also get messages like :
> > "Column is_insertable_into in table views has unknown type code 1111"
>
> This message is probably from your own code after doing
> DatabaseMetaData.getColumns() on information_schema.views. The DATA_TYPE
> column is returning java.sql.Types.OTHER which you don't know how to
> handle. Perhaps we need to look at how domains are handled here and it
> should return the base type's code, but as this problem is in the
> information schema you shouldn't have to deal with in.

Right you are. Thanks.

Mof.

> Will these patches be applied to the standard jdbc driver ?
>

The jdbc driver is in a state of flux at the moment. The core team has
decided to give the jdbc code the proverbial boot from the cvs repository
and it is uncertain as to where it will land. Where it will end up and
who will run that have yet to be determined. Once it does find a new home
the release schedule and so on will probably not be governed by the server
releases. With the existing arrangement I would have recommended the
information_schema change go into 7.4 while holding the foreign key to a
unique index change for 7.5 as it has some additional side effects that
shouldn't go into the stable release. How things will go in the new
driver I have no idea.

Kris Jurka