Failed to enable constraints. When using a data table adapter

Question

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 
f.id, f.name, 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.

Stortz

what happens if you use just 'f.productid = p.ProductID' or just 'f.productid = p.MasterProductID'? – Tmesis

Usually I would expect to have a DataTable filled with rows from a single table instead of a join query. You could then still go from FormSaveData to Products and vice versa via foreign key relationships. Anyway if you need this query for the table adapter, you should check the definition of the DataTable that is going to be filled by this adapter for columns that must not be NULL and for unique and foreign-key constraints in the DataSet and whether the data that results from this query might break one of them. – Rimarimas

@Tmesis It works if I only use one or the other. The thing is that some rows have the productID and some have the master and it needs to check both. – Stortz

Answer 1

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.

Candless

Is this is a setting in the table adapter properties? I was really hoping for an answer like this. Less regression testing. – Stortz

I set the EnforceConstraints to false, but now I'm getting a Object reference not set to an instance of an object error :( – Stortz

Once I redid my table adapters it worked fine. Thank you for the help. – Stortz

Answer 2

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.

Engulf