Re: Index Creation Questions

Mark Stock wrote: >Schultheis, Carol L wrote: >> 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 second one creates a unique index AND allows you to use referential >integrity. Not so fast. You imply by your answer, Mark, that you cannot use referential integrity constraints unless you specify the constraint when the table is created. Did you forget about ALTER TABLE? The main difference is that when you create a primary (or foreign) key Online will create an index where required of the appropriate type for the key, UNLESS AN APPROPRIATE INDEX ALREADY EXISTS. The main difference is that under the first scenario, the user has the opportunity to name the index something appropriate; the later the index has a generated name beginning with a space. This makes it singularly difficult to manipulate if the need should arise. What this really means is that if you create the constraint during table creation a system generated name will be used. If you create the table, the index, then use alter table statements to define the constraints, you have the ability to specify the index name, location, and fragmentation strategy to be used. If you don't the index will be created using defaults. Note that this may be OK in any particular case. But I confess to a strong dislike for system names for indexes (and for that matter, constraints). They can make administration a little more difficult at times. David