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

    Question

  • Hi, why do i keep getting (with my dataset):

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

    all over the place.  I found an article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataTableClassGetErrorsTopic.asp

    and come up with the following code:

    Dim myDataSet As New KIT

    Dim rowsInError() As Data.DataRow

    Dim myTable As Data.DataTable

    Dim i As Integer

    Dim myCol As Data.DataColumn

    For Each myTable In myDataSet.Tables

    ' Test if the table has errors. If not, skip it.

    If myTable.HasErrors Then

    ' Get an array of all rows with errors.

    rowsInError = myTable.GetErrors()

    ' Print the error of each column in each row.

    For i = 0 To rowsInError.GetUpperBound(0)

    For Each myCol In myTable.Columns

    Response.Write(myCol.ColumnName & " " & rowsInError(i).GetColumnError(myCol))

    Next

    ' Clear the row errors

    rowsInError(i).ClearErrors()

    Next i

    End If

    Next

    but nothing gets displayed, please don't say for me to go to the asp.net forum because they take ages to reply over there.  Appreciate the help.  Thanks

    Monday, July 10, 2006 2:51 PM

Answers

  • Hello,

    I had this error and it wasn't related with the DB constrains (at least in my case). I have an .xsd file with a GetRecord query that returns a group of records. One of the columns of that table was "nvarchar(512)" and in the middle of the project I needed to changed it to "nvarchar(MAX)".

    Everything worked fine until the user entered more than 512 on that field and we begin to get the famous error message "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    Solution:  Check all the MaxLength property of the columns in your DataTable.

    The column that I changed from "nvarchar(512)" to "nvarchar(MAX)" still had the 512 value on the MaxLength property so I changed to "-1" and it works!!.

    Hope this help someone too!!

    Tuesday, January 09, 2007 8:39 PM

