Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

[Luther Miller]

I know about "sqlwish". Is there an ADO.NET-Wish mailbox at Microsoft?

I've been building a complex data calculation engine that makes
liberal use of ADO.NET typed DataSets, among other things.

In the development process, I inevitably screw things up now and again
and get the infamous System.Data.ConstraintException.

Well, let me just vent a little here. This is the most useless
!@&*()!@ exception message on the planet. Hey, I've got an idea - why
not get rid of ALL the exceptions in the .NET Framework and leave just
"Exception" with a message like "An exception occured. Have fun trying
to find out why."?

When you have a DataSet with a dozen tables, as many relationships,
and multiple constraints, that's about how useful it is to see
"System.Data.ConstraintException: Failed to enable constraints. One or
more rows contain values violating non-null, unique, or foreign-key
constraints." There are literally hundreds of possible causes for this
exception, and figuring out what caused it can sometimes takes hours.
(One day I found my self commenting out relationships in the XML
source one-by-one until the Exception went away. Of course, I had to
comment out a ton of code that depended on those relationships, too...
that was FUN!)

I am reduced to checking my code into source control before even the
most minor of changes, lest I make two, or bravely, even three changes
to code at once and get the CEoD (ConstraintException of Death).

Oh yeah - constructive criticism is in order here. I think that
ADO.NET programmers around the world would all appreciate it if the
message could elaborate on exactly WHICH constraint was violated and
why. Did I have a null value in a non-null column? Did I have a
foreign key into a table where the record doesn't exist? What table?
What column? What record? A little help here would sure be nice!
Thanks in advance.

 

[Neil McKechnie]

Luther,

Here's something which may save you a lot of pain in future...

Try creating a dataset (untyped or typed), give it two tables (Table1 and
Table2). Give each table three columns (leave types as default). Then
create a relationship (and an implied constraint) between
Tables(0).Columns(0) and Tables(1).Columns(0). This will also result in a
primary key being allocated on Tables(0). You are now ready to add data.

Put the following code into a button_click event:
DataSet1.EnforceConstraints = False
DataSet1.Tables(0).Rows.Add(New Object() {"1", "Hello", "World"})
DataSet1.Tables(0).Rows.Add(New Object() {"2", "Hello", "World"})
DataSet1.Tables(0).Rows.Add(New Object() {"3", "Hello", "World"})
DataSet1.Tables(0).Rows.Add(New Object() {"3", "Hello", "World"})
DataSet1.Tables(1).Rows.Add(New Object() {"1", "Hello", "World"})
DataSet1.Tables(1).Rows.Add(New Object() {"4", "Hello", "World"})
DataGrid1.SetDataBinding(DataSet1, "")
Try
DataSet1.EnforceConstraints = True
Catch ex As Exception
MsgBox(ex.Message)
End Try

As you can see, this is adding two rows which violate constraints on the
table. The exception is displayed, but in the grid you can view the two
table's contents and the problematic rows are marked with a red exclamation
mark.

You can also inspect the tables programatically using the
DataTable.GetErrors method, which returns the rows in error.

Hope this helps,

Neil.

 

[Luther Miller]

Neil,

Thanks for the tip. There is no user interface in the engine I am
working on, but I could add one for testing. However, the
DataTable.GetErrors method sounds promising. The next time I run into
this, I may just have to create a method to walk through all the
tables in a DataSet and output the results of GetErrors.

-Luther