Using DBMS-features like foreign keys, check constraints, defaults, unique keys, etc. as some sort of "last line of defence" to my opinion is crucial whenever designing/maintaining a database system, especially in "evolving systems" that are not out-of-the-box.
of course you could do all those things by "well tested SPs", but how can you ever assure to have your 1000+ procedures in a larger system up-to-date, especially when the underlying DB-table structure has to be changed?
the cost of such features is absolutely outweighed by the fact that you've done everything possible to ensure data integrity.
"scarcely necessary if creating, updating and deleting are done through well-tested stored procedures".
Where is it more likely that bugs will arise ? In a "well-tested" stored-proc, or in a foreign key declaration ?
With the no-foreignkeys approach, you end up writing all sorts of repair routines to search for & clean up orphaned data. Of course, this orphaned data should never exist, because these "well-tested" stored-procs are entirely 100% bug-free and never allow duff data into the database. Perhaps in fantasy-land that is true, but in the real world ... And of course, every application always uses these stored-procs, no application ever bypasses them and updates the data directly. That would never happen. No, of course not - that would just be daft, and lead to duff data.
If declarative integrity checks are used, you avoid wasting time on such cleanup activities. You state to the DBMS what things must be true, and let the DBMS worry about it. Make it work for its living - that's what it's there for! OK there is some (minimal) overhead, but I'd rather pay that cost and have good data, than save some minimal amount of performance and end up with crap data that needs sorting later.
Good article David.
But the first thing I thought was: Oh no, not another discussion on DRI...
Sounds a bit defeatist but, as with so many things it's impossible to get everything that you want. If someone creates a method to enforce DRI without the overheads involved then I'm sure we'll all be pleased to make them very rich. In the mean time, however, we're going to have to work around the constraints.
Enforcing DRI programmatically using stored procedures sounds like it's got some potential for disaster. Once the database ownership has been handed over, you can't rely on the initial protocol. I've seen people go to extraordinary lengths to remove and create their constraints and DRI programmatically, not something I would recommend for the faint of heart.
I would be in favour of being able to use TRUNCATE if there's no associated child data though. Does anyone know why this has been "blanket-enforced"?
Nice article
Particularly interesting for me, because in my spare time I am working on building a .net CMS system using SQL 2000 for the backend. There have been a couple of occasions where I've been unsure whether to include DRI or not, but for now my approach is to always include it.
Good stuff.
That was an outstanding article. Thanks.
We're currently in a bit of a struggle with a development team that has become completely enamored with generated code. Problem is, with type of coding being generated it's a lot of extra work to determine if a record has been edited. Therefore, they want to simply delete complete record sets and re-insert them. We've been arguing against it at length, and you've just given us some more ammunition. Thanks again.
BTW, our own tests, while not focusing on DRI, do bear out most of your tests. For those who are concerned that he did the test on a code table, which is unlikely to change, our developers are deleting from an active table with about eight child tables, so the paradigm holds up.
