My client doesn't allow the use of Foreign Key Constraints in SQL Server

I'm working on a project at a Fortune 500 Microsoft customer and ran across something interesting: They DO NOT allow foreign key constraints in production SQL Server databases. The theory being that they have negative performance impact, and that developers can check for related data as needed in stored procedure code.

This is new to me, but I wanted to double check with some SQL Gurus before I call their baby ugly... :-)

Here is a great response from my friend Brian:

"Their baby is UGLY! But you really can't tell a parent that!

They are correct, you do take a noticeable hit ( seen up to 5% on heavily updated DB's)
In particular you can see a lot locking and deadlocks.

However I've yet to see development staff that consistently check the data or deal properly with errors and/or partial rollbacks! I'll put money on the fact they have corrupt data!

I like FK's because they 'document' the DB and I use them to automate data archiving.

That said, I am in the process of converting my current app to ignore FK relationships.
(We do 24,000,000 + insert/update/deletes daily so performance is an issue)

I define relationships in the DB.
Enable then in Dev and Test to facilitate testing.
Disable them in perf and prod for performance

Once a day/week check the FK's. Not much more expensive than DBCC CheckDB
IF we find corrupt data, we enable the FK so we throw errors to facilitate route cause analysis.
Note this happens regularly after a code release ;) A little less often when new customer/data patterns hit the DB.

While this is not a 'Best Practice' it is a better than what they are doing today. "


Example code:

-- Create the tables and populate with valid data
use tempdb
go

if object_id('child') is not null drop table child
if object_id('parent') is not null drop table parent
go

create table parent (id int primary key)
create table child (id int primary key, pid int)
alter table child with nocheck add constraint FK_child_parent foreign key ( pid) references parent(id) -- Use 'with nocheck' so FK can be added even if existing data is corrupt
go

insert into parent values (1)
insert into parent values (3)
insert into child values (1, 1)
insert into child values (3, 3)
go

-- Try adding invalid data
insert into child values (2, 2)
go

-- Disable FK and try again
alter table child nocheck constraint FK_child_parent
insert into child values (2, 2)

-- Check the table
DBCC checkconstraints (FK_child_parent)

-- Enable the FK so code now throws errors
alter table child with nocheck check constraint FK_child_parent -- Use 'with nocheck' so existing corrupt data is ignored, but new data will error
insert into child values (2, 2)