Primary Keys vs Unique Index
Asked whether using a PRIMARY KEY constraint instead of a plain UNIQUE INDEX (on IDS 7.31) causes performance or other problems. Replies: no real performance difference, since Informix implements primary keys with a unique index. Advantages of primary keys: needed as targets for foreign keys/referential integrity, useful for replication, constraint checking deferred until triggered actions finish, and they automatically imply NOT NULL. Caveats noted: auto-generated index names start with a space (problems with MS Access via ODBC) and may violate naming standards — avoidable by creating the named index first and then the constraint. Consensus: keep the primary keys; don't convert them.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there any performance or other known problem with using a Primary Key with IDS 7.31.UC2A as opposed to a Unique Index? It seem to me that they do the same exact thing in a slightly different manner. I have always used Unique Index's -- I am now working on an db that uses Primary Keys rather than Unique Indexes. I am tempted to change all the Primary Keys to Unique Indexes. Is one of these preferred and why? Greg
this is not a 'known problem' but something i thought of ... if you ever need to replicate the tables, you will need a primary key to resolve those issues... "Gregory P. Schin" wrote: > Is there any performance or other known problem with using a Primary Key > with IDS 7.31.UC2A as opposed to a Unique Index? > > It seem to me that they do the same exact thing in a slightly different > manner. > I have always used Unique Index's -- I am now working on an db that uses > Primary Keys rather than Unique Indexes. I am tempted to change all the > Primary Keys to Unique Indexes. Is one of these preferred and why? > > Greg
Primary keys will allow you to enforce referential integrity. a foriegn key needs a primary key to refer to. Referential constraints are sometimes very important. If referential integrity of data is not an issue then maybe you would not use primary keys. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------
Thanks for the replies I am referring to unique indexes where no referential constraints/foreign keys are involved. There are instances where primary/foreign keys exist in this database and I understand the relationship there. The person that setup this database used primary keys instead of unique indexes even where there were no foreign keys/constraints involved. Is this now just considered standard? Steven Hauser wrote: > Primary keys will allow you to enforce referential integrity. > a foriegn key needs a primary key to refer to. Referential constraints > are sometimes very important. > > If referential integrity of data is not an issue then maybe you would not use > primary keys. > -- > --------------------------------------------------------- > Steven Hauser > email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 > ---------------------------------------------------------
Primary key and Unique index are different concepts although they all enforce uniqueness. Primary Key is a "Constraint" which has all features of a contraint. For example contraint checking is deferred until all triggerred actions are complete. In relational database theory "index" is a physical design issue (to improve query speed, etc) while primary key is a logical design issue (ideally you should have a primary key for each table). Informix internally uses indexes to implement primary keys so you have all advantages of a unique index on primary keys. Generally if the column(s) meets the criterias of primary key then a primary key is preferred. Regards, Carl Wu Gregory P. Schin wrote in message <39344422.419B20BE@lucent.com>... >Is there any performance or other known problem with using a Primary Key >with IDS 7.31.UC2A as opposed to a Unique Index? > >It seem to me that they do the same exact thing in a slightly different >manner. >I have always used Unique Index's -- I am now working on an db that uses >Primary Keys rather than Unique Indexes. I am tempted to change all the >Primary Keys to Unique Indexes. Is one of these preferred and why? > >Greg > >
gps@lucent.com schrieb: > I am referring to unique indexes where no referential constraints/ > foreign keys are involved. There are instances where primary/ > foreign keys exist in this database and I understand the relationship > there. The person that setup this database used primary keys instead > of unique indexes even where there were no foreign keys/constraints > involved. Is this now just considered standard? Depends on what you want. Referential or primary/foreign key constraints are in no way essential for a database to function. They can make life easier, however, and they will create the necessary indexes for you. I wouldn't bother changing the primary key constraints to unique indexes, since the unique indexes are already there. However, there are a few scenarios where the auto-generated indexes can be a problem: 1) You want to map an MS-Access table to an Informix table via ODBC. This will lead to problems if the index name begins with a space, as is the case in auto-generated indexes. 2) Company standards require you to adhere to a certain naming scheme for indexes, constraints etc. You can overcome this by first creating the indexes with the names you want and then creating the constraints, which will use existing indexes. HTH, Richard -- +----------------------------+---------------------------------------+ | Dr. med Richard Spitz | E-Mail: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum der Univ. München | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +----------------------------+---------------------------------------+
"Gregory P. Schin" wrote: > Is there any performance or other known problem with using a Primary Key > with IDS 7.31.UC2A as opposed to a Unique Index? > > It seem to me that they do the same exact thing in a slightly different > manner. > I have always used Unique Index's -- I am now working on an db that uses > Primary Keys rather than Unique Indexes. I am tempted to change all the > Primary Keys to Unique Indexes. Is one of these preferred and why? > > Greg A tiny point : Primary Keys also automatically enforce the NOT NULL constraint on the columns that it is composed of, unlike a unique index where you need to have independent NOT NULL constraints on each column. Rudy