Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Enforcing Referential integrity in Microsoft SQL Server 2000 Expand / Collapse
Author
Message
Posted Thursday, January 6, 2005 6:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 20, 2006 9:21 AM
Points: 34, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlserve


> Nick Duckstein
Post #154165
Posted Monday, January 17, 2005 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:18 PM
Points: 1,035, Visits: 411

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



*****************/
Post #155899
Posted Monday, January 17, 2005 11:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
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
Post #155929
Posted Monday, January 17, 2005 11:23 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:09 PM
Points: 91, Visits: 198

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




Post #155935
Posted Tuesday, January 18, 2005 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 11, 2005 1:07 PM
Points: 160, 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?

Post #156203
Posted Tuesday, January 18, 2005 1:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:34 AM
Points: 6,259, Visits: 2,031

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
Post #156208
Posted Tuesday, January 17, 2006 4:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 28, 2014 3:15 AM
Points: 50, Visits: 290
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.
Post #251197
Posted Tuesday, January 17, 2006 5:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:59 AM
Points: 2,913, Visits: 1,842
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
Post #251203
Posted Tuesday, January 17, 2006 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705

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
Post #251299
Posted Tuesday, January 17, 2006 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:59 AM
Points: 2,913, Visits: 1,842
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
Post #251318
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse