Primary Keys and Indexes

[ChrisQuick]

When you create a table in Oracle 8.0.5 like so:

CREATE TABLE TEST
(
TEST_SEQ_NUM NUMBER NOT NULL PRIMARY KEY,
TEST_NAME VARCHAR2(25)
)

Does it make sense to also do:

CREATE INDEX TEST_PK ON TEST (TEST_SEQ_NUM)


Doesn't declaring a column as a primary key, automatically create an index on that column as well?

 

[amolso]

No It will Not

'CREATE INDEX ' will just create an index which will be useful for scanning the data fast.

Primary key will create a unique index on the field
which will be created using the syntax
'CREATE UNIQUE INDEX ...'
This will also not serve the purpose fully. Even if it will create the unique index as far as RDBMS concept of FOREIGN KEY is concerned it will not support as foreign key to other table. Therefore Indexes can be used for scanning fast and storing unique data based on conditions but it cannot replace anytime to primary key

You will need to go through RDBMS Books, it will help you a lot.

Amol

 

[ChrisQuick]

Well, since the primary key is declared during the table creation, and that in turn creates a unique index on the column, why would anyone create a table as shown above, and then immediatly follow that with a plain non-unique CREATE INDEX statement? Isn't that redundant?

 

[amolso]

Yes It is

Because when you create a primary key a unique index for that column is automatically created in Oracle (refer USER_INDEXES table)

In the above case if you want to search something quickly on based on TEST_NAME then you can use
'CREATE INDEX ..' on the fiels 'TEST_NAME' OR
if you want TEST_NAME also not to be duplicated then you may create a unique index on 'TEST_NAME' .This will also serve purpose of searching quickly but will not allow to duplicate the name

OK
Amol

 

[ChrisQuick]

Thanks. The reason that I ask is, we have a very larger (at least I think it is) database with over 1800 tables and for EACH table they declared the primary keys while creating the tables, and then created non-unique indexes on the primary key as well. So that means we have a BUNCH of indexes we don't really need.

 

[MikeLacey]

Chris,

Can I suggest that you double check that you really *do* have two indexes defined for each primary key?


Mike
michael.j.lacey@ntlworld.com

http://www.cargill.com/

 

[amolso]

Chris you will have to check again

Actually if a particular column is a primary key
then Oracle don't allow another unique index on that
column. If you try , it will show following error
(provided if it does not include any other column)

ERROR at line 1:
ORA-01408: such column list already indexed

Please see to it that unique indexes are there only on primary key columns
If those indexes you are seeing from user_indexes or all_indexes it may be your primary key itself
(index name in primary key is usually like SYS_C00... something number)

Amol