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

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.

Comments (6)

  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. Lukas Eder

    Still seeing this problem in version 3.15.1

    Here's another report of the same issue: http://stackoverflow.com/q/41526928/521799. It includes a workaround:

    pragma table_info('work_on');
    

    The above query returns something like this:

    +----+----+----------+-------+----------+----+
    | 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|
    +----+----+----------+-------+----------+----+
    

    The pk column shows all the columns that are part of the primary key

  3. Lukas Eder

    In version 3.15.1, there is some logic in PrimaryKeyFinder, line 1891:

                    if (pkColumns == null) {
                        rs = stat.executeQuery("pragma table_info('" + escape(table) + "');");
                        while(rs.next()) {
                            if (rs.getBoolean(6))
                                pkColumns = new String[]{rs.getString(2)};
                        }
                    }
    

    This logic only takes the last primary key column into consideration. It is fallback logic for cases when both PK_NAMED_PATTERN and PK_UNNAMED_PATTERN fail to match. Of course, the reason why PK_UNNAMED_PATTERN fails to match is a bad regular expression:

        protected final static Pattern PK_UNNAMED_PATTERN =
            Pattern.compile(".*\\sPRIMARY\\s+KEY\\s+\\((.*?,+.*?)\\).*",
                Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
    

    The regex assumes there's at least one whitespace between KEY and the opening parenthesis. PK_NAMED_PATTERN also has this flaw.

    So, two remaining bugs:

    1. Both patterns are still wrong
    2. The query fetching columns from the pragma table_info() call overwrites columns and only reports the last column
  4. Log in to comment