Re: Primary key uses

Michael Hoffman wrote: > > Hi all, > [Informix 7.3 (migrating -> 9.2), Sun Solaris 5] > > In the current database, nearly every table has a Primary Key set, yet > there are only 2 Referential Keys setup. Most of these primary keys are not > being referenced, other than for data validation and data retrieval. Every time you select a single record, you use the primary key. Although if the full relational model has been implemented, then there should be more than 2 referential constraints. > What would be the differences (both positive and negative) caused by > dropping these restraints and replacing them with Unique Indexes? Of course, > I'll leave the single Primary key that is being referenced active until we > modify the software to handle the constraint. Restraints! I didn't know you were wearing any! ;-) The difference at the database level is not much. A unique index is exactly the method that a primary key uses to maintain data integrity. However, in reality, the use of constraints makes your life a whole lot easier. The relational model is maintained even before a line of code has been written. Also, if the model changes during the development project (yes I know that would never happen, but does on every project :) then you can change the table relationships without having to alter any application code..., well almost. :-) > Is it the general concensus that Referential Integrity should be > handled on a software level or via triggers? Depends what you mean by software. If you mean the application, then no. If you mean the database server, then yes. Triggers are generally used to implement business rules, not maintain referential integrity. Don't use triggers unless you have to for performance reasons. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+

Thanks for all the responses! I could probably start a huge debate about Referential Integrity checking on the database level (less I/O, ease of adaptation, ease of analysis) versus on the software level (ability to question the user for solutions!), but that's for another day. (anyone taking the bait? :-) I can hear Jake Soloman: "Now, now Mike.... why do you always need to stir up trouble on your first days?" :-)) Anyway, I think I may follow Carlton Doe's suggestion to build Unique Indexes first, especially on our soon-to-be fragmented tables, then add the Primary Key constraints. Whether we ever use them in a primary-foreign key style remains to be seen. The application is still evolving, but so far, those checks are not necessary. (Yes, the DB is a relational db, but there is really only one field tying most of the tables together.) Thanks again. Michael Hoffman