How to: Troubleshoot “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”

Working with DataSets, you might face this error message that will keep you from using code that you knew was working just fine. There may be many reasons for this error message to pop up on your screen, and most of them have been talked about extensively in other blog posts. A search online will give you more than a handful of reasons why you might get this message. However, it’d be interesting to discuss why this error appears when you execute some SQL Query when, if you were to execture the same SQL Query inside your SQL Server, it would work just fine.

This has to do with the fact that the .xsd in your visual studio is built once. When you first configure your  Table Adapters and Data Tables. Once they have been configured, they’re strongly typed and ready to be used. Unfortunately, they do not keep a live connection to the actual database. What that means is that your .xsd and your SQL DB, more often than not, will be out of sync if/when you decide to change something in your DB.

You need to make sure that the .xsd represents your SQL correctly. In my case, I had erased a column from a table in my SQL DB and although I did go back to the .xsd and re-configured the affected Data Table, I failed to notice that, although the SQL Table had four columns, the .xsd Data Table was still looking for a fifth one.

You can click on the left side of the columns that make up your .xsd Data Table to select the column and then easily delete it with a press of the delete key. That should fix it.

Another solution found online includes the checking of the column properties inside your .xsd file to make sure that they match the column properties of your actual SQL DB table column.