C# – SQL Server Foreign Key, Unique constraints

I got tripped up today with a particularly nasty Join statement that worked perfectly form 90% of the queries, but was blowing up at times.  The error message was: 

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

I looked and looked at this SQL code:

SELECT Process.ProcessID, Process.Name, Process.Description, Process.ResultLogStatusID, Process.StatusTimeStamp, ResultLogStatus.Name AS ProcResult, RequirementTestLink.RequirementID

FROM RequirementTestLink

LEFT JOIN Process ON Process.ProcessID = RequirementTestLink.ProcessID

LEFT JOIN ResultLogStatus ON Process.ResultLogStatusID = ResultLogStatus.ResultLogStatusID

WHERE (RequirementTestLink.RequirementID IN (" + sReqs + "))

ORDER BY RequirementTestLink.RequirementID, Process.ProcessID

It looked perfectly legit to me. I figured out that there would be multiple instances of Process.ProcessID in the RequirementTestLink table. But this shouldn’t have been a problem since it would just create multiple rows for the multiple instances.

Relax or turn off constraints in your DataSet.

The problem was that I was using a SQL Adapter to fill a DataTable. When I made the DataTable, it got junked up with Primary Key and Foreign Key constraints on the ProcessID field. The answer had been right there in the message (see image below). They spelled it out in the first line: “Relax or turn off constraints in your DataSet”. It took me 30 minutes to work it out on my own…if I’d just actually read those pop ups (but so often they are so cryptic I can’t comprehend them 😛 ).

So, to get rid of that Primary Key:

1. Left click on the key icon (you can see it in the image at right beside ProcessID)
2. Right click on the same key icon and select ‘Delete key’. 

If that doesn’t do it for you, then zap the Foreign Keys as well.  You can see one coming in at the top right (left picture).  Click on the line and delete it.