1. Taro L. Saito
  2. sqlite-jdbc
  3. Issues
Issue #107 resolved

DatabaseMetaData.getPrimaryKeys does not always return all primary keys for multi-line table definitions

Art Doler
created an issue

While implementing a DB compare tool, I noticed that some tables do not return all primary keys out of the DatabaseMetaData.getPrimaryKeys query, instead only returning a single key.

I have experimented with different table structures and can't pin down exactly what causes it to work successfully - it seems to fail more often than it works correctly, at least with the database I have.

Here's SQL for one table which returns only one PKey, and one which returns all PKeys (table1 and table2, respectively):

    oneId INT NOT NULL,
    twoId INT NOT NULL,
    PRIMARY KEY (oneId,twoId)

    oneId VARCHAR(16)  NULL,
    twoId INT  NULL,
    threeId INT  NULL,
    column1 VARCHAR(64)  NULL,
    column2 INTEGER  NULL,
    PRIMARY KEY (oneId,twoId,threeId)

I've attached this to the ticket as well for convenience, along with an actual SQLite DB file and a small program that should repro the issue.

Here's the output of the program on these tables:

Found PKey for table1: column 'twoid' with key seq '0'.
Found PKey for table2: column 'oneid' with key seq '0'.
Found PKey for table2: column 'threeid' with key seq '2'.
Found PKey for table2: column 'twoid' with key seq '1'.

Let me know if you need more information.

Comments (4)

  1. Stefan Zeiger

    Has this fix been released? I see the same symptoms (not all columns of a compound primary key reported through JDBC metadata API) in 3.8.6 and 3.8.7. It works for me in 3.7.2.

  2. Log in to comment