Description:
DatabaseMetaData.extractForeignKeyForTable checks if the comment line begins with "FOREIGN
KEY", however the line actually begins with something like "CONSTRAINT `0_181` FOREIGN
KEY", so DatabaseMetaData.getImportedForeignKeys returns no foreign keys for tables.

This probably also applies to DatabaseMetaData.getExportedForeignKeys.

This also bring me to another point.  If the current code for
DatabaseMetaData.getImportedForeignKeys worked, it would return a null values for the
foreign key name, i.e the following assert would fail:

ResultSet rs = connection.getMetaData().getImportedKeys(catalogName, schemaName,
tableName);
...
String fkName = rs.getString(12);
assert (fkName != null) : "Foreign Key name is null";

However it seems to me if the comments for the table schema returns lines such as
"CONSTRAINT `0_181` FOREIGN KEY", then it is atleast possible to assign some meaningful
value to foreign key name, in this case "0_181".  Why is this important?  Because it
allows users to easily group together foreign keys that are made up of more than column.

Regards
Parwy

How to repeat:
execute:

ResultSet rs = connection.getMetaData().getImportedKeys(catalogName, schemaName,
tableName);
...

while (rs.next()) {
    System.out.println("Got a fk");
}

You should never see any fks output.

Suggested fix:
update DatabaseMetaData.extractForeignKeyForTable so that it can parse lines begining with
text such as "CONSTRAINT `0_181` FOREIGN KEY" and use the constraint name/id as the
foreign key name.

Here is the diff for the fix, that shows FKs and gives the FK a name.

***************
*** 1185,1191 ****
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

--- 1185,1191 ----
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

***************
*** 1259,1264 ****
--- 1259,1267 ----
                                  // multi-columned keys : (m n) REFER airline/vv(a b)
                                  int firstLeftParenIndex = keys.indexOf('(');
                                  int firstRightParenIndex = keys.indexOf(')');
+
+                                 String constraintName = keys.substring(0,
firstLeftParenIndex);
+
                                  String referencingColumns =
keys.substring(firstLeftParenIndex
                                                                             + 1,
firstRightParenIndex);
                                  StringTokenizer referencingColumnsTokenizer = new
StringTokenizer(referencingColumns,
***************
*** 1319,1325 ****
                                                        .getBytes();
                                      tuple[10] = Integer.toString(actions[0])
                                                         .getBytes();
!                                     tuple[11] = null; // FK_NAME
                                      tuple[12] = null; // PK_NAME
                                      tuple[13] =
Integer.toString(java.sql.DatabaseMetaData.importedKeyNotDeferrable)
                                                         .getBytes();
--- 1322,1328 ----
                                              .getBytes();
                                      tuple[10] = Integer.toString(actions[0])
                                              .getBytes();
!                                     tuple[11] = s2b(constraintName); // FK_NAME
                                      tuple[12] = null; // PK_NAME
                                      tuple[13] =
Integer.toString(java.sql.DatabaseMetaData.importedKeyNotDeferrable)
                                              .getBytes();
***************
*** 1592,1598 ****
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

--- 1595,1601 ----
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

***************
*** 1657,1665 ****

                                  while (commentTokens.hasMoreTokens()) {
                                      String keys = commentTokens.nextToken();
!                                     getExportKeyResults(catalog,
!                                         tableNameWithCase, keys, tuples,
!                                         fkresults.getString("Name"));
                                  }
                              }
                          }
--- 1660,1666 ----

                                  while (commentTokens.hasMoreTokens()) {
                                      String keys = commentTokens.nextToken();
!                                     getExportKeyResults(catalog, tableNameWithCase,
keys, tuples, fkresults.getString("Name"));
                                  }
                              }
                          }
***************
*** 1869,1875 ****
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

--- 1870,1876 ----
          fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
          fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
          fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
!         fields[11] = new Field("", "FK_NAME", Types.CHAR, 255);
          fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
          fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);

***************
*** 1931,1938 ****

                                  while (commentTokens.hasMoreTokens()) {
                                      String keys = commentTokens.nextToken();
!                                     getImportKeyResults(catalog, table, keys,
!                                         tuples);
                                  }
                              }
                          }
--- 1932,1938 ----

                                  while (commentTokens.hasMoreTokens()) {
                                      String keys = commentTokens.nextToken();
!                                     getImportKeyResults(catalog, table, keys, tuples);
                                  }
                              }
                          }
***************
*** 4921,4927 ****
       * @return the list of rows with new rows added
       */
      public List extractForeignKeyForTable(ArrayList rows,
!         java.sql.ResultSet rs, String catalog) throws SQLException {
          byte[][] row = new byte[3][];
          row[0] = rs.getBytes(1);
          row[1] = s2b(SUPPORTS_FK);
--- 4921,4928 ----
       * @return the list of rows with new rows added
       */
      public List extractForeignKeyForTable(ArrayList rows,
!                                           java.sql.ResultSet rs,
!                                           String catalog) throws SQLException {
          byte[][] row = new byte[3][];
          row[0] = rs.getBytes(1);
          row[1] = s2b(SUPPORTS_FK);
***************
*** 4935,4940 ****
--- 4936,4947 ----
          while (lineTokenizer.hasMoreTokens()) {
              String line = lineTokenizer.nextToken().trim();

+             String constraintName = null;
+             String fkprefix = "CONSTRAINT `     ` ";
+             if (line.length() > fkprefix.length()) {
+                 constraintName = line.substring(0, fkprefix.length());
+                 line = line.substring(fkprefix.length(), line.length());
+             }
              if (line.startsWith("FOREIGN KEY")) {
                  if (line.endsWith(",")) {
                      line = line.substring(0, line.length() - 1);
***************
*** 4960,4965 ****
--- 4967,4973 ----

                  String localColumnNamesString = keyTokens.nextToken();
                  String referCatalogTableString = keyTokens.nextToken();
+
                  StringTokenizer referSchemaTable = new
StringTokenizer(referCatalogTableString
                                                                         .trim(), " .");
                  String referColumnNamesString = keyTokens.nextToken();
***************
*** 4985,4990 ****
--- 4993,4999 ----
                      firstTime = false;
                  }

+                 commentBuf.append(constraintName);
                  commentBuf.append("(");
                  commentBuf.append(localColumnNamesString);
                  commentBuf.append(") REFER ");
***************
*** 4998,5005 ****
                  int lastParenIndex = line.lastIndexOf(")");

                  if (lastParenIndex != (line.length() - 1)) {
!                     String cascadeOptions = cascadeOptions =
line.substring(lastParenIndex
!                                 + 1);
                      commentBuf.append(" ");
                      commentBuf.append(cascadeOptions);
                  }
--- 5007,5013 ----
                  int lastParenIndex = line.lastIndexOf(")");

                  if (lastParenIndex != (line.length() - 1)) {
!                     String cascadeOptions = cascadeOptions =
line.substring(lastParenIndex + 1);
                      commentBuf.append(" ");
                      commentBuf.append(cascadeOptions);
                  }
***************
*** 6282,6292 ****
       *
       * @throws SQLException if a database access error occurs
       */
!     private void getExportKeyResults(String catalog, String exportingTable,
!         String keysComment, List tuples, String fkTableName)
          throws SQLException {
!         getResultsImpl(catalog, exportingTable, keysComment, tuples,
!             fkTableName, true);
      }

      /**
--- 6290,6302 ----
       *
       * @throws SQLException if a database access error occurs
       */
!     private void getExportKeyResults(String catalog,
!                                      String exportingTable,
!                                      String keysComment,
!                                      List tuples,
!                                      String fkTableName)
              throws SQLException {
!         getResultsImpl(catalog, exportingTable, keysComment, tuples, fkTableName,
true);
      }

      /**
***************
*** 6329,6341 ****
       *
       * @throws SQLException if a database access error occurs
       */
!     private void getImportKeyResults(String catalog, String importingTable,
!         String keysComment, List tuples) throws SQLException {
          getResultsImpl(catalog, importingTable, keysComment, tuples, null, false);
      }

!     private void getResultsImpl(String catalog, String table,
!         String keysComment, List tuples, String fkTableName, boolean isExport)
          throws SQLException {
          // keys will equal something like this:
          // (parent_service_id child_service_id) REFER ds/subservices(parent_service_id
child_service_id)
--- 6339,6357 ----
       *
       * @throws SQLException if a database access error occurs
       */
!     private void getImportKeyResults(String catalog,
!                                      String importingTable,
!                                      String keysComment,
!                                      List tuples) throws SQLException {
          getResultsImpl(catalog, importingTable, keysComment, tuples, null, false);
      }

!     private void getResultsImpl(String catalog,
!                                 String table,
!                                 String keysComment,
!                                 List tuples,
!                                 String fkTableName,
!                                 boolean isExport)
              throws SQLException {
          // keys will equal something like this:
          // (parent_service_id child_service_id) REFER ds/subservices(parent_service_id
child_service_id)
***************
*** 6343,6348 ****
--- 6359,6368 ----
          // 1: parse the opening parentheses to determine how many results there will be
          // 2: read in the schema name/table name
          // 3: parse the closing parentheses
+         int firstLeftParenIndex = keysComment.indexOf('(');
+         String constraintName = keysComment.substring(0, firstLeftParenIndex);
+         keysComment = keysComment.substring(firstLeftParenIndex, keysComment.length());
+
          StringTokenizer keyTokens = new StringTokenizer(keysComment.trim(),
                                                          "()", false);
          String localColumnNamesString = keyTokens.nextToken();
***************
*** 6388,6394 ****

              tuple[UPDATE_RULE] = s2b(Integer.toString(actions[1]));
              tuple[DELETE_RULE] = s2b(Integer.toString(actions[0]));
!             tuple[FK_NAME] = null; //not available from show table status
              tuple[PK_NAME] = null; //not available from show table status
              tuple[DEFERRABILITY] = s2b(Integer.toString(
                          java.sql.DatabaseMetaData.importedKeyNotDeferrable));
--- 6408,6414 ----

              tuple[UPDATE_RULE] = s2b(Integer.toString(actions[1]));
              tuple[DELETE_RULE] = s2b(Integer.toString(actions[0]));
!             tuple[FK_NAME] = s2b(constraintName);
              tuple[PK_NAME] = null; //not available from show table status
              tuple[DEFERRABILITY] = s2b(Integer.toString(
                      java.sql.DatabaseMetaData.importedKeyNotDeferrable));

Mark, please take a look at this. Thanks!

Parwinder, can you send your patch directly to me, mark@mysql.com? The bug system has
formatted it, so 'patch' doesn't like it :(

Thanks!

This is now fixed in the 3.0 and 3.1 tree. A combination of your patch, along with some
extra code to allow older MySQL servers without constraint names in 'SHOW CREATE TABLE'
fixed it. You can check out the fix in either 3.1 or 3.0 in this evening's snapshot builds
after 00:00 GMT 19-Jun-03 at http://mmmysql.sourceforge.net/snapshots/

Thanks for your bug report and patch!