ASP.NET SQL Server: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

In an ASP.NET / SQL-Server web app I hit this error in a web form after modifying a the query of a TableAdapter in a .xsd file using VWD:

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

The underlying SQL Query was a fairly simple UNION ALL query looking for a value (in this case an email address) across several different tables. I had two new tables to search in, so I simply added two more UNION ALL sections to the query for these new tables.

Then I hit the above error. There were some slight differences to the column definitions between the new tables and the existing ones. I tried all sorts of things including CASTing variables and checking for NULLs, all to no avail. Eventually I twigged that it was hanging on to something in the .xsd file behind the scenes.

Solution: Copy the SQL from the table adapter, delete the table adapter, then re-create it and paste in the old SQL. Runs perfectly. (Well, it did after I had two goes at getting the table adapter name exactly the same as before!)

Oh the joys...!