Re: To index or not to index ? that is the (constraint) question.

billy@west.co.za (Billy Wheeler) wrote: :> Now, back to the master table and the real question I'm asking. Given :> that I've got a unique constraint on the primary key column on the :> master table, is it necessary to also create a unique index on that :> column, or is that implicit in the constraint? :A PRIMARY KEY implies a UNIQUE CONSTRAINT implies a UNIQUE INDEX. (And NOT :NULL). So you only have to specify the PRIMARY KEY. Yes, indeed. But still: Here's how we create indexes and primary keys: create unique index tablename_primary on tablename (columnname/s); alter table tablename add constraint primary key (columnname/s) constraint tablename_primkey; to avoide problems with informix generated constraint names that start with a space. You get problems with MS Access at least and possibly general ODBC problems with these names. Also the above gives you a separation of the index and the primary key constraint so you can drop and recreate the index independent of the primary key. So the moral is that the sentence: :A PRIMARY KEY implies a UNIQUE CONSTRAINT implies a UNIQUE INDEX. (And NOT :NULL). So you only have to specify the PRIMARY KEY. doesn't *allways* hold. It holds only if you have specified the primary key as part of the create table statement - which we don't. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org