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.

Jegar

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 . – Jegar

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

Answer 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

Trichoid

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

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:) – Grizel

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. – Mensal

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. – Thurgau

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. – Electret

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

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

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. – Polypeptide

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. – Wry

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

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)... – Headstock

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

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

Synsepalous

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. – Jit

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

Unfortunately, unlike DataSet, DataTable has no EnforceConstraints property. Creating a DataSet only for this purpose and then adding the DataTable to the DataSet solved the problem for me. Thanks a lot. – Sharpfreeze

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

Rheotropism

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. – Melanimelania

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

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

Kidwell

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. – Lon

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

Sterol

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. – Sheeb

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! – Temperament

Answer 6

The problem is with the Data Access designer. In Visual Studio, When we pull a View from "Server Explorer" to the Designer window, it is adding either a Primary key on a column randomly or marking something to a NOT NULL though it is actually set to null. Though the actual View creation in the SQL db server, doesn't have any primary key defined or the NOT NULL defined, the VS designer is adding this Key/constraint.

You can see this in the designer - it is shown with a key icon on left of the column name.

Solution: Right click on the key icon and select 'Delete Key'. This should solve the problem. You can also right click on a column and select "Properties" to see the list of properties of a column in the VS Data access designer and change the values appropriately.

Obadias

Answer 7

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 or stored procedure.

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

Phytogeography

Answer 8

Mine started working when I set AllowDBNull to True on a date field on a data table in the xsd file.

Curcuma

Answer 9

It sounds like possibly one or more of the columns being selected with:

   e.eval, e.batch_no, e.crsnum, e.lect_code, e.prof_course

has AllowDBNull set to False in your Dataset defintion.

Farly

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

Answer 10

It is not clear why running a SELECT statement should involve enabling constraints. I don't know C# or related technologies, but I do know Informix database. There is something odd going on with the system if your querying code is enabling (and presumably also disabling) constraints.

You should also avoid the old-fashioned, non-standard Informix OUTER join notation. Unless you are using an impossibly old version of Informix, you should be using the SQL-92 style of joins.

Your question seems to mention two outer joins, but you only show one in the example query. That, too, is a bit puzzling.

The joining conditions between 'e' and the rest of the tables is:

AND c.crsnum = e.crsnum  
AND c.batch_no = e.batch_no  
AND d.lect_code= e.lect_code 

This is an unusual combination. Since we do not have the relevant subset of the schema with the relevant referential integrity constraints, it is hard to know whether this is correct or not, but it is a little unusual to join between 3 tables like that.

None of this is a definitive answer to you problem; however, it may provide some guidance.

Calctufa

Answer 11

Thank you for all the input made so far. I just wanna add on that while one may have successfully normalized DB, updated any schema changes to their application (e.g. to dataset) or so, there is also another cause: sql CARTESIAN product (when joining tables in queries).

The existence of a cartesian query result will cause duplicate records in the primary (or key first) table of two or more tables being joined. Even if you specify a "Where" clause in the SQL, a Cartesian may still occur if JOIN with secondary table for example contains the unequal join (useful when to get data from 2 or more UNrelated tables):

FROM tbFirst INNER JOIN tbSystem ON tbFirst.reference_str <> tbSystem.systemKey_str

Solution for this: tables should be related.

Thanks. chagbert

Thurgau

Answer 12

I solved the same problem by changing this from false to true. in the end I went into the database and changed my bit field to allow null, and then refreshed my xsd, and refreshed my wsdl and reference.cs and now all is well.

this.columnAttachPDFToEmailFlag.AllowDBNull = true;

Photophilous

Answer 13

Short and easy Soloution:

Go to MSSQL Studio Sever ;

Run the query of the cause of this error : in my case i see that id value was null because i forget to set Identity specification increment by 1.

So entered 1 for the id field as its is autoincremane and modify dont allow NULLS in desing view

That was the error that caused my bindingsource and tabel adapter throwin error at this code:

   this.exchangeCheckoutReportTableAdapter.Fill(this.sbmsDataSet.ExchangeCheckouReportTable);

Sporogony

Answer 14

DirectCast(dt.Rows(0),DataRow).RowError

This directly gives the error

Camfort

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? – Trichoid

Answer 15

If you are using visual studio dataset designer to get the data table, and it is throwing an error 'Failed to Enable constraints'. I've faced the same problem, try to preview the data from the dataset designer itself and match it with table inside your database.

The best way to solve this issue is to delete the table adapter and create a new one instead.

Delitescent

Answer 16

* Secondary way : *

---

If you don't need [id] to be as Primary key,

Remove its primary key attribute:

on your DataSet > TableAdapter > right click on [id] column > select Delete key ...

Problem will be fixed.

Gunther

Answer 17

