ZZartin (8/31/2015)
Just out of curiosity, it seems like a lot of people are putting a lot of faith in foreign key constraints I'm wondering why? Compared to a primary key or unique constraint where I can tell you exactly what it's enforcing all a foreign key constraint enforces is that some arbitrary matching record exists in the parent table? A foreign key has no way of knowing whether it's referencing the right record at all and no way to handle errors when something goes wrong.
I wouldn't say that anyone has suggested we put more faith in foreign keys than we do in any other construct within the database. When given a foreign key definition, just like with a unique constraint, I can tell you exactly what's enforcing things. It's not a arbitrary row. It's one that is enforced by the definition of the foreign key. Now, can that be the wrong data? Certainly. Just as, with a unique constraint, the data will be unique, but that doesn't make the data right. The structures we have enforce certain behaviors that helps us define data integrity. They don't help us define data rightness or data usefulness. That's a different problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning