SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enforcing Referential integrity in Microsoft SQL Server 2000


Enforcing Referential integrity in Microsoft SQL Server 2000

Author
Message
Nick Duckstein
Nick Duckstein
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlserve


> Nick Duckstein
DCPeterson
DCPeterson
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 432

Nick,

As much as I agree with your general point that integrity should be enforced at the data layer, not the application etc... I think you shot yourself in the foot when you advocated the use of what I call Massively Unified Code-Key (MUCK) tables.

The number of tables in a "schema" isn't what complicates things, it is much more often the inappropriate combining of things into fewer tables that complicates both enforcement of integrity constraints and querying. This is the whole purpose of normalization, eliminate dependencies in your tables so that integrity CAN be enforced through the use of Primary and Foreign Keys. See my article on the subject for a more detaied treatment of the subject.



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 714
Nick,

You forget a key problem with the OTLT (One True Lookup Table), what dcpeterson calls a "MUCK" table -- It very clearly violates the First Normal Form, destroying all data integrity. RI is not the only means of enforcing integrity in an RDBMS. Domain integrity is just as important.

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Steve Rosenbach
Steve Rosenbach
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 206

Nick - I very much agree with Don Peterson's comment - a very good article except for the suggestion to consolidate lookup tables. Don's article, for which he provides a link, give an excellent summary of the issue and provides good reasons for *not* going the route of consolidating lookup tables.

It's not that lookup tables complicate a database - it's that life and business processes can in fact be complicated, and accurately representing the facts and the relationships between them properly leads to a lot of "little tables."

That one criticism notwithstanding, I liked your article and your writing style.

Best regards,

SteveR





Joris Dobbelsteen
Joris Dobbelsteen
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 1

Deal with some old DBase application that don't (cannot) enforce referential integrity and you know why you should always use them. Even today there are modern samples of this and usually they just become inconsistent and getting good data out of them is a real pain.

Still I don't know why you don't stick to the subject of Referential integrity and get to using MUCK (or true-lookup-tables). They can become a real pain and they provide no benefit, except a good key to integrity problems. I've designed some lookup tables and the database had almost an equal number of lookup tables compared to other tables. In the end adding the lookup tables paid off because:

  1. A slight addition was needed that the lookup table could provide, not much changes to meet the new requirement.
  2. Adding some more bits and bytes with predefined condities to the lookup table made my filtering work much easier.

The first thing can be done with a MUCK table, the second one cannot. Besided, why complicate a design with such tables?
Even in OO you don't do what you do with MUCK tables, you only take benefit that the object has the same layout (so why not use the same class or definition(!)), but you don't put them in the same array, unless you are, IMHO, some kind of idiot.
It might confuse people that tables are NOT the same as classes, rather we should change SQL to:

CREATE DEFINITION Lookup (Id, Value)
CREATE TABLE CustomerStatus FOLLOWING Lookup
CREATE TABLE BatchStatus FOLLOWING Lookup
CREATE TABLE ... FOLLOWING Lookup
CREATE DEFINITION OrderLookup : Lookup (canceled bit)
CREATE TABLE OrderStatus FOLLOWING OrderLookup
Now we are going the correct way, view definition as class and table as array of a definition. But who's going to build it?


noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12290 Visits: 2048

In line with the previous replies. The lookup tables are (should be) DIFFERENT Entities. The business purpose of the model CAN change therefore the ablity to change will be crippled by using the proposed "UNIFIED" approach.

On another point though the article seems well organized and makes a good sense on the reasons for RI.

HTH\




* Noel
Mark Storey-Smith
Mark Storey-Smith
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 305
Also in agreement with the comments regarding MUCK tables. This has cropped up in various articles on the site, there really should be some moderation and/or editing process to stop anymore appearing. The more often its mentioned the more likely it is that someone will assume its a good practice, which we all know it most definately isn't.
David.Poole
David.Poole
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9997 Visits: 3336
In general I agree with this article but there is one massive BUT that has been overlooked here and that is performance.

I have a database where the design of the database with its referential integrity is such that any attempt to archive off old data means taking the server off-line for a day. Performance is totally unacceptable for deleting data.

I am going to write up my findings on the performance issues with DRI so there is something quantitive rather than emotive to mull over

LinkedIn Profile

Newbie on www.simple-talk.com
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 714
David:

Are you talking about DRI, or "cascading" actions? Note that DRI is only primary key/foreign key constraints. The cascading stuff is not necessary for enforcing any kind of RI, and is only there to make life easier in some cases. If it's too slow, turn it off and write your own scripts.

If it's not cascading actions you're referring to, I'd be interested in why your deletes are so slow that they require taking the server offline. Sounds like you're missing some key indexes to support those deletes?

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
David.Poole
David.Poole
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9997 Visits: 3336
I'm not talking about the infestation known to MS Access programmers as cascading.

I have inherrited a database whose lookup tables would give DCPeterson apoplexy. The design was done by C++ programmers so the reasoning is that a lookup table consists of an id and description, this would be an implementation of a single class therefore this represents an implementation of a single table.

Beyond the lookup tables this also means that other "classes" are also given their own tables when perhaps they shouldn't be. Consequently there are foreign keys all over the place.

There are over 200 tables in the database all cross linked to each other and to the lookup table.

One delete requires a delete from several other tables all of which get stung by DRI.

Basically the database needs scrapping and rewriting but
a) There are several very large business critical applications that interact with this database.
b) The database has to be available 24/7/363 (Yes I mean 363)
c) The redesign would take over one year even before recoding the apps

LinkedIn Profile

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search