Hi,
I created an unique constrain on a table. And then removed it to include one more column. Then again I try to create it (with same name) -
ALTER TABLE EMB_PRINT_OUTWARD_DETAIL
ADD CONSTRAINT EP_OUT_DET_UNIQ_FID_PART
UNIQUE (FAB_INWARD_DET_SEQ_NO, PURPOSE, PART_ID);
I get error as -
ORA-02261: such unique or primary key already exists in the table
I thought it still exists and I try to drop it again -
ALTER TABLE EMB_PRINT_OUTWARD_DETAIL drop CONSTRAINT EP_OUT_DET_UNIQ_FID_PART;
but I get the error as -
ORA-02443: Cannot drop constraint - nonexistent constraint
If I try to search that with below query, I get no data found -
select * from USER_constraints where constraint_name = 'EP_OUT_DET_UNIQ_FID_PART';
If I try that from SYS user account, still no such constraint present -
select * from dba_constraints where constraint_name = 'EP_OUT_DET_UNIQ_FID_PART';
And if I try to perform an insert I get error -
ORA-00001: unique constraint (PAN_GAR.EP_OUT_DET_UNIQ_FID_PART) violated
That mean, the constraint exists on the database ! But it is not visible to delete or even recreate ! I use Oracle 11g (11.2.0.x)
Can you please let me know what's going wrong here. Thanks,
-Anand