Re: Multi column foreign keys.

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