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

Question

I make an outer join and executed successfully in the informix database but I get the following exception in my code:

DataTable dt = TeachingLoadDAL.GetCoursesWithEvalState(i, bat);

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

I know the problem, but I don't know how to fix it.

The second table I make the outer join on contains a composite primary key which are null in the previous outer join query.

EDIT:

    SELECT UNIQUE a.crs_e,  a.crs_e  || '/ ' || a.crst crs_name, b.period,
           b.crscls, c.crsday, c.from_lect, c.to_lect,
           c.to_lect - c.from_lect + 1 Subtraction, c.lect_kind, e.eval, e.batch_no,
           e.crsnum, e.lect_code, e.prof_course
    FROM rlm1course a, rfc14crsgrp b, ckj1table c, mnltablelectev d,
         OUTER(cc1assiscrseval e)  
    WHERE a.crsnum = b.crsnum 
    AND b.crsnum = c.crsnum 
    AND b.crscls = c.crscls 
    AND b.batch_no = c.batch_no 
    AND c.serial_key = d.serial_key  
    AND c.crsnum = e.crsnum  
    AND c.batch_no = e.batch_no  
    AND d.lect_code= e.lect_code 
    AND d.lect_code = .... 
    AND b.batch_no = ....

The problem happens with the table cc1assiscrseval. The primary key is (batch_no, crsnum, lect_code).

How to fix this problem?

---

EDIT:

According to @PaulStock advice: I do what he said, and i get:

? dt.GetErrors()[0] {System.Data.DataRow} HasErrors: true ItemArray: {object[10]} RowError: "Column 'eval' does not allow DBNull.Value."

So I solve my problem by replacing e.eval to ,NVL (e.eval,'') eval.and this solves my problem. Thanks a lot.

Solution 1

This problem is usually caused by one of the following

• null values being returned for columns not set to AllowDBNull
• duplicate rows being returned with the same primary key.
• a mismatch in column definition (e.g. size of char fields) between the database and the dataset

Try running your query natively and look at the results, if the resultset is not too large. If you've eliminated null values, then my guess is that the primary key columns is being duplicated.

Or, to see the exact error, you can manually add a Try/Catch block to the generated code like so and then breaking when the exception is raised:

Then within the command window, call GetErrors method on the table getting the error.
For C#, the command would be ? dataTable.GetErrors()
For VB, the command is ? dataTable.GetErrors

This will show you all datarows which have an error. You can get then look at the RowError for each of these, which should tell you the column that's invalid along with the problem. So, to see the error of the first datarow in error the command is:
? dataTable.GetErrors(0).RowError
or in C# it would be ? dataTable.GetErrors()[0].RowError

Solution 2

You can disable the constraints on the dataset. It will allow you to identify bad data and help resolve the issue.

e.g.

dataset.TableA.Clear();
dataset.EnforceConstraints = false;
dataAdapter1.daTableA.Fill(dataset, TableA");

The fill method might be slightly different for you.

Solution 3

This will find all rows in the table that have errors, print out the row's primary key and the error that occurred on that row...

This is in C#, but converting it to VB should not be hard.

 foreach (DataRow dr in dataTable)
 {
   if (dr.HasErrors)
     {
        Debug.Write("Row ");
        foreach (DataColumn dc in dataTable.PKColumns)
          Debug.Write(dc.ColumnName + ": '" + dr.ItemArray[dc.Ordinal] + "', ");
        Debug.WriteLine(" has error: " + dr.RowError);
     }
  }

Oops - sorry PKColumns is something I added when I extended DataTable that tells me all the columns that make up the primary key of the DataTable. If you know the Primary Key columns in your datatable you can loop through them here. In my case, since all my datatables know their PK cols I can write debug for these errors automatically for all tables.

The output looks like this:

Row FIRST_NAME: 'HOMER', LAST_NAME: 'SIMPSON', MIDDLE_NAME: 'J',  has error: Column 'HAIR_COLOR' does not allow DBNull.Value.

If you're confused about the PKColumns section above - this prints out column names and values, and is not necessary, but adds helpful troubleshooting info for identifying which column values may be causing the issue. Removing this section and keeping the rest will still print the SQLite error being generated, which will note the column that has the problem.

Solution 4

• Ensure the fields named in the table adapter query match those in the query you have defined. The DAL does not seem to like mismatches. This will typically happen to your sprocs and queries after you add a new field to a table.

• If you have changed the length of a varchar field in the database and the XML contained in the XSS file has not picked it up, find the field name and attribute definition in the XML and change it manually.

• Remove primary keys from select lists in table adapters if they are not related to the data being returned.

• Run your query in SQL Management Studio and ensure there are not duplicate records being returned. Duplicate records can generate duplicate primary keys which will cause this error.

• SQL unions can spell trouble. I modified one table adapter by adding a ‘please select an employee’ record preceding the others. For the other fields I provided dummy data including, for example, strings of length one. The DAL inferred the schema from that initial record. Records following with strings of length 12 failed.

Solution 5

This worked for me, source: here

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!!.

View more solutions

Comments

[Anyname Donotcare]

When I remove ,e.eval,e.batch_no,e.crsnum,e.lect_code,e.prof_course From the query every thing goes okay. what is the problem please .

[Brain2000]

There is also a bug in ADO.NET where a "non-unique clustered index" will create an erroneous Data.UniqueConstraint item on the DataTable.

[Anyname Donotcare]

I put allow null = true for all columns in this table but in vain.

[Anyname Donotcare]

Thanks a lot . >? dt.GetErrors()[0] {System.Data.DataRow} HasErrors: true ItemArray: {object[10]} RowError: "Column 'eval' does not allow DBNull.Value."

[Christoffer Lette]

Welcome to SO, Bob. I've edited your answer (still in review, though). For example, we prefer not to have greetings and signatures in the answers (it's considered "noice", please see the FAQ). Your name and gravatar will always show below the answer anyway.

