Finding ghost constraint from Oracle DB

Question

I had a constraint in a table

CREATE TABLE "USERSAPPLICATIONS" (
    "USERID" NUMBER NOT NULL ,
    "APPLICATIONNAME" VARCHAR2 (30) NOT NULL ,
 CONSTRAINT "PK_USERSAPPLICATIONS" PRIMARY KEY ("USERID","APPLICATIONNAME") 
) 
/

Two weeks ago I modified the table, added some columns, deleted the constraint "PK_USERSAPPLICATIONS" and added a surrogate key. I can see in Oracle SQL Developer that the constraint PK_USERSAPPLICATIONS does not exist anymore.

Regardless of that, when I try to add two entries with the same userid/applicationName combination, I get an error

SQL Error: ORA-00001: unique constraint (ACCOUNTMP1.PK_USERSAPPLICATIONS) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

When I execute the statement

SELECT *
FROM   user_cons_columns
WHERE  constraint_name = 'PK_USERSAPPLICATIONS' 

I get zero rows. How can that be? Oracle shouldn't have any knowledge of the constraint PK_USERSAPPLICATIONS as it has been deleted already weeks ago, and I cannot see it in the database either.

Answer 1

Do you still have the index which was used by that constraint? Because unless you included the DROP INDEX clause when you dropped the constraint it will still be there. Start with

SELECT * 
FROM   user_indexes
WHERE  index_name = 'PK_USERSAPPLICATIONS'  
/

Alternatively,

select index_name 
from user_indexes
where table_name = 'USERSAPPLICATIONS'
and  uniqueness='UNIQUE' 
/

or

select index_name 
from user_ind_columns
where table_name = 'USERSAPPLICATIONS'
and  column_name in ('USERID' ,'APPLICATIONNAME')  
/

edit

Proof of concept

SQL> create table t23 (id number not null, alt_key varchar2(10) not null)
  2  /

Table created.

SQL> create unique index t23_idx on t23 (id)
  2  /

Index created.

SQL> alter table t23 add constraint t23_pk primary key (id) using index
  2  /

Table altered.

SQL> insert into t23 values (1, 'SAM I AM')
  2  /

1 row created.

SQL> insert into t23 values (1, 'MR KNOX')
  2  /
insert into t23 values (1, 'MR KNOX')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_PK) violated

SQL>

So the constraint works. What happens if we drop it, without the DROP INDEX clause?

SQL> alter table t23 drop constraint t23_pk
  2  /

Table altered.

SQL> insert into t23 values (1, 'MR KNOX')
  2  /
insert into t23 values (1, 'MR KNOX')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_IDX) violated


SQL>

Note the subtle change in the error message. The second failure references the index name, whereas the original message referenced the constraint. If the index name is the same as the constraint name it would be hard to diagnose this.

If you don't explicitly pre-create the unique index Oracle's default behaviour is to create a non-unique index. Consequently, dropping the constraint without dropping the index does not cause this problem. (Caveat this behaviour is true of 11g. I presume - but cannot be sure - that it is also this way in earlier versions).