0

For the first time I am trying to create a DataSet/TableAdapter in the VS2010 DataSet Designer using a query with a join and function; NVL(). Typically I had only used single database tables by dragging them to the designer workspace from the server explorer. In this case I right-clicked and added a TableAdapter and entered the following query:

SELECT a.primary_key, NVL(a.message, b.subject) as subject
FROM TableA a, TableB b
WHERE a.primary_key = b.primary_key (+) 
AND (a.time_stamp BETWEEN :time_start AND :time_end);

The DataTable was created with the appropriate columns. The issue is that when attempting to execute the query on the TableAdapter I am given the error:

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

I don't see anything unusual in the properties of the individual columns. What am I missing?

Thanks!

McArthey
  • 1,614
  • 30
  • 62
  • FROM clause got eaten by gremlins... possible to restore it? – b0rg Feb 28 '12 at 15:32
  • Not a spectacular addition but good catch. Thanks. Never fear, it's in the code but I hadn't provided it here. – McArthey Feb 28 '12 at 15:35
  • leaving aside "whys" messages are stored outside of subjects, most likely there are subjects missing for messages somewhere out there, that is if join is correct. I gave the query to check in my answer, please tell us the results. – b0rg Feb 28 '12 at 15:42

2 Answers2

2

most likely it's left outer join and the FROM clause is missing.

Last time these things happened to me when there were no rows returned for b, but it was too long ago, I have even forgotten the old join syntax... run the query in query analyser or ssms and tell us :)

and this query too: 

select count(1) from tableB where primary_key not in (select primary_key from tableA)
b0rg
  • 1,879
  • 12
  • 17
  • This was the issue. Apparently the constraints on the primary table were still in place but the seconday table allows for insertion of a duplicate value and the key is slightly different. Thanks for helping to locate this strangeness. Can I vote *against* poor documentation and lack of monitoring on changes? ;) – McArthey Mar 08 '12 at 16:07
0

Be sure to set the property "EnforceConstraints" on DataSet Level to "false".

Dimi Takis
  • 4,924
  • 3
  • 29
  • 41
  • Thanks for your suggestion. I am investigating further, but I was hoping for a less "brute force" method. The query runs fine in all other contexts and the "preview" mode works fine. I believe I have satisfied all not null columns, foreign keys, and unique keys so I don't understand where this error is coming from. Is it due to the NVL() function or something else entirely? – McArthey Feb 28 '12 at 15:04