Debugging DataSet Constraint Errors

Introduction

If you've ever been frustrated by the vague error message that the DataSet code gives when trying to fill a DataTable with constraint errors, then maybe this will help.

Background 

If you've just started using DataSets and DataTables, you've learnt the basics and you've started to build your application, you start to understand that you could do things better.  So you add constraints to the DataSet designer, and try your code again.  You then get treated to the following error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.", in code you didn't even write! 

Using the Code 

To help diagnose the problem, try the following:

• Find the .Fill/.GetBy line that caused the problem.
• Select the line, and surround it with a try/catch block.
• Change the catch block so that it looks like:

catch (ConstraintException)
{
    DataRow[] rowErrors = this.YourDataSet.YourDataTable.GetErrors();

    System.Diagnostics.Debug.WriteLine("YourDataTable Errors:" 
        + rowErrors.Length);

    for (int i = 0; i < rowErrors.Length; i++)
    {
        System.Diagnostics.Debug.WriteLine(rowErrors[i].RowError);

        foreach (DataColumn col in rowErrors[i].GetColumnsInError())
        {
            System.Diagnostics.Debug.WriteLine(col.ColumnName 
                + ":" + rowErrors[i].GetColumnError(col));
        }
    }
}

• Now re-run your program.
• When you open the form, go back to Visual Studio and look at the Output window.
• You should see a count of the errors found, the row error and maybe a column error too.

One of the most common issues will be the ordering of the Fill statements.  You probably started with your main DataTable then added lookup tables etc afterwards.  Unfortunately .NET doesn't know that!, so when you call Fill on your main DataTable it tries to make sure that any Foriegn Key column in that DataTable is valid by checking the data in other DataTable.  The one you've not filled yet!  So if your still having problems re-order your Fill lines, so that the tables (the ones with the Primary Key side of constraint) are filled first.  Then move onto your main tables, then finally any tables that reference the main.

Note that you might have to fill DataTables even when you have no intent of displaying their data on your form.  e.g. Your Main table as a person Foreign Key from your people table, and your people table has a  Foreign Key from your ShoeSize table.  You will need to fill the ShoeSize, then People, then Main to avoid the error.

Points of Interest

If you are certain you are not going to alter any of the data, then you can set the EnforceConstraints property of the DataSet to false.  This will stop the checks, but obviously you could loose the data integrity. 

History 

v1.0: 18/06/2012: Initial version.