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

Question

3.67/5 (2 votes)

See more:

.NET2.0

Hello

Can any body help me?

I bind a dataset to combobox. (siteName, siteId) At the run time I got following error.

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

If siteName have less char then no problem. But if it is more than 25+ then it raise error.

I set datatype of siteName varchar(MAX)

Record save in database but at the time fetching it raise error (i.e.(in load method) I am not written any code. Just regular binding data.

When I run query in query builder it is executed successfully at the point when we create dataset. How this can be possible if it have non-null, unique, or foreign-key constraints?

Posted 30-Nov-09 1:18am

archana_Kalyankar

Updated 15-Feb-19 0:15am

keetawat

v4

Add a Solution

Comments

Member 10367533 25-Apr-14 2:53am

I got same error msm, i have completed my project , finally create crystal report as fetch record from Emp_sal table, I got error when I change 2 field’s data type int to numeric (18,0),it was not reset when I change numeric to int data type……

11 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

joaquin_gs · Answer 1 · 2012-11-06T20:27:00

This error was also showing in my project, using Visual Studio 2010. I tried other solutions posted in other blogs, 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 have a .xsd object which I put there during the project design time (in 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 or stored procedure.

After I included all the constrained columns (not null, primary key, foreign key, etc) into my queries the problem disappeared completely.

Perhaps you don't need all the table columns to be present in the query/store procedure result, but because the constraints are still applied the error is shown if some constrained column does not appear in the result.

Hope this helps someone else.

keetawat · Answer 2 · 2010-01-18T23:00:00

Can you try this function

VB

 Public Function getSP2DataTable(ByVal spName As String, ByVal paramName() As String, ByVal paramValue() As String)
        On Error GoTo ErrorHappening
        Dim rtnDT As New DataTable
        Dim StartTime As Date = TimeOfDay
        Dim endTime As Date
        Using tmpDT As New DataTable
            Using myDB As New MySql.Data.MySqlClient.MySqlConnection(ConString)
                myDB.Open()
                Using myCMD As New MySql.Data.MySqlClient.MySqlCommand(spName, myDB)
                    myCMD.CommandType = CommandType.StoredProcedure
                    For i As Byte = 0 To paramName.GetUpperBound(0)
                        myCMD.Parameters.AddWithValue(paramName(i), paramValue(i))
                    Next
                    Dim myResult As IAsyncResult = myCMD.BeginExecuteReader
                    'Dim myRD As MySql.Data.MySqlClient.MySqlDataReader = myCMD.EndExecuteReader(myResult)
                    'tmpDT.Load(myRD, LoadOption.OverwriteChanges)
                    'myRD.Close()
                    Using myDataSet As New DataSet
                        Using myAdapter As New MySql.Data.MySqlClient.MySqlDataAdapter(myCMD)
                            myAdapter.Fill(myDataSet)
                            rtnDT = myDataSet.Tables(0)
                        End Using
                    End Using                    
endTime = TimeOfDay
                    frmMain.scriptTime.Text = "Last procedure name [" & spName & "] was executed in " & DateDiff(DateInterval.Second, StartTime, endTime) & " Second(s)."
                    myDB.Dispose()
                    myCMD.Dispose()
                End Using
            End Using
            Return rtnDT
        End Using
ErrorHappening:
        errorInfo()
    End Function

I was used the DataSet instread of DataReader.
Sorry for less English typing skill.

Abhishek Sur · Answer 3 · 2009-11-30T01:59:00

Solution 2

Dont know what exactly the problem is. But I guess, some foreign key violation has taken place.

Use Sql Server Profiler to find what exactly the insert statement it generates when you pass 25+ characters.

I think there should be some problem in insert statement when you make it 25+.

Posted 30-Nov-09 1:59am

Abhishek Sur

v2

www.Developerof.NET · Answer 4 · 2009-11-30T02:19:00

What i can make out from the error is that your table return some duplicate data for a column which is declared as primary key in your typed dataset. Remove the key attribute( not recommended since it actually defies the basic logic of a combo control) :doh: :doh: :doh: .

I would recommend you to verify your data first.:thumbsup:

Christian Graus · Answer 5 · 2009-11-30T01:31:00

Solution 1

There must be other code in there that is causing this. How do you do the data binding ? Because if the data is IN the database, it can't violate any constraint in the data layer. What code causes the error ?

Posted 30-Nov-09 1:31am

Christian Graus

Christian Graus · Answer 6 · 2009-11-30T02:00:00

Solution 3

Well, real programmers write data layers. Using that automatic rubbish, you're at the mercy of Microsoft and debugging becomes very difficult. I agree with the other reply, you need to some how work out what SQL is being generated and debug from there.

Posted 30-Nov-09 2:00am

Christian Graus

Frankie-10589807 · Answer 7 · 2019-02-15T00:15:00

For this Error is was able to go through it with the following sequence:

1. I Cleared the dataset before the call to fill the table adapter
Me.MyDataSet.Clear()
2. I set the Enforce Constraint to False also before the call to fill the table adapter
Me.MyDataSet.EnforceConstraints = False

And this cleared the error.
Though Constraints are meant to maintain data consistency and integrity, i am of the opinion that you exercise care when you do this and ensure the consistency of your data as well as the integrity.

Hope this helps some one out there.

Thanks

alireza2858 · Answer 8 · 2012-10-04T03:57:00

Solution 7

I had this problem, too
But
there is another way :
if you query contains some selection from two or more table having relations
select your data from you data base with "sp" then use it

Posted 4-Oct-12 3:57am

alireza2858

Comments

Member 14865162 3-Aug-20 23:45pm

what is sp?

DoingWork · Answer 9 · 2013-11-12T00:23:00

Solution 9

I had this problem, too.

Simply before calling

C#

myAdapter.Fill(myDataSet)

add this line of code

C#

myDataSet.Clear();

Posted 12-Nov-13 0:23am

DoingWork

Member 10524099 · Answer 10 · 2014-02-18T04:27:00

Solution 11

Hi Dears;
You Must Used These Code Before Fill DataTable :

var dt = new System.DataTable.DataTable();
dt.Clear();
dt.Rows.Clear();
dt.Columns.Clear();

Posted 18-Feb-14 4:27am

Member 10524099

Kent Lewis · Answer 11 · 2014-07-31T07:42:00

I was looking at this problem and it occurred to me that any data item not returned to a tableadapter from the associated stored procedure may cause this error. I deleted the column that was not being returned from the list of expected data items. I made another test and the error was gone. This error was do to the null value that resulted when the data item was not returned from the stored procedure.

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

11 solutions

Solution 8

Solution 6

Solution 2

Solution 4

Solution 1

Solution 3

Solution 16

Solution 7

Solution 9

Solution 11

Solution 12

Add your solution here

Preview 0

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

11 solutions

Solution 8

Solution 6

Solution 2

Solution 4

Solution 1

Solution 3

Solution 16

Solution 7

Solution 9

Solution 11

Solution 12

Add your solution here

Preview 0

Existing Members

...or Join us