Can I retrieve the impacted constraint(s) from a InvalidConstraintException?

Question

Two days ago, I have asked this question on how to modify deletion rules of foreign key constraints at runtime. That question has been answered, but now I would like to generalise my program:

Currently I have:

try
{ 
dt_MainTable1.Rows[0].Delete();
}
catch (Exception ex)
{
    if (MessageBox.Show("In order to avoid data corruption, not only " + 
                          " the required tupple will be deleted, " + 
                          "but also the ones in other tables, " + 
                          "referring to that tupple. " + 
                          "Is that what you want?",
                        "Warning",
                        MessageBoxButton.YesNo) == MessageBoxResult.Yes)
    {
        (ForeignKeyConstraint)(dt_SubTable1.Constraints[0]).DeleteRule 
          = Rule.Cascade;
        dt_MainTable1.Rows[0].Delete();
        (ForeignKeyConstraint)(dt_SubTable1.Constraints[0]).DeleteRule
          = Rule.None;
    }
}

... and this is what I would like to have: I try to do something (like a modification or a deletion), but I fall into a System.Data.InvalidConstraintException, based on a Constraint. Then I ask a question and based on the answer I turn the impacted Constraint's Deletion Rule or Modification Rule into None or Cascade. The big problem: can I retrieve the impacted Constraint from the generated System.Data.InvalidConstraintException? (I've been debugging and checking the properties of the System.Data.InvalidConstraintException, but I didn't find anything), just to show what I want (see the ex.FindRelevantConstraints() method):

try
{ 
LaunchDeleteAction(...);
}
catch (Exception ex)
{
    if (MessageBox.Show("In order to avoid data corruption, not only " + 
                          " the required tupple will be deleted, " + 
                          "but also the ones in other tables, " + 
                          "referring to that tupple. " + 
                          "Is that what you want?",
                        "Warning",
                        MessageBoxButton.YesNo) == MessageBoxResult.Yes)
    {
        for each (ForeignKeyConstraint C in ex.FindRelevantConstraints()   <===
          C.DeleteRule = Rule.Cascade;
        LaunchDeleteAction(...);
        for each (ForeignKeyConstraint C in ex.FindRelevantConstraints()   <===
          C.DeleteRule = Rule.None;
    }
}

Edit after some more investigation

Meanwhile I've found out that the Exception, ex, contains the name of the corresponding Constraint, as you can see in following watch window excerpt:

ex.Message : Cannot delete this row because constraints are enforced on relation Constraint1, and deleting this row will strand child rows.

I mean, it can't possibly be the intention to parse the Exception's message in order to get a reference to the Constraint object I'm looking for?

Desperate edit
In the meantime, I've decided to give special names to the Constraint objects I'm creating, like you can see here:

dt_SubTable1.Constraints.Add(
  new ForeignKeyConstraint("|FKC|MainTable.SubId|SubTable.Id|", 
                           dt_SubTable.Columns["Id"],
                           dt_MainTable.Columns["SubId"])
    { DeleteRule = Rule.None, 
      UpdateRule = Rule.None });

As you can see, I can use the pipe character for separating the table names and the dots for finding the column names, but that's really a desperate idea :-)

Minor point: "tuple", not "tupple". Less minor point: it sort of looks like you're allowing a user to decide whether the referential integrity of the system should be honoured. That's not really a user-level decision, it's a fundamental decision about your model. Also, it seems like you're only talking about constraints on data tables in client code. What's the relationship with SQL Server? Is the client going to try to save the results back to SQL? Do the constraints also exist in SQL? — allmhuran, Aug 30 '21 at 08:06
@allmhuran: you seem to misunderstand what I mean: I want, at all cost, to maintain data integrity: when a user wants to remove a tupple, then he gets a question, mentioning that the mentioned tuple is referred to by another table, and then the user can choose: either nothing happens (and the data integrity keeps intact) or all data (the mentioned tuple and the ones, referring to it) get deleted (which also keeps the data integrity intact). — Dominique, Aug 30 '21 at 08:15
Ah ok, so kindof like the behaviour you would see in MS Access. I would say that the solution here is not to handle this in an exception. It's not really an "exceptional" circumstance... you can check the constraints on the table before attempting the deletion, at which point you have all the information you need to prompt the user for confirmation. In my opinion it's also a bit smelly to put user interaction inside a catch block. — allmhuran, Aug 30 '21 at 08:23
@allmhuran: I see what you mean, but how can I get the constraint? As you can see in the first piece of code, I would like to remove a tuple from `dt_maintable`, but I fall into a constraint of `dt_subtable`. How can I find this? — Dominique, Aug 30 '21 at 08:30
Recursion baby! A `ForeignKeyConstraint` has a `Table` and `RelatedTable` property. I get that letting it fall through via an exception means getting this recursive evaluation "for free", but then, as you say, you've lost some context and can't get the information from the exception directly, so you'd have to do the recursion there anyway. Might as well do it up front. — allmhuran, Aug 30 '21 at 08:41

ste-fu · Accepted Answer · 2021-09-20T16:31:05.813

As a general rule it is better to test for the presence or absence of something that will cause an exception rather than to catch it.

Likewise handling user interaction in a catch block is not ideal and changing the data integrity constraints to perform the operation seems the wrong way to deal with it.

A better approach might be to:

Test for the existence of any related data in other tables
If found: present a message to the user
If user clicks proceed: delete the related data first, before finally deleting the parent record

If the relationship is a simple as the main and sub tables in the example you could try something like

if (dt_SubTable1.AsEnumerable.Any(r => s => r.Field("ForeignIdField") == MainId))

If you have a few DataTable instances you could iterate over them checking the Constraints properties in code.

foreach (var constraint in dt_Sub1.Contstraints)
{
    if (constraint is ForeignKeyConstraint && constraint.RelatedTable.TableName = dt_Main.TableName)
    // check for matching data
}

If you are trying a more generalized approach This question has several methods showing how you can find the foreign keys for a given table. You could create a method FindRelevantConstraints(string tableName) wrapping the system stored procedure sp_fkeys which would return both the table name and the name of the field.

This will allow you to create a query for any and all sub tables that depend on your primary table using the id value

> "As a general rule it is better to test for the presence or absence of something that will cause an exception rather than to catch it." Although this is true for c# and other languages, this isn't true for ALL languages: in python for example it is better to do go ahead and try the thing rather then to first test if it can be done. — whme, Sep 22 '21 at 09:44
Nitpicking, but have to point out, there are some notable exceptions to "test for it rather than catch", anything that changes asynchronously to the application, like connection timeouts, filesystem access, etc.. It may seem like a good idea to check to see if a file exists, but it's actually pretty useless because it could get deleted/moved immediately after you checked to see if it was there. — jrh, Sep 22 '21 at 16:33

score 3 · Answer 2 · answered Sep 21 '21 at 15:17

To directly answer the question in your title:

Can I retrieve the impacted constraint(s) from a InvalidConstraintException?

No, unfortunately, you can't.

According to the reference source, InvalidConstraintException does not store any useful information apart from the message text and (if applicable) an inner exception.

Some additional reading reveals that the System.Data methods pass all relevant information (e.g. the constraint name) to the ExceptionBuilder methods, but, alas, this information is only used to create the Exception message and then thrown away.

Can I retrieve the impacted constraint(s) from a InvalidConstraintException?

2 Answers2