无法启用约束.一行或多行包含违反非空、唯一或外键约束的值

问题描述

我在 informix 数据库中进行了外连接并成功执行，但在我的代码中出现以下异常:

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

未能启用约束.一行或多行包含值违反非空、唯一或外键约束.

我知道问题所在，但我不知道如何解决.

我进行外连接的第二个表包含一个复合主键，在前一个外连接查询中为空.

 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 减法，c.lect_kind，e.eval，e.batch_no，e.crsnum、e.lect_code、e.prof_courseFROM rlm1course a, rfc14crsgrp b, ckj1table c, mnltablelectev d,外(cc1assicrseval e)其中 a.crsnum = b.crsnum和 b.crsnum = c.crsnum和 b.crscls = c.crscls和 b.batch_no = c.batch_no和 c.serial_key = d.serial_key和 c.crsnum = e.crsnum和 c.batch_no = e.batch_noAND d.lect_code= e.lect_codeAND d.lect_code = ....和 b.batch_no = ....

问题发生在表 cc1assicrseval 上.主键是(batch_no, crsnum, lect_code).

如何解决这个问题?

根据 @PaulStock 的建议:我照他说的做，我得到:

?dt.GetErrors()[0] {System.Data.DataRow} HasErrors: true ItemArray:{object[10]} RowError:列 'eval' 不允许 DBNull.Value."

所以我通过将 e.eval 替换为 ,NVL (e.eval,'') eval 来解决我的问题.这解决了我的问题.非常感谢.

解决方案

此问题通常由以下原因之一引起

• 为未设置为 AllowDBNull 的列返回空值
• 使用相同的主键返回重复的行.
• 数据库和数据集之间的列定义不匹配(例如 char 字段的大小)

如果结果集不是太大，请尝试以本机方式运行查询并查看结果.如果您已经消除了空值，那么我的猜测是主键列被重复了.

或者，要查看确切的错误，您可以像这样手动将 Try/Catch 块添加到生成的代码中，然后在引发异常时中断:

然后在命令窗口中，在获取错误的表上调用 GetErrors 方法.
对于 C#，命令将是 ?dataTable.GetErrors()
对于 VB，命令是 ?dataTable.GetErrors

这将显示所有有错误的数据行.然后，您可以查看其中每个的 RowError，它应该会告诉您无效的列以及问题.因此，要查看第一个错误数据行的错误，命令是:
?dataTable.GetErrors(0).RowError
或者在 C# 中它会是 ?dataTable.GetErrors()[0].RowError

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);

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:

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

解决方案

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

这篇关于无法启用约束.一行或多行包含违反非空、唯一或外键约束的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！