DatabaseMetaData getImportedKeys() order

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