DatabaseMetaData#getImportedKeys() returns wrong value for DELETE_RULE

[rdicroce]

Driver version or jar name

6.1.7

SQL Server version

13.0.4001.0

Client operating system

Windows 10

Java/JVM version

Oracle 8u144

Table schema

dbo

Problem description

DatabaseMetaData#getImportedKeys() returns UPDATE_RULE and DELETE_RULE columns with incorrect values.

Expected behavior and actual behavior

For a foreign key created with ON DELETE SET NULL, the JavaDoc says DELETE_RULE should be equal to the importedKeySetNull constant, which has a value of 2. But the driver reports 1.

The cause is that the data is obtained by executing sp_fkeys on the server. sp_fkeys only returns 0 or 1 for the delete and update actions (i.e. it does not provide detail at the level of granularity required). This also affects any other APIs (e.g. getExportedKeys()) that use sp_fkeys to get their data.

Possible solutions:

• The sys.foreign_keys table has more granular information. It could be joined to various other tables to return all of the relevant data.
• sp_fkeys could be changed. There was an issue about this but it was closed as Won't Fix.

[xiangyushawn]

Hello @rdicroce , thank you very much for reporting the error and providing the idea to fix it. We created a PR for it. If you have time, could you please take a look at the PR and any suggestions would be very appreciated too. Thank you!

[rdicroce]

The PR looks okay to me. I don't have any way of testing it because the code I was working on that exposed this problem has since been removed.

[xiangyushawn]

@rdicroce thank you. Let's wait for the PR to be reviewed and then we can close this issue after the PR is merged. Thank you 😄

[xiangyushawn]

Closing the issue since PR is merged.