Back when working on the TFS team, I tried (unsuccessfully) to get foreign keys adopted, at least in debug/test builds, or at least in most of the tables (I could see tbl_LocalVersion, for instance, being an exception *if* perf testing showed a significant hit for the FK being in place). Today I happened to hit a bug in Team Build that’s caused (at least in part) by the lack of FK.
Luckily for me, the actual problem and solution were already posted on StackOverflow, which saved me a huge amount of time in figuring this out.
As mentioned there, the problem is caused by entries in the queue (tbl_BuildQueue, in the collection database, usually Tfs_DefaultCollection) tied to invalid (no-longer-exists) build definitions.
Here’s tbl_BuildQueue, in all its “FKs are for losers!’ glory:
I have a whopping 62 rows in the table at the moment.
Checking in tbl_BuildDefinition to see what the valid DefinitionId values are:
Finding the row(s) with invalid FK values is simple enough (various options – not exists, left join, etc.)
select * from tbl_BuildQueue bq
left join tbl_BuildDefinition bd
on bq.DefinitionId = bd.DefinitionId
where bd.DefinitionId is null
Sure enough, there’s an invalid row (thankfully just the one).
While one of the people on the SO thread left the invalid row and changed the status to cancelled, in my case I remove the row since IMHO if the root bug were fixed, then that would be the result (cascade the delete down a level).
After deleting that one row, the queued builds are now being processed fine.
Certainly you could argue that another potential route for the fix is to do soft deletes instead, but in either case, it seems safe to say, I would think, that getting into this kind of state with an invalid value in what’s effectively an FK field (and IMHO, actually SHOULD be an FK) is a Bad Thing.
Even if you take the approach of making sprocs your only surface area for your database, it still seems bizarre to me to not use a mechanism like FK that the database gives you when it can help protect you from bugs in your sprocs.
Ah, well, back to real work.