All replies

  • From I can tell from the error message you have a primary key setup for you datatable.  The primary key must be unique. The error says you have 2 or more records with the same primary key.
    • Proposed as answer by Salimzadeh Friday, December 17, 2010 2:27 PM
    Monday, July 10, 2006 4:27 PM
  • I have had alot of issues with Visual Studio 2005 and Crystal Reports and this was one of them.  You can avoid the error by setting the EnforceConstraints property of your DataSet to False

    _SubDataSet2.EnforceConstraints = False

    Also if you are using an .xsd file,  check the properties and make sure the AllowDBNull property is set to False.

    Hope this helps!

    • Proposed as answer by Vijay Jadhav Tuesday, June 30, 2009 7:08 AM
    Tuesday, December 12, 2006 5:55 PM
  • Hello,

    I had this error and it wasn't related with the DB constrains (at least in my case). I have an .xsd file with a GetRecord query that returns a group of records. One of the columns of that table was "nvarchar(512)" and in the middle of the project I needed to changed it to "nvarchar(MAX)".

    Everything worked fine until the user entered more than 512 on that field and we begin to get the famous error message "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    Solution:  Check all the MaxLength property of the columns in your DataTable.

    The column that I changed from "nvarchar(512)" to "nvarchar(MAX)" still had the 512 value on the MaxLength property so I changed to "-1" and it works!!.

    Hope this help someone too!!

    Tuesday, January 09, 2007 8:39 PM
  • Searching for two ours and your tip made the solution. Columnlength!!
    Wednesday, January 17, 2007 10:28 AM
  • It worked for me.  Have been searching for a solution for this for ages.

    Thanks a lot Luis.

    Thursday, January 18, 2007 11:30 AM
  • Ken,

     

    While your response didn't seem to help this guy, it did resolve my issue.  I simply removed the the primary key that the TableAdapter created.  For me this is no big deal since the TA represents a simple view more or less as I am using it.

     

    Thanks.

    Chris

    Wednesday, May 02, 2007 3:43 PM
  • thanks for the help. I wish the error was more information. I could not have figured out this problem. Thanks again
    Friday, May 04, 2007 8:09 PM
  • I keep getting this error whenever I FILL (and it returns just one row) even though the data is unique (it has identity column)

    If I set the

    EnforceConstraints = false

    It will not guard me for the other table adapters because it is enforesed at the dataset level

     

    there must be something else behind this error.

    Saturday, May 12, 2007 3:23 PM
  • Thanks!  I wasted hours on this last night and knew I didn't have violating keys!  Turns out I had changed a field length in the db and it no longer matched the dataset.  Good call!
    • Proposed as answer by Ksheera Sagar Tuesday, March 27, 2012 2:35 PM
    Tuesday, November 06, 2007 2:55 PM
  •  Ŀụịs wrote:
    Hello,

    I had this error and it wasn't related with the DB constrains (at least in my case). I have an .xsd file with a GetRecord query that returns a group of records. One of the columns of that table was "nvarchar(512)" and in the middle of the project I needed to changed it to "nvarchar(MAX)".

    Everything worked fine until the user entered more than 512 on that field and we begin to get the famous error message "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    Solution:  Check all the MaxLength property of the columns in your DataTable.

    The column that I changed from "nvarchar(512)" to "nvarchar(MAX)" still had the 512 value on the MaxLength property so I changed to "-1" and it works!!.

    Hope this help someone too!!

     

    Hello,

     

    As I'm a beginner with SQL Server, I would like to know how to check all MaxLength property of columns in a database.

     

    Thaks for your help gentlmen. Smile

     

    Regards,

    blackjack

    Monday, February 11, 2008 11:01 AM
  • In the SQL Server database, right-click on the table and select 'Modify'.    Under the column heading "Datatype" you will see such things as varchar(50), etc.  the '50' can be changed to 'MAX' and it will give the max length. 

    Thursday, February 14, 2008 5:36 PM
  •  

    Thanks,

     

    From the error message I was not able to figure out what the problem is.

    I had changed the length of a non-null column.

    I was filtering the records and all the times the rows did not exceed the max length the records were displayed properly.

    When the filter criteria generated rows which exceeded the max length it gave me an error.

    Your post helped me figure it out.

    Friday, February 15, 2008 6:49 PM
  • Thanks for your help.

     

    I will try this on monday and give you a feedback.

     

    Regards,

    blackjack

    Friday, February 15, 2008 7:05 PM
  • I didn't see anyone mention this solution, but for me, I had to set the unique property of a FK field in the datatable to false. Somehow the code generator though this Foreign Key field was a primary key (probably becuase I'm talking to a Sybase DMBS) and turned that switch on.

    As soon as I changed that property, my problem immediately went away.

    Just make sure that your Typed Dataset matches the database design, or it will fail!

    GOOD LUCK!



    Tuesday, February 26, 2008 10:25 PM
  • It doesnt make sense to me because I do not know what is causing the problem but this is what i did and it helped me. I opened the DataSet the created a copy of the table adapter that was giving me problems. Then I viewed the dataset xsd and found the complexType node for the new table adapter and copied that entire node and pasted it in the place where the problem table adapter complexType was.
    Thursday, February 28, 2008 6:26 PM
  • I found a solution.
    You have to turn off the EnforceContraints in the outer data set class.
    For example, if use the DataSet Designer to create a ProductsDataSet set class with the nested type ProductsDataTable and the corresponding adapter ProductsDataSetTableAdapters.ProductsDataSetTableAdapter do the following:


    ProductsDataSet MyProductsDataSet= new ProductsDataSet();

    MyProductsDataSet.EnforceConstraints=false;

    ProductsDataSet.ProductsDataTable MyDataTable =  MyProductDataSet.Products;

    ProductsDataSetTableAdapters.ProductsTableAdapter adapter = new ProductsDataSetTableAdapters.ProductsTableAdapter();

    adapter.MyQuery(MyDataTable, QueryInput);

    Wednesday, April 30, 2008 11:18 PM
  • Thanx
    SubDataSet2.EnforceConstraints =
    False
    it works fine.

    Monday, May 05, 2008 9:25 AM
  • In my case the xsd file had created in 2 primary keys... all these coments really helped.
    Wednesday, May 07, 2008 5:04 AM
  • So, the real question here is:

    When the heck is Micro$oft going to fix the problem of the development studio not being able to update the xsd, and .cs files, when you make these changes?!  They build these files for you, when you include a database into your project, but they provide you with NO way to "rescan" the database to rebuild these files after changes.....typical poor workmanship of Micro$oft products.....
    Tuesday, June 03, 2008 7:44 PM
  • My problem also turned out to be relater to maxlength.

    So... how do I get the xsd file to re-synch with the database structure once I modify the structure.  So far, I've had to go into the xsd and make the changes manually... in this case to the max lengths of email fields.

    Randy
    Friday, June 27, 2008 6:53 AM
  • nice solution.. its works for meBig Smile
    Monday, November 17, 2008 11:44 AM
  • I thought i might mention some things here.  The designer is an ok tool and the strongly typed datasets that are created with drag and drop are not going to be perfect for all situations.  I can see why many like to use the designer for quick setup of certain things but if you really want to avoid all these problems then work with straight code.  You can do all this with dynamic t-sql that the behind the scenes code the designer generates for you.  It is the same basic code and it has no overhead on it when you do it yourself.  You can easily enforce your own constraints in a way that works for you and still have it function in the way it is supposed to.  Enforcing constraints such as non - null or unique values or related id's for foreign keys is no more than doing a few checks to make sure your data is intact before executing the commands to the database. 

     

    I have been helping a college student with a program he has been developing in the desinger and there is so much excess objects in the designer that it is very difficult to make out which objects go with what.  And the controls are difficult to match to their bound objects as well.  It can be done but in the end it takes more time to figure out why something won't work and have to redo things after making changes than it would be to just code it out.  I don't think the designer was ever meant to replace code it is just a tool to help you get started quickly.  And it seems to be a tool that is really designed for the beginner. 

     

    Anyway, just some thoughts that might help some of you.  If you are new to database or working with database in code and want more info on how to work with code to avoid these kinds of problems i have a database tutorial on my website.  I also have many code samples for database and just about anything you need.  There are plenty of examples in the forum here as well if you do some searching.  My tutorial gives more details about the objects themselves also.

     

    Jeff - www.srsoft.us

     

    Monday, November 17, 2008 3:21 PM
  • HI,
    I was getting this error message becaue in my dataset, in one of the table adapters, one of the query was returning data different than the main query. E.g. Main query was returning datacolumn1 and datacolumn2 while my second query was returning just datacolumn1. I changed the second query to return the datacolumn2 but not use it and it solved the problem.

    Cheers!
    Dev
    Wednesday, February 04, 2009 8:41 PM
  • Thank you so much for this solution.  I rebuilt the same page three times and THEN I found your answer.  I wish I would have met you a few hours sooner. :-)
    Saturday, March 07, 2009 2:25 AM
  • Check out the post at: http://www.rickiswright.com/Home/TableAdapterWhitepaper/tabid/56/Default.aspx
    The problem is easy to fix and the post is about 3 minutes of explanation.

    Rick Wright
    Sunday, May 10, 2009 9:58 PM
  • Sunday, May 10, 2009 9:59 PM
  • Actually, the solution lies in using partial classes. That solves all the problems you are complaining about.
    See:
    http://www.rickiswright.com/Home/TableAdapterWhitepaper/tabid/56/Default.aspx
    For other ways to Fill a TableAdapter data table.
    Rick Wright
    • Proposed as answer by typetrice Tuesday, May 12, 2009 2:06 PM
    Sunday, May 10, 2009 10:01 PM
  • This issue can be related to calling a SELECT function from a dataset tableadapter that does not include all fields that cannot be null.
    In other words if you have a field that does not allow nulls then it must be in the SELECT field list for the function that is called. The 'enforce constraints' error seems to be related trying to create the result object and if a field cannot be null and it does not appear in the Selected fields then the error is thrown.
    • Proposed as answer by typetrice Tuesday, May 12, 2009 2:14 PM
    Tuesday, May 12, 2009 2:14 PM
  • typetrice is correct, you must select all non-null fields in your select statement.

    This error can also occur if there is a leftover table relationship still in the dataset.  Even if you redesign the table, you must go through and rebuild the dataset.  I've just been refreshing each query in my table adapters by hand.  Sometimes I find that I have to delete the table entirely and drag it over again.
    Tuesday, May 12, 2009 10:14 PM
  • That is total and utter BS! You're right, and I had to include columns that have nothing to do with what I needed to get done. Idiocy!
    Wednesday, May 20, 2009 4:16 AM
  • Hello,

           I am facing same problem, in which i have one field and data type is nvarchar(MAX) and i am getting same error,according to your

    solution i have set the MaxLengh property to "-1"  <xs:maxLength value="-1" /> but after i m getting following error,please help me to

    short out my problem.

    Error :

    Unable to convert input xml file content to a DataSet. The MaxLength constraining facet is invalid - Value '-1' was either too large or too small for NonNegativeInteger. Value '-1' was either too large or too small for NonNegativeInteger.   

    Thanks,

    Kaushal.
    Tuesday, June 02, 2009 6:45 AM
  • Hi SarahBurgess,

    I had a same error. Here is my snippet :

            private void FillDataSet(DataSet oData, Stream oXMLStream)
            {
                try
                {
                    Sis oSis = new Sis();
                    oSis.EnforceConstraints = false;
                    oSis.ReadXml(oXMLStream);
                  
                    DataRow oRow = oData.Tables[0].Rows[0];
                    oRow["AcademicDetails"] = oSis.Student[0].AcademicDetails;
                    oRow["Action"] = oSis.Student[0].Action;
                    oRow["UserName"] = oSis.Student[0].GetAuthenticationRows()[0].UserName;
                    oRow["PassWord"] = oSis.Student[0].GetAuthenticationRows()[0].Password;
                    oRow["FirstName"] = oSis.Student[0].GetPersonalDetailsRows()[0].FirstName;
                    oRow["MiddleName"] = oSis.Student[0].GetPersonalDetailsRows()[0].MiddleName;
                    oRow["LastName"] = oSis.Student[0].GetPersonalDetailsRows()[0].LastName;              
                }
    
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    Where Sis is the Xsd.

    In above snippet, I have set .EnforceConstraints = false;, and it works Gr8!

    Really nice one, thanks once again.

    Thanks.


    • Edited by Vijay Jadhav Tuesday, June 30, 2009 9:58 AM
    • Proposed as answer by Simon Goodson Tuesday, August 04, 2009 10:06 AM
    Tuesday, June 30, 2009 7:06 AM
  • I had this issue and for me it was a problem with the XSD.  I'd added two new columns to a table and all of the get statements had been updated automatically to include the two new columns except one.  This was a rather complicated bit of SQL that involved a UNION.  As soon as I included the missing columns in to the get statement (so the columns it was pulling out matched all of the others on the table adapter) the problem was resolved.
    Tuesday, August 04, 2009 10:10 AM
  • Congratulation Simon.
    Tuesday, August 04, 2009 10:22 AM
  • Hello,

    I had this error and it wasn't related with the DB constrains (at least in my case). I have an .xsd file with a GetRecord query that returns a group of records. One of the columns of that table was "nvarchar(512)" and in the middle of the project I needed to changed it to "nvarchar(MAX)".

    Everything worked fine until the user entered more than 512 on that field and we begin to get the famous error message "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    Solution:  Check all the MaxLength property of the columns in your DataTable.

    The column that I changed from "nvarchar(512)" to "nvarchar(MAX)" still had the 512 value on the MaxLength property so I changed to "-1" and it works!!.

    Hope this help someone too!!

    This solved my problem, thanks.
    Friday, September 25, 2009 3:11 AM
  • Thanks!!! That really solved my problem!  It wasn't an obvious solution so I'm glad to you got it before I did.
    Monday, September 28, 2009 9:12 PM
  • Thanks,

    By Setting

    EnforceConstraints = false , It works fine for me.

    Friday, December 04, 2009 8:29 AM
  • Glancing over some of your posts, i think the fix at this point seems to be to disable the constraints which are put in place for a reason.  they are not enforced by mistake, they are put in place based on your database schema (design) being setup a certain way.

    I am not saying don't remove the enforced contstraints and any of you that know me, know that many times i am a "make it work" developer, but i am saying it is worth your while to investigate a little further into why your issue is causing you to have to override the enforced constraints. 

    For instance, when you make a change to your database, it will not automatically update your drag and drop created dataset, the two are not directly connected.  This is something you have to do on your own.  There are some options to refresh the dataset but from what i understand this will not update your controls on your forms which have been dropped onto your forms from the datasources explorer from your dataaset.

    So the point is, just to keep in mind the designer tools are not the "end all" solution to your programming project.  You can't expect it to do everything for you.

    I used to think the drag and drop was for beginners, and in many ways it is, but really only for simple setups.  If you need to make complex functionality with your dataset, the designer can be used but a beginner is not going to be able to make it work easily, and many times, not correctly at all.

    The drag and drop for me was something that gave me a spark of hope when i first started programming with database because instantly i was able to see the data work, but it turned out to be a nightmare as soon as i wanted to increase the complexity of it.

    So in short, for simple setups with a few related table, the designer is good, but if you plan on going further than what it does automatically, or don't understand the idea of disconnected data then you need to look at working with code to really understand how to use the designer.  In the end you will find that the designer is not as great as you thought it was at first.  Atleast not for complex data needs.

    If you want to understand more about relationships, primary keys, disconnected data model, etc... see the new parts in my turorial series (Widgets Inc Project) on my website.

    Hope it helps you
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    Friday, December 04, 2009 1:43 PM
  • Thanks Luis, Your solution definitely saved me.  I know that Ken Tuckers solution is a good place to look, and it is also the direction pointed to by the error message and Visual Studio Help.  However, for many of us, the "unique primary key" solution is a false diversion. 

    The maxlength solution solves the problem in many cases.  If the size of data colums are changed during project development, it seems you have to go in and manually update the dataset designer file.  (it does not happen automatically during compile)

    Thanks for the solution.
    Saturday, January 02, 2010 7:43 PM
  • I'm kind of late to this thread, but I had this problem too and it was related to the length of a field that I changed. I just opened the xsd code and manually updated the xs:maxLength value for the field that I had changed
    Wednesday, January 27, 2010 6:31 PM
  • Hi there,

    I have this error on my app and tryed everything that i readed on this page and didn't work.

    Then I deiced to review all the PK on the dataTable and guess what I found? While in the graphic mode the dataTable show a PK with two fields, the PK that I was defined, when I do right click on the PK and select EditKey only one field was selected and it not was as PK.

     

    So, guys, review this on yours dataTable, does'nt take with the key that VS show on the Design view of your data table.

     

    I hope this Help.

    Sunday, March 21, 2010 9:10 PM
  • I found another error in Visual Studio related to this problem:

    I you define a DataTable in your data set that is a join between two tables and only select out a portion of the primary key (e.g. the key consists of 2 columns, but only one is returned in the resulting dataset), Visual studio will helpfully mark the partial key as the primary key for the DataTable.

    This results in the inevitable "Failed to enable constraints" error when you try to fill the DataTable.

    And as an added bonus, Visual Studio will not allow you to change the status of this key column to non-unique.  I had to hand-edit the .xsd to remove the constraint.  Hopefully this will be fixed in VS2010...

    • Proposed as answer by Auction God Thursday, June 24, 2010 12:58 PM
    Thursday, June 24, 2010 12:57 PM
  • Thanks a lot Luis!!!

    It is working fine now.

     

    Regards,

    G Srinivasan

    Wednesday, August 18, 2010 4:28 PM
  • Yes, excellent - even three years later - this was the issue for me and saved my ... (you know what).  In my file: '<DBNAME>DataSet.Designer.VB' file - it contained the max lengths for my data set, of course, along with the Microsoft scolding me with their " Changes to this file may cause incorrect behavior and will be lost if the code is regenerated..." so they warn me not to edit the file, then produce a buggy environment where I HAVE TO EDIT the file or my app fails to work. 

       As far as I can tell, there is not any circumstances where the "the code is regenerated", I've had to edit that file all the time (add columns, delete columns), no one in Redmond seemed inclined to have this code actually be regenerated, although I do appreciate the fact that it was created in the first place, it's 100,000 lines of code in my app, so I'm kinda glad I didn't have to hand edit that!

       But yes, I tweaked the numerious columns/field that I had to change size of in the D/B tables, thinking "certainly all that code in the DataSet designer, it must certainly know how to see the SQL data tables field lengths and adjust itself to match up with it, but NOooo...

    Anyhow, thanks for the valuable help & suggestions.  This saved me.

    Best Regards All

    Sunday, September 12, 2010 3:02 AM
  • Hello Luis,

     

    You're da man! I see that even recent posts still appreciate your suggestion as the one that gets people back on track.

    What intrigues me though is this: why is the length of the fieldproperty changed in the XSD file? I generate the XSD more or less by getting information fom a database I connect to. Most of the fieldlenghts are "inherites" from the database, but some of them are simply set to a value of "10". I konw I did not change the structure or definition of the datatables after the XSD was put up.

     

    Anyone with suggestions ?

     

    Peter


    "Every misfortune is a new challenge"
    Tuesday, September 14, 2010 10:06 AM
  • Works for me too!

     

    thanx

    Monday, September 20, 2010 1:49 PM
  • Thanks! it helped me to resolve a gr8 headache ... Thank you very much!!
    Sunday, September 26, 2010 1:25 PM
  • i also have this error! but none of the previous solutions works for me..

    I tried to use the EnforceConstraints = false, i also tried to look and edit the xsd file, but with no results..

    the maxlength solution is not useful for mw beacause in my table i have 3 int columns and its maxlength is -1 by default..

    Primary key in this table is made by 2 columns (the reference with mysql database is correct)

    the exception cames out in calling a select query method like this:

     

    SELECT        SUM(responses) AS summ, response_code AS resp_code
    FROM            ftp_response_code
    WHERE        (response_code > 0)
    GROUP BY response_code

     

    table is made by    dump (int), response_code (int), responses (int)      primary key is made by dump and response_code

     

    in data preview i can see the right result of this query..

     

    Can someone help me?

    Tuesday, November 16, 2010 9:08 PM
  • Please check you property window for wat you have to assign. Like AllowDbNull, NullValue etc.

    i had same prob, cleared by checking this..

     

    Hope it may help you

    Sunday, December 19, 2010 6:53 AM
  • In your case it was the maxlength property that was the problem. In some other cases it can be null values etc. One easy way to find out exactly which field in the dataset schema is causing the problem is to check the errors on each row in every table in the typed dataset. It can be done quickly in a watch window. This blog post explains the procedure.
    http://www.fransson.net
    Tuesday, December 21, 2010 12:50 PM
  • Thanks mate, Worked like a charm!
    Monday, December 27, 2010 9:44 PM
  •  

    This problem could be resolved quickly and efficiently if Microsoft would only create a specific error message instead of the generic "Something is wrong" message.  For example, name the row, column and the problem.  In my case, everything is correct as far as I can tell, so when I look at my 330 rows of 20 columns each, I already know that every one is correct.  So, without a specific error message, I can only assume that it's a bug in Microsoft code. 

     

    From the solutions listed on this thread, I can see that most people are having to turn off some or all of the constraints, because they don't really know what or where the problem is.  A specific error message would save all of this effort. 

     

     


    rwg
    Friday, January 21, 2011 10:00 PM
  • Thanks a lot ! this worked !!!

    However, this is one more pathetic error message from MS.

    Monday, May 16, 2011 7:47 PM
  • AVOID "Select * from tablename"

     

    I had a webservice that connected to an SQL Express 2005 without any difficulties.

    Then when I connected it to an SQL Server 2008 I got the error

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

     

    To correct this I avoided using the asterisks in my select statement i.e. "Select * from tablename"

    I replaced it with an explicit select that includes all of the column names that I needed like this:

    "Select tablename.fieldname1, tablename.fieldname2 from tablename

    ----

    Good luck.

    • Proposed as answer by cmadore Friday, June 10, 2011 7:08 PM
    Friday, June 10, 2011 7:05 PM
  • THANK YOU!  This 4-year-old post is a persistent life-saver!  I had enlarged the Length of a few columns in a particular SQL 2008 table.  Of course, the DataSet doesn't know I did that and, as a result, VS2010 threw the infamous error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints"

    For me, the solution was merely to:

    1. Double-click the DataSet's XSD
    2. Review, one by one, each column in the DS and ensure the MaxLength was the SAME as its counterpart in the physical table.

    Upon doing so, I discovered two columns in the DS whose MaxLength was SHORTER than what was defined in the table column.  I manually corrected it, by typing over the value, then saving the DS.

    I (hopefully) won't make that oversight ever again.


    Saturday, July 09, 2011 4:12 AM
  • Great post & solutions. Helped a ton. Thanks.
    Thursday, October 13, 2011 8:50 PM
  • I also had similar problem. I was able to solve it by naming the DataColumn similar to the name on what was in the table. This post really helped me. - Thanks!
    Friday, October 28, 2011 2:56 AM
  • I had the same problem. All I did delete the table adapter and recreated it and solved the problem. One thing to remember is consistance of data bype in talbe  and Stored procedure. This is what i did and worked for me. 
    Jashim
    Monday, December 12, 2011 8:44 PM
  • I had this problem using an XSD in .NET. I had defined a query on a table via a table adapter and using a stored procedure. I did not return any more than one column via the sproc.

    When I returned dummy fields for the non-null fields (firstname, lastname) as well as the field I was interested in, managerName, it worked perfectly.

    Friday, December 16, 2011 7:45 PM
  • Yes its helps me a lot.

    Thnaks.

    Thursday, January 12, 2012 6:56 AM
  • Well i had that issue, i solved by doing this:

    First solution was:

    this.EnforceConstraints = false;  /* this line is in yout Dataset.Designer.cs only change from true to False */

    My Second Tricky Solution was:

    The first time throws the "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." exception but the second time it does the work very well, i ll wait for your feed backs

    try{
        sqlDataAdapter.Fill(DatasetObject);
    }
    catch{
    }
    sqlDataAdapter.Fill(DatasetObject);

    [edit]The problem was a column maxlen different than DataBase[/edit] ->SOlution 3


    • Proposed as answer by fraymxl Wednesday, February 15, 2012 7:37 PM
    • Edited by fraymxl Wednesday, February 15, 2012 7:58 PM
    Wednesday, February 15, 2012 6:37 PM
  • I had the same issue, and this post is just what I needed.

    I had increased the size of a VarChar(10) field to VarChar(12) in SQLExpress to allow longer text.
    I got the same erroneous error.  The DB constraints were OK.  I could select and preview data OK. I disabled constraints to no effect.

    However, the MaxLength in the TableAdapter properties didnt get updated, so I thought I had a completly different problem to what was really the problem - Without your post I would have no hair by now.  Thanks a lot!

    Wednesday, February 29, 2012 3:32 PM
  • I had the same issue because of the String to Integer change on column type. Thanks for pointing out.
    Tuesday, March 27, 2012 2:36 PM
  • HI all

    If you Working With datatable Only 

    Just Clear the Datatable before you Load it again with the Following

     DataTable1.Columns.Clear;
     Datatable1.Rows.Clear;
     Datatable1.Clear;

    thanks;

    Sameh Senosi

    BI,Software Developer

    My YouTube Channel


    • Edited by samehsenosi Tuesday, April 24, 2012 9:34 AM
    Tuesday, April 24, 2012 9:32 AM
  • This error was also showing in my project. I tried all the proposed solutions posted here, but no luck at all because the problem had nothing to do with fields size, table key fields definition, constraints or the EnforceConstraints dataset variable.

    In my case I also have a .xsd object which I put there during the project design time (the Data Access Layer). As you drag your database table objects into the Dataset visual item, it reads each table definition from the underlying database and copies the constraints into the Dataset object exactly as you defined them when you created the tables in your database (SQL Server 2008 R2 in my case). This means that every table column created with the constraint of "not null" or "foreign key" must also be present in the result of your SQL statement of stored procedure.

    After I included all the key columns and the columns defined as "not null" into my queries the problem disappeared completely.

    Hope this helps someone else.

    Wednesday, November 07, 2012 4:58 AM
  • Thank you very much, the suggested solved my problem!!

    great_scandinavian

    Sunday, December 16, 2012 9:55 AM