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.

MGR: the Intelogist

About MGR: the Intelogist

SharePoint Server developer, turned Sitefinity developer, turned Angular developer, turned SharePoint Online consultant, turned Unily consultant, turned O365 consultant... Never a dull moment!

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

  1. Lino

    Nice post. I was checking continuously this blog and I am impressed! Very helpful information, specifically the last part 🙂 I care for such information much. I was seeking this certain info for a long time. Thank you and best of luck.

    • MGR

      Great news, Lino. Thanks for the comment.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 

This site uses Akismet to reduce spam. Learn how your comment data is processed.