Re: Oracle 8: DROP INDEX

Hi,

Oracle always uses an Index to enforce PK's, up until Oracle8i this has needed to be a Unique Index, but now I believe Non-Uniques do the job equally well.

Anyway, when the PK is created in your example, the PK effectively takes ownership of the Index thereby generating the error message when you try and drop the index.

If you create the PK before the Index, then Oracle will actually create an Index for you, with a name of SYS_ or some other meaningless name, and then your index is not locked for the PK's use.

Hope this helps,
Andrew

cristian wrote:

> The following mistake happens when I try to turn off an index in Oracle
> 8.0.4:
> "ORA-02429: cannot drop index used for enforcement of unique/primary key”.
> However that mistake only happens when happened the following situation:
>
> CREATE TABLE TESTEINDICE (COL1 NUMBER(4,0) NOT NULL, COL2 NUMBER(4,0) NOT
> NULL, COL3 NUMBER(4,0) NOT NULL) /
>
> CREATE UNIQUE INDEX TESTEINDICEIndice2 ON TESTEINDICE (COL1,COL2) /
>
> ALTER TABLE TESTEINDICE ADD(PRIMARY KEY (COL2,COL1)) /
>
> DROP INDEX TESTEINDICEIndice2 /
> When executing this command the mistake it happens.
> If I turn off the primary Key the index it disappears, to the you create
> again it the index it reappears with other name.
> To create the primary key first and only later the index that mistake
> doesn't happen.
>
> How do I do to turn off this index?
Received on Mon May 24 1999 - 18:41:49 CDT