Simulation and Analysis on the unique constraint index cannot be deleted after Oracle deletes the primary key constraint for 10 GB

When you create a unique constraint before creating a primary key constraint, if you use a common method to delete the primary key constraint, the unique constraint index will not be deleted, which is a PROBLEM of Oracle 10 Gb.

This article uses an experiment to show you how to solve this problem.

[Symptom]
In a 10g environment, the ORA-00001: unique constraint (SEC. PK_T) violated error is still reported when duplicate data is inserted after the primary key constraint is deleted.
The primary key constraint is successfully deleted, but the unique constraint index is not cascade.

[Problem simulation]
1. Create Table T
Sec @ ora10g> create table t (x int, y int );

Table created.

2. Create a unique constraint index first
Sec @ ora10g> create unique index pk_t on t (x );

Index created.

3. Create a primary key constraint.
Sec @ ora10g> alter table t add (constraint pk_t primary key (x ));

Table altered.

4. View Constraints
Sec @ ora10g> col OWNER for a5
Sec @ ora10g> col CONSTRAINT_NAME for a30
Sec @ ora10g> col TABLE_NAME for a10
Sec @ ora10g> col INDEX_OWNER for a12
Sec @ ora10g> col INDEX_NAME for a10
Sec @ ora10g> select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_OWNER, INDEX_NAME from user_constraints where table_name = 'T ';

OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_OWNER INDEX_NAME
--------------------------------------------------------------------
SEC PK_T p t sec PK_T

5. View index information
Sec @ ora10g> select INDEX_NAME, INDEX_TYPE, GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE G
--------------------------------------
PK_T NORMAL N

GENERATED field description:
GENERATED VARCHAR2 (1) Indicates whether the name of the index is system generated (Y) or not (N)

6. Delete the primary key constraint
Sec @ ora10g> alter table t drop constraint pk_t cascade;

Table altered.

7. Confirm constraints and index Deletion
Sec @ ora10g> select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_OWNER, INDEX_NAME from user_constraints where table_name = 'T ';

No rows selected

Sec @ ora10g> select INDEX_NAME, INDEX_TYPE, GENERATED from user_indexes;

INDEX_NAME INDEX_TYPE G
--------------------------------------
PK_T NORMAL N

The index is not deleted.
Therefore, if duplicate data is inserted, the system still reports a violation of the "constraint"
Sec @ ora10g> insert into t values (1, 1 );

1 row created.

Sec @ ora10g> insert into t values (1, 1 );
Insert into t values (1, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SEC. PK_T) violated

1 2 Next Page