Index on PK

Hi, version :Informix IDS 9.1.3 Does Informix automatically create unique index for a Primary key when a create table script is run? Michael

Michael Lai wrote: > > Hi, > > version :Informix IDS 9.1.3 > > Does Informix automatically create unique index for a Primary key when > a create table script is run? Assuming you include a PRIMARY KEY constraint? Yes, HOWEVER, from a DBA's point of view you are better off creating the table without the PRIMARY KEY, manually add a UNIQUE index on the key column(s), then alter the table and add the PRIMARY KEY CONSTRAINT which will then use your existing index. The reason for this is that the PRIMARY KEY generated index will have a name which is invalid (it has a leading space) and so cannot be manipulated with normal DDL commands. This creates the following problems: o Cannot specify the index in an optimizer directive o Cannot reorganize the index using disable/enable o Cannnot drop the index explicitely and recreate manually if it becomes damaged or inefficient o Cannot use ALTER FRAGMENT ON INDEX ... INIT IN to move it to a different dbspace for space sake or to reorganize it Art S. Kagel