8

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.

Gilad Green
  • 36,708
  • 7
  • 61
  • 95
Maxx
  • 3,925
  • 8
  • 33
  • 38
  • what happens if you use just 'f.productid = p.ProductID' or just 'f.productid = p.MasterProductID'? – tofutim Oct 14 '11 at 22:13
  • 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. – JayK Oct 14 '11 at 22:23
  • @tofutim 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. – Maxx Oct 15 '11 at 22:20

2 Answers2

12

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.

Gilad Green
  • 36,708
  • 7
  • 61
  • 95
Icarus
  • 63,293
  • 14
  • 100
  • 115
  • Is this is a setting in the table adapter properties? I was really hoping for an answer like this. Less regression testing. – Maxx Oct 15 '11 at 22:21
  • I set the EnforceConstraints to false, but now I'm getting a Object reference not set to an instance of an object error :( – Maxx Oct 17 '11 at 15:59
  • 2
    Once I redid my table adapters it worked fine. Thank you for the help. – Maxx Dec 07 '11 at 21:37
4

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.

Zolfaghari
  • 1,259
  • 1
  • 15
  • 14