Re: Regarding Oracle Constraints and Indexes

When you create a primary key, oracle creates an index for it. Therefore when you drop the primary key, the index goes away. I reckon oracle effectively creates a special index which is used to enfore the primary key 'constraint' exactly the same way an index would, as oppose to re-inventing the wheel.

You can effecively create a primary key by creating a unique index on one or more columns. The difference, if i remember correctly, between the primary key and unique index is that no columns in a primary key index can have null values, however those in a unique index can, so long as the index remains unique.

"rbhatraju" <venkataramana.bhatraju_at_nospam.com> wrote in message news:9c7470563109f6c09c55bb24a18d77b0_at_localhost.talkaboutdatabases.com...
> Actually I have created a new table with a not null column cust_id,
> followed by a unique index and then added a primary key on this column. It
> gave me a system generated name for the PK constraint. So to have a
> legitimate name, I dropped the primary key using
> SQL> ALTER table tablename drop primary key;"
>
> SQL> select index_name, tablespace_name from dba_indexes where owner='XXX'
> and table_name='XXX';
>
> no rows selected
>
> When I searched for index it gave me no rows.
>
> My question is dropping a primary key drops the index as well in oracle?
> This won't behave the same way in DB2 (even if the primary key gets
> dropped the unique index remains)
>
> Thanks and regards in advance.
> Venkat
>
Received on Wed Feb 02 2005 - 16:18:08 CST