RE: Primary key uses

As a data warehouse guy. Doing any kind of referential integrity within the DB is a no-no. For example - with duplicates it's cheaper to join your entire input set against a table and discover if you have dups/resolve them than it is to try to insert with a primary/unique index. How much cheaper? Try 20 minutes vs 9 hours. The same can be said for parent/child dependencies. Of course that is the DW world - not the OLTP world. cheers j. -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Tuesday, November 07, 2000 5:03 PM To: informix-list@iiug.org Subject: Re: Primary key uses Michael Hoffman wrote: > > In <8tsrnf$a0a1@www.informix.com> article, Hal Maner mentioned that: > : I believe the general consensus would be/should be to use referential > : integrity in the database via primary and foreign keys. Most modern systems > : implement this. Where you do not find this is typically in systems > : developed by people not knowledgeable on the relational theory > > HARUMPH! :-) > > : and/or people with old file I/O or mainframe experience. > > Bing! Bing! Bing! And as a developer, unless performance took a hit, I > always made sure to include the checks in my software, regardless of the > DB checks. Life is just safer that way. Regardless of the checks in the software I always include checks in the database. Life is just safest that way! Art S. Kagel > : I believe this is better for the following reasons (this is coming from a > : person who is primarily a developer, but also a DBA and a sysadmin): > : 1. Performance is better (with software checks, you have to write code to > : check for these conditions and this usually means two-three database I/O > : statements instead of an internal ref. integrity check). > > : 4. Yes, a unique constraint can ensure uniqueness just as good as a primary > : key can, but a unique index will not do much in a case where people DELETE a > : parent record while they leave the children. A properly established > : primary-foreign key relationship prevents this automatically. You can also > : implement cascading deletes. > > These are the 2 points that I'm most interested in. > > I don't pretend to live in a perfect world, so I understand the errors that > crop up in a database, especially when Indexes go bad (if they didn't, we > wouldn't need Update Stats! :-)). If a Primary Key constraint is just another > form of Unique Index, then it can potentially go bad as well. I thought I > recalled reading in some past c.d.i. article about Update Stats having no > effect on the constraints. Is this true, and if so, what is a cure so > performance is not hampered? UNIQUE and PRIMARY KEY constraints are enforced in the data base using a UNIQUE index. Period. Operationally there is no difference. It is the documentation of the relationship that is really different. Art S. Kagel > I'm **really** not concerned with the primary-foreign key relationships. I > do understand the benefits of using them, but they don't apply, yet, to this > database! That is why there are only 2 of them setup currently, both having > to do with user security checks. In that case, I still hold that software > is a better place to screen so proper error messages and warnings can be > trapped or passed to the user. > > So, for pure indexing purposes, are Primary Key Constraints better or worse > than Unique Indexes?