Hi,
In org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData both getImportedKeys() and getExportedKeys()
call getImportedExportedKeys() which order its result strictly according to
java.sql.DatabaseMetaData : PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ. But this is not
enough when 2 multi-field keys point to the same table. E.g. :
CREATE TABLE test."PERSON"
(
"FIRST_NAME" character varying(100) NOT NULL,
"LAST_NAME" character varying(100) NOT NULL,
"FIRST_NAME_PARENT_1" character varying(100),
"LAST_NAME_PARENT_1" character varying(100),
"FIRST_NAME_PARENT_2" character varying(100),
"LAST_NAME_PARENT_2" character varying(100),
CONSTRAINT "PERSON_pkey" PRIMARY KEY ("FIRST_NAME" , "LAST_NAME" ),
CONSTRAINT "PARENT_1_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_1", "LAST_NAME_PARENT_1")
REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "PARENT_2_fkey" FOREIGN KEY ("FIRST_NAME_PARENT_2", "LAST_NAME_PARENT_2")
REFERENCES test."PERSON" ("FIRST_NAME", "LAST_NAME") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
For this method to be useful the constraint must be used otherwise we can't know which first name
goes with which last name. The H2 database (and I believe MySQL as well) order by PKTABLE_CAT,
PKTABLE_SCHEM, PKTABLE_NAME, FK_NAME, KEY_SEQ (see
https://code.google.com/p/h2database/source/browse/tags/version-1.3.172/h2/src/main/org/h2/jdbc/JdbcDatabaseMetaData.java
).
For 9.2-1003, con.conname just needs to be added before pos.n at line 3440 :
--- AbstractJdbc2DatabaseMetaData-9.2-1003.java 2013-07-22 17:07:25.001273200 +0200
+++ AbstractJdbc2DatabaseMetaData-multiField.java 2013-07-22 17:09:16.570654600 +0200
@@ -3437,13 +3437,16 @@
sql += " AND fkc.relname = " + escapeQuotes(foreignTable);
}
+ // con.conname required otherwise we cannot assemble
+ // multi-field foreign keys when a table has multiple links
+ // to another
if (primaryTable != null)
{
- sql += " ORDER BY fkn.nspname,fkc.relname,pos.n";
+ sql += " ORDER BY fkn.nspname,fkc.relname,con.conname,pos.n";
}
else
{
- sql += " ORDER BY pkn.nspname,pkc.relname,pos.n";
+ sql += " ORDER BY pkn.nspname,pkc.relname,con.conname,pos.n";
}
return createMetaDataStatement().executeQuery(sql);
Cheers,
Sylvain
