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