Re: Index Creation Questions

Schultheis, Carol L wrote: > > Can someone answer some basic general questions? > > 1. what is the difference between creating a unique index on a column > via the create unique index statement > versus using the primary key constraint when creating the table. The primary key and index are essentially independent. A primary key constraint, or a unique constraint for that matter, requires a unique index to support it. If one exists on the same columns the constraint will use that index otherwise it creates one to use. The index itself is the same either way and the constraint behaves the same. The main gain from constraints over just adding the index is a) the documentation value and b) the integrity support from the engine. > 2. what is the difference between creating an index on a column via the > create index statement > versus using the alter table and adding a foreign key constraint. Same as above except that the index does not have to be unique. Art S. Kagel