How do you know which constraint causes 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.' exception

[fvancrae (Frederic Vancraeyveldt)]

How do you know which constraint is throwing this error.

I perform a streamer.ReadDataTable(tablename, dataTable, true, true);

but I get the exception below.

I would like to know which field/constraint causes it.

[fvancrae (Frederic Vancraeyveldt)]

Note: Could it be the primary key?

I see there are identical rows in the table. The first field (value 32) is marked as primary key in the da schema.

[mh (marc hoffman)]

@fvancrae

I see there are identical rows in the table. The first field (value 32) is marked as primary key in the da schema.

If the first row only is the PK, that’s definitely bad. What’s the PK in the database? the VD itself shouold enforce unique PKs. That said, the error sound more like a mssing value for a NON-NULL field, or a value in a FK field that doesnt mate the valid foreign keys… It’s too bad thw db error doesnt saw which field; i have the same annoyande with Postgres some times; the erros are often deliberately vague…

[fvancrae (Frederic Vancraeyveldt)]

After removing the duplicate rows the error isn’t returned anymore.

So the issue appears to be the non unique PK in the DB.

We do not enforce unique constraint via the DB.

My post is mainly to indicate the RO exception message and details are not useful or even misleading. Maybe the cause is indeed the underlying (MSSQL) DB. In that case I guess RO can do little about it.

[mh (marc hoffman)]

@fvancrae

So the issue appears to be the non unique PK in the DB.

We do not enforce unique constraint via the DB.

Scary This is SQL Server? i would not have expected that to even allow a PK that isn’t automatically uniqie…

[fvancrae (Frederic Vancraeyveldt)]

For historic reasons we don’t use uniqueidentifier type by regular integer without constraint for which we determine the new PK value ourselves.

We set the PK property in RO DA however to be able to update using that key.

The situation (multiple DB records with same PK) is definately a DB issue but it would still be nice if the error message indicates the actual issue

[mh (marc hoffman)]

@fvancrae

The situation (multiple DB records with same PK) is definately a DB issue but it would still be nice if the error message indicates the actual issue

Agreed, yeah. Unfortunately, i believe this error doesnt originate from DA, but from the database. @EvgenyK correct me if i’m wrong?

[EvgenyK (Evgeny Karpov)]

Hi,

according to screenshot:

@fvancrae

error is raised inside System.Data.DataTable class.

You can see StackTrace property for receiving full call-stack.

[mh (marc hoffman)]

@EvgenyK

error is raised inside System.Data.DataTable class.

Yeah. So it’s either a message generated by the .NET database layer or (more likely, IMHO) recived from the database engine itself and merely promoted to an excpetion by the .NET database layer.