Removing Foreign Key relations from Production SQL Server :

Question

Removing Foreign Key relations from Production SQL Server :

Unnikrishnan.C

Old Hand

Points: 320
More actions
March 8, 2005 at 6:11 am

#87801

Hi,
Someone was telling that removing all the Foreign Key relations from the SQL Server in production environment would enhance the performance. But is it a good idea to remove the Foreign Key relations? Will it affect the data integrity? Or is it based on the assumption that being in Production environment the system might have well tested for data integrity. Can you share your views on this?
Regards
Unnic

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I don't know how removing foreign key constraints would affect performance, but it certainly would affect referential integrity between tables. In my shop, data integrity is never sacrificed for the sake of performance. See 'Referential Integrity' in BOL.

Greg

noeld SSC Guru Points: 96590 More actions · Answer 2

It does affect performance specially at DML (U/I/D) time not at reading though, but I would never ever remove them. Have you ever heard : garbage in - garbage out. It's been my experience that if you let open the possibility of bad data get in your DB it will get in and how much is worth a db permeated with bad data ?

Do your math,

Cheers

* Noel

Daljit-208782 SSChasing Mays Points: 623 More actions · Answer 3

removing the foreign keys constraint from the database help in some performance boost as while storing the data to the tables SQL engine does not need to check integrity of the data.

On the other hand there will be a burden in changing you application logic or business rules so that there should not be inconsistency in the data. You need to write SP's for back end checking and and need to enhance your application logic to check the things on client API.

Cheers

Ben Blum Right there with Babe Points: 776 More actions · Answer 4

What a terrible idea!

Is the person who told you to do that in line for the same promotion as you? Sounds like a setup.

I'd sooner give my boss a wedgie than remove the relationships from a production database!

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 5

I wouldn't normally add to such a pile-on, but it IS such a bad idea, that I can't believe someone suggested it seriously. Performance is important, but not at the expense of data integrity. I've seen a few databases without FKs, and even with the checking that tries to prevent integrity issues, something always gets through.