Error in a method findForeignKeys() of MS SQL schema

[SerjRamone]

In yii2/db/mssql/Schema.php on line 347 we see a [rc].[constraint_name]

SELECT
    [kcu1].[column_name] AS [fk_column_name],
    [kcu2].[table_name] AS [uq_table_name],
    [kcu2].[column_name] AS [uq_column_name]
FROM {$referentialConstraintsTableName} AS [rc]
JOIN {$keyColumnUsageTableName} AS [kcu1] ON
    [kcu1].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu1].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu1].[constraint_name] = [rc].[constraint_name]
JOIN {$keyColumnUsageTableName} AS [kcu2] ON
    [kcu2].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu2].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu2].[constraint_name] = [rc].[constraint_name] AND
    [kcu2].[ordinal_position] = [kcu1].[ordinal_position]
WHERE [kcu1].[table_name] = :tableName

This is a mistake.
Must be a [rc].[unique_constraint_name] for get a right foreign keys.

[o-rey]

I confirm.
This issue results in incorrect relations generated by Gii.

[samdark]

@o-rey can you confirm that the fix suggested is correct?

[o-rey]

Yes, at least for SQL Server 2014.

For example, if I have a table tbl_address with FK company_id pointing to tbl_company, the result of [kcu2].[constraint_name] = [rc].[constraint_name] would be

company_id  tbl_address company_id

This leads to incorrect relations generated.

Meanwhile the result of [kcu2].[constraint_name] = [rc].[unique_constraint_name] is

company_id  tbl_company id

which is correct.

[samdark]

Do you have a chance to test older MSSQL?

[o-rey]

@samdark SQL 2008 confirmed.

[samdark]

@o-rey thanks! I've created a pull request. Since I don't have proper MSSQL environment currently it would be great if you'll verify it's good to be merged.

[o-rey]

@samdark No, that's incorrect.
First join must not be changed.

Here's the correct query:

SELECT
    [kcu1].[column_name] AS [fk_column_name],
    [kcu2].[table_name] AS [uq_table_name],
    [kcu2].[column_name] AS [uq_column_name]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS [rc]
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS [kcu1] ON
    [kcu1].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu1].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu1].[constraint_name] = [rc].[constraint_name]
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS [kcu2] ON
    [kcu2].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu2].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu2].[constraint_name] = [rc].[unique_constraint_name] AND
    [kcu2].[ordinal_position] = [kcu1].[ordinal_position]
WHERE [kcu1].[table_name] = :tableName

[samdark]

Fixed. Thanks for reporting and verifying it.