Re: Multi column foreign keys.

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