Re: 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


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc