metaData.getPrimaryKeys() returns a single row when the key is composite

Question

I have an issue with a compound primary key in JDBC using SQLite driver.

The getPrimaryKeys() method from a DatabaseMetaData object returns a single row when I have verified the key is actually a compound key consisting of two columns.

Does any one have any suggestions / alternatives how the true listing of primary keys can be retrieved?

Here is the code:

DatabaseMetaData meta = con.getMetaData();

ResultSet pks = meta.getPrimaryKeys(null, null, "work_on");
ResultSetMetaData rsmd = pks.getMetaData();
while(pks.next()) {
    for (int i = 1; i < rsmd.getColumnCount(); i++) {
        System.out.print(pks.getString(i) + " ");
    }
    System.out.println();
}

Answer 1

It seems you have run into this issue: https://bitbucket.org/xerial/sqlite-jdbc/issues/107/databasemetadatagetprimarykeys-does-not

Workaround for the current JDBC bug

The bug in the JDBC driver is a bad regular expression matching your SQL string. The regular expression expects at least one whitespace between the KEY keyword and the opening parenthesis. If you write this:

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key(s_id, p_id)
)

The primary key won't be reported correctly (because there's another bug in the fallback logic when the regular expression fails to match anything, that other bug results in only the last PK column to be reported). So, to work around this problem, you could carefully design your tables to always have this whitespace:

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key (s_id, p_id)
  --         ^ whitespace here!
)

Workaround by not using the JDBC API

You can always run this query here yourself (which is the JDBC driver's fallback query):

pragma table_info('work_on');

And then collect all those rows that have the pk flag set to true. For instance, the following table

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key(s_id, p_id)
)

... produces this output:

+----+----+----------+-------+----------+----+
| cid|name|type      |notnull|dflt_value|  pk|
+----+----+----------+-------+----------+----+
|   0|s_id|varchar(4)|      0|{null}    |   1|
|   1|p_id|varchar(4)|      0|{null}    |   2|
|   2|x   |varchar(4)|      0|{null}    |   0|
+----+----+----------+-------+----------+----+