DatabaseMetaData.getPrimaryKeys does not always return all primary keys for multi-line table definitions
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):
CREATE TABLE Table1 ( oneId INT NOT NULL, twoId INT NOT NULL, PRIMARY KEY (oneId,twoId) ); CREATE TABLE Table2 ( 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.