[jsblaisdell]

Awesome. That didn't work, but I could add a watch for the dataset and type .GetErrors after it and expand the values. That is extremely useful. Hopefully I won't forget it before the next time I need it:)

[fortboise]

This helped me find the data causing my problem, which was not "bad data" but rather bad behavior of the Data Source Configuration Wizard. It's apparently not getting revised column constraints (and I'm missing an added table to boot), in spite of going out and talking to the DB... and with cache not enabled.

[e-on]

Yes this was really helpful - the reason for my error was the length of the field was longer than the column's maxLength in the table adapter. One thing I did notice was that in order to hit the breakpoint in the designer file, you need to go to Tools > Options > Debugging, and make sure "Enable Just My Code" is unchecked. It'll then allow you to step through the designer file code.

[PaulStock]

Good suggestion, but that only works if it is the first row in the datatable which has the error, doesn't it? If 100 good rows are returned and then 1 bad row, there won't be a RowError on Rows(0), will there?

[Mark Berry]

My issue must have been MaxLength as well. I use the VWD 2010 dataset designer. The source table was changed by someone else. I modified the SQL Query to select *, thinking that would refresh all columns, but apparently it didn't update existing lengths. So I modified the query to select one field, saved the .xsd, opened the .xsd in Notepad++ to check that all but one of the MaxLength defs were gone, then modified the query again to select *. THAT refreshed the MaxLengths and got me past this error.

[Jon D]

Thank you so much, I have been scratching my head on this one all day as everything was reporting back fine. I also had to change to nvarchar(MAX), but the DataTable had kept MaxLength at 10! I owe you a drink!

[Andez]

Brilliant way to find out exactly where it went wrong. Totally helped me unfathom problems in a solution I inherited where there were inconsistencies with the data. Although it was on a DataSet and I just iterated through each table, then each row. +10 if I could.

[Dan]

Thanks for this answer. I was have a Case Sensitive problem and just needed to set it appropriately in the Dataset.

[Hasan Shouman]

Excellent Answer! Thanks for all of the details !

[Chagbert]

Adding onto @steve : I simply just re-write all my custom queries associated within the tableAdapter i.e. make sure all my queries do "query" / show / "return" all the NOT NULL columns. Thank you so much guys for the other causes.

[Mahmoud]

google always give me this answer to my error, and always this answer helps me, thanks pro ^^

[arnfada]

Great answer! Helped me a lot.

[Jeffrey Roughgarden]

I had this problem because the column name of the C# primary key was not the same as the name in the SQL database. Made them the same and the problem vanished.

[Alex]

This worked for me. It was a Column 'MyColumn' does not allow DBNull.Value, but it wouldn't show that any other way. Thanks :)

[Uday]

Thanks @PaulStock for you answer I got my same issue solve.

[Rob]

This was extremely useful, I found a mismatch between data column length - it was increased in the database and not in the dataset.

[Rudy Hinojosa]

use >datatable.GetErrors() to find offending rows. followed by >datatable.GetErrors()[rownumber].RowError to get back error description

[can.do]

In my case this is caused by column definition change, column size was increased, now the application is reporting the constraint issue. Both database and dataadapter have been changed to match, application rebuilt, any suggestions are appreciated. This is not making any sense, on top of that it's a retrieval, and all changes were applied... Thanks.

[Sajjad Ahmed Paracha]

In my case the primary key was of type varchar , user mistakenly entered 2 records having same primary key with different case (i.e. Capital and Small). Somehow Oracle was able to fetch both records in select all statement however the C# code was complaining about the uniqueness. The problem was fixed by deleting one of the records and making sure table had only one row with that primary key regardless of the case.

[Yahfoufi]

Wow!! Thanks a bunch!!

[Elidio Marquina]

In my case was a Designed DataSet with a Max Length Limit on the attributes of the Table Type. Thanks for the orientation.

[Minion91]

Still a relevant answer, still a great answer. Thanks a lot !

[Allen]

Exactly the right recommendation. I enclosed the problematic bits in a #define so once the problem is resolved the entire Try/Catch is simplified (removed)...

[nuwancy]

Thnx alot. Never knew abt datatable.GetError(). Surprisingly this was the error for me. "Column 'col1' exceeds the MaxLength limit."

[Hafiz M Taha Waseem]

Brother! I was after this error for 2 days After consulting your points of causes, My Problem got resolved Thanks a lot!