I also had this issue and it was resolved after modifying the *.xsd to reflect the revised size of the column changed in the underlying SQL server.

Oulman

Answer 18

I resolved this problem by opening the .xsd file with an XML reader and deleting a constraint placed on one of my views. For whatever reason when I added the view to the data it added a primary key constraint to one of the columns when there shouldn't have been one.

The other way is to open the .xsd file normally, look at the table/view causing the issue and delete any keys (right click column, select delete key) that should not be there.

Perigee

Answer 19

To fix this error, i took off the troubling table adapter from the Dataset designer, and saved the dataset, and then dragged a fresh copy of the table adapter from the server explorer and that fixed it

Baylor

Answer 20

Just want to add another possible reason for the exception to those listed above (especially for people who like to define dataset schema manually):

when in your dataset you have two tables and there is a relationship (DataSet.Reletions.Add()) defined from first table's field (chfield) to the second table's field (pfield), there is like an implicit constraint is added to that field to be unique even though it may be not specified as such explicitly in your definition neither as unique nor as a primary key.

As a consequence, should you have rows with repetitive values in that parent field (pfield) you'll get this exception too.

Mythological

Answer 21

In my case this error was provoked by a size of a string column. What was weird was when I executed the exact same query in different tool, repeated values nor null values weren't there.

Then I discovered that the size of a string column size was 50 so when I called the fill method the value was chopped, throwing this exception.
I click on the column and set in the properties the size to 200 and the error was gone.

Hope this help

Brueghel

Answer 22

I solved this problem by doing the "subselect" like it:

string newQuery = "select * from (" + query + ") as temp";

When do it on mysql, all collunms properties (unique, non-null ...) will be cleared.

Bombycid

Answer 23

            using (var tbl = new DataTable())
            using (var rdr = cmd.ExecuteReader())
            {
                tbl.BeginLoadData();

                try
                {
                    tbl.Load(rdr);
                }
                catch (ConstraintException ex)
                {
                    rdr.Close();
                    tbl.Clear();

                    // clear constraints, source of exceptions
                    // note: column schema already loaded!
                    tbl.Constraints.Clear();
                    tbl.Load(cmd.ExecuteReader());
                }
                finally
                {
                    tbl.EndLoadData();
                }
            }

Adamsun

Answer 24

I received the same error type and in my case it solved it by removing the select fields and replacing them with a *. No idea why it was happening. The query had no typos or anything fancy.

Not the best solution but nothing else worked and I was getting exhausted.

In my search for a clear answer I found this on this: https://www.codeproject.com/questions/45516/failed-to-enable-constraints-one-or-more-rows-cont

Solution 8

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/stored 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.

Sulphanilamide

Answer 25

If you have failing DataSet (not DataTable):

if (dataSet.HasErrors)
    foreach (DataTable table in dataSet.Tables)
        if (table.HasErrors)
            foreach (var row in table.GetErrors())
                Debug.Write($"Error in DataTable {table.TableName}: {row.RowError}")

Kenwee

Answer 26

if _sample_DataSet was the name of dataset that encounter error while filling, you can put the fill dataset inside a Try Catch and then put following code in catch{} block then you are able to exactly find the erroneous column.

            foreach (DataTable _dtable in _sample_DataSet.DataSet.Tables)
            {
                foreach (DataRow dr in _dtable.Rows)
                {
                if (dr.HasErrors)
                {
                        if (dr.HasErrors)
                        {
                            Debug.Write("Row error="+dr.RowError);

                        }
                }
            }

Electrify

Answer 27

The problem is with the UNIQUE clause IMHO. I have had problems like this crop up where the sql query executes 100% in SQL but it doesn't work in code.

UNIQUE or DISTINCT clauses are an issue.

Piezochemistry

Answer 28

I got this error using SQLite running a 4-table inner join. After reading some answers here I removed from the select any primary keys. The last one ("WorkflowID", that I actually needed) was resolved when I replace the primary key's column with the value of the joined column (a lookup table).

From:

                    SELECT s.StepID, s.StepName, w.WorkflowID AS WID, w.WorkflowName FROM Apps a
                INNER JOIN Apps_Steps ss ON a.AppID = ss.AppID
                INNER JOIN Steps s ON s.StepID = ss.StepID
                INNER JOIN Workflows w ON w.WorkflowID = s.WorkflowID
                WHERE a.AppID = 1

To:

                    SELECT s.StepID, s.StepName, s.WorkflowID AS WID, w.WorkflowName FROM Apps a
                INNER JOIN Apps_Steps ss ON a.AppID = ss.AppID
                INNER JOIN Steps s ON s.StepID = ss.StepID
                INNER JOIN Workflows w ON w.WorkflowID = s.WorkflowID
                WHERE a.AppID = 1

Tavis