Skip to content

[BUG] DatabaseMetaData#getImportedKeys() returns duplicate rows if multiple FKs have the same name #1091

Closed

Description

Driver version

7.2.2

SQL Server version

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)

Client Operating System

Windows 10 Enterprise 1803

JAVA/JVM version

11.0.3+7 AdoptOpenJDK Hotspot

Table schema

CREATE TABLE [s1].[PkTable1] (
    [col] INT PRIMARY KEY
);

CREATE TABLE [s1].[FkTable1] (
    [col] int,
    CONSTRAINT fk_DuplicateName FOREIGN KEY ([col]) REFERENCES [s1].[PkTable1]([col])
);

CREATE TABLE [s2].[PkTable2] (
    [col] INT PRIMARY KEY
);

CREATE TABLE [s2].[FkTable2] (
    [col] INT,
    CONSTRAINT fk_DuplicateName FOREIGN KEY ([col]) REFERENCES [s2].[PkTable2]([col])
);

Problem description

When foreign keys are defined with the same names but in different schemas DatabaseMetaData#getImportedKeys() returns a result set with duplicate rows.

Expected behaviour: the result set contains one row per foreign key column
Actual behaviour: the result set contains multiple duplicate rows per foreign key column

This appears to be caused by the join at the end of the query run by SQLServerDatabaseMetaData#executeSPFkeys() only joining on the FK name and not the schema. Appending AND schema_id(t.FKTABLE_OWNER) = s.schema_id to the join seems to fix it.

Reproduction code

try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
    ResultSet rs = conn.getMetaData().getImportedKeys("TestDB", "s1", "FkTable1");

    System.out.println("Results:");
    // Prints out 2 rows with the same data when the table only has 1 FK
    while (rs.next())
        System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%d\t%d\t%d\t%s\t%s\t%d\n",
                rs.getString("PKTABLE_CAT"), rs.getString("PKTABLE_SCHEM"), rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME"),
                rs.getString("FKTABLE_CAT"), rs.getString("FKTABLE_SCHEM"), rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
                rs.getShort("KEY_SEQ"), rs.getShort("UPDATE_RULE"), rs.getShort("DELETE_RULE"),
                rs.getString("FK_NAME"), rs.getString("PK_NAME"), rs.getShort("DEFERRABILITY")
        );
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Assignees

Labels

BugA bug in the driver. A high priority item that one can expect to be addressed quickly.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions