Finding ghost constraint from Oracle DB

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_indexesWHERE  index_name = 'PK_USERSAPPLICATIONS'  /

Alternatively,

select index_name from user_indexeswhere table_name = 'USERSAPPLICATIONS'and  uniqueness='UNIQUE' /

or

select index_name from user_ind_columnswhere 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) violatedSQL>

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) violatedSQL>

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).

Try to check for index for this columns. In some cases index associated with constraint isn't dropped after constraint deletion