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

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.