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")
);
}
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_idto the join seems to fix it.Reproduction code