Opinion: Let the database handle the RI, not the code
I enjoy perusing the SQL Server team blogs. Lots of sharp folks right there in the middle of the nuts and bolts of our beloved database server. Most of the time some really great stuff. I did read one the other day that caught my eye – one entitled Zeroing in on blocking on seemingly unrelated tables. This blog had an unusual conclusion that I’d like to share. From the blog, regarding blocking caused by foreign key constraints:
2. Removing the referential integrity. This assumes that your application does not need this, or can enforce it via some other means, e.g. within the business logic layer of the application.
Does not need this? You don’t suppose that the RI was put there for a reason do you? You can never, ever assume that you application doesn’t need this. Moreover, enforcing RI with procs or TSQL is a bad idea. Trust me – if you do this, at some point some time you’ll have garbage to clean up out of your database without fail. Unusual situations caused by poor programming, a lack of testing, rollbacks, etc. will leave orphans. H*ll, even having RI will leave you open to orphaned records at some point, because some day sure as the sun comes up in the morning, someone will remove the RI, add some data, and add the constraint back with a no check. Enforcing business logic and RI in the database was popular around the Dallas area several years ago…a lot of the guys at Verizon were doing it that I am aware of…they realized a performance gain, but overall was a poor practice.
Just as you don’t enforce integrity with triggers, neither should you with stored procedures.
Lee
----------------------------------------------
“Stop swingin' the bat. Put the bat down, Wendy. Wendy? Give me the bat... “
1886d291-c03d-4f94-8dd1-73be65289316|3|5.0