Maxx Maxx - 1 year ago 48
SQL Question

Failed to enable constraints. When using a data table adapter

I'm getting

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

for the following query in my table adapter.

select,, p.productid, p.masterproductID, f.productid, f.dateCreated, f.retired, p.code as HTML_DisplayName, p.ThumbnailID, p.code
from FormSaveData f

inner join Products p on (f.productid = p.ProductID or f.productId = p.MasterProductID)

where f.userId = 130559
and (p.b_IsArchived = 0 and p.b_IsRetired=0 and p.b_IsStaged = 0)

When I run the query on its own, it works just fine, other userIds also work just fine, so this is a very particular case. I've narrowed it down to the fact that I am inner joining on

f.productid = p.ProductID **or** f.productId = p.MasterProductID

I believe in certain rare cases this causes some kind of conflict that the table adapter does not like. I'm pretty much a novice when it comes to table adapters and SQL so any advice on how this can be better written or why this is happening would be greatly appreciated.

There are no constraints, keys or special rules set for anything in the FormSaveData table.

Answer Source

Looks to me that your query is returning several records with the same product_id and the table adapter expects only unique rows. I am pretty sure you can disable the behavior by setting EnforceConstraints to false.