queryForeignKeys returns incorrect fkTableName

[ataft]

The ds.discoverForeignKeys function, which uses queryForeignKeys from discovery.js, returns the pkTableName as both pkTableName and fkTableName on SQL Server 2016. The following query should return the correct results for fkTableName:

SELECT kcu.constraint_name AS "fkName", kcu.table_schema AS "fkOwner", t.name AS "fkTableName",
kcu.column_name AS "fkColumnName", kcu.ordinal_position AS "keySeq",
'PK' AS "pkName", ccu.table_schema AS "pkOwner",
ccu.table_name AS "pkTableName", ccu.column_name AS "pkColumnName"
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_schema = tc.constraint_schema AND ccu.constraint_name = tc.constraint_name
JOIN sys.foreign_keys AS f
ON f.name = ccu.constraint_name
JOIN sys.foreign_key_columns AS fc 
ON f.object_id = fc.constraint_object_id
JOIN sys.tables t 
ON t.object_id = fc.referenced_object_id
WHERE tc.constraint_type = 'FOREIGN KEY'

Here's the corresponding DDL that shows the foreign key constraint:

CREATE TABLE [dbo].[PAYMENTS](
    [PAYMENT_KEY] [int] IDENTITY(1,1) NOT NULL,
    [DRG Definition] [varchar](250) NOT NULL,
    [Provider Id] [varchar](50) NOT NULL,
    [Hospital Referral Region Description] [varchar](150) NOT NULL,
    [Total Discharges ] [numeric](18, 4) NULL,
    [Covered Charges] [numeric](18, 4) NULL,
    [Total Payments] [numeric](18, 4) NULL,
    [Medicare Payments] [numeric](18, 4) NULL,
    [Year] [int] NOT NULL,
 CONSTRAINT [PK_PAYMENTS] PRIMARY KEY CLUSTERED 
(
    [PAYMENT_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PAYMENTS]  WITH CHECK ADD  CONSTRAINT [FK_PAYMENTS_PROVIDERS] FOREIGN KEY([Provider Id])
REFERENCES [dbo].[PROVIDERS] ([Provider Id])

ALTER TABLE [dbo].[PAYMENTS] CHECK CONSTRAINT [FK_PAYMENTS_PROVIDERS]

[ataft]

The same is also true of the queryExportedForeignKeys function. fkTableName is the same as pkTableName on SQL Server 2016

[ataft]

Is there any update on this bug? Foreign keys are the only absolute way to know a relationship exists between two entities. Isn't this crucial to discovery?

[ataft]

Never mind, I wrote the correct query and created a pull request (https://github.com/strongloop/loopback-connector-mssql/pull/155) for this fix.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.