Submitted by: @cincuranet

Duplicates DNET372

Votes: 1

Reported to me, privately by Van Den Berghe, Vincent.

I have been thinking about a way to illustrate the bug, such that the method might be included in a future test.
After some thinking, I came up with the function below:

    public void CheckForeignKey\(FbConnection connection\)
    \{
        foreach \(DataRow row in connection\.GetSchema\("ForeignKeys"\)\.Rows\)
        \{
            var constraintName = \(string\)row\["CONSTRAINT\_NAME"\];
            var tableName = \(string\)row\["TABLE\_NAME"\];
            var referencedTableName = \(string\)row\["REFERENCED\_TABLE\_NAME"\];

#⁠if true
// this fails:
foreach (DataRow pr in connection.GetSchema("ForeignKeyColumns", new string[] { null, null, tableName, constraintName }).Rows)
{
Debug.Assert(constraintName == (string)pr["CONSTRAINT_NAME"]);
Debug.Assert(tableName == (string)pr["TABLE_NAME"]);
Debug.Assert(referencedTableName == (string)pr["REFERENCED_TABLE_NAME"]);
}
#⁠else
// use this instead of connection.GetSchema("ForeignKeyColumns") until the bug has been corrected
string commandText = @"
SELECT null AS CONSTRAINT_CATALOG,null AS CONSTRAINT_SCHEMA,co.rdb$constraint_name AS CONSTRAINT_NAME,
null AS TABLE_CATALOG,null AS TABLE_SCHEMA,co.rdb$relation_name AS TABLE_NAME,
coidxseg.rdb$field_name AS COLUMN_NAME,
null as REFERENCED_TABLE_CATALOG,null as REFERENCED_TABLE_SCHEMA,refco.rdb$relation_name AS REFERENCED_TABLE_NAME,
refidxseg.rdb$field_name AS REFERENCED_COLUMN_NAME,
coidxseg.rdb$field_position AS ORDINAL_POSITION
FROM rdb$ref_constraints ref
INNER JOIN rdb$relation_constraints co ON ref.rdb$constraint_name=co.rdb$constraint_name
INNER JOIN rdb$index_segments coidxseg ON co.rdb$index_name=coidxseg.rdb$index_name
INNER JOIN rdb$relation_constraints refco ON ref.rdb$const_name_uq=refco.rdb$constraint_name
INNER JOIN rdb$index_segments refidxseg ON refco.rdb$index_name=refidxseg.rdb$index_name AND coidxseg.rdb$field_position=refidxseg.rdb$field_position
WHERE co.rdb$relation_name=@p0 AND co.rdb$constraint_name=@p1
ORDER BY ref.rdb$constraint_name,coidxseg.rdb$field_position"
;
// AND coidxseg.rdb$field_name = @p3",
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = commandText;
cmd.Parameters.Add(
"@p0", FbDbType.Char, 31).Value = tableName;
cmd.Parameters.Add(
"@p1", FbDbType.Char, 31).Value = constraintName;
<//cmd.Parameters.Add>("@p3", FbDbType.Char, 31).Value = fieldName;
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Debug.Assert(constraintName == reader.GetString(2 /*"CONSTRAINT_NAME"*/).TrimEnd());
Debug.Assert(tableName == reader.GetString(5 /*"TABLE_NAME"*/).TrimEnd());
Debug.Assert(referencedTableName == reader.GetString(9/*"REFERENCED_TABLE_NAME"*/).TrimEnd());
}
}
#⁠endif
}
}

This function will just loop through all foreign key definition, get the columns and perform some simple checks on the latter (the constraint name, table name and referenced table name must all match).
Executed as such, the function will fail, because of the bug in ForeignKeyColumns.
If you change the #⁠if true to #⁠if false, and use the corrected (and compatible) query, the function will succeed.
You can try it on the database model included in this e-mail (DatabaseCreation.sql).