4 Replies Latest reply: Mar 5, 2013 10:26 AM by vuatsc RSS

    deleted constraint still enforce the constraint

    vuatsc
      I have a two-column table which has a couple of constraints one of which is to keep the pair of these two columns unique. When I insert the first record ( the table is empty ) I get that constraint 's violation. I should not have that error. Disabling the constraint does not help so I delete the constraint. Now I can insert that record, but when I try inserting the same record, I have that violation again with the name of the deleted constraint.

      Below is the output: ( the deleted constraint is EQ_FOR_QUOTE_HAZ_HAZMAT_RFQ )
      SQL> delete from req_for_quote_haz;
      1 row deleted.
      SQL> commit;
      Commit complete.
      SQL>  select constraint_name from all_constraints where table_name='REQ_FOR_QUOTE_HAZ';
      CONSTRAINT_NAME
      ------------------------------
      REQ_FOR_QUOTE_HAZ_HAZMAT_ID_FK

      SQL>  insert into REQ_FOR_QUOTE_HAZ values (1815,1);
      1 row created.

      SQL>  insert into REQ_FOR_QUOTE_HAZ values (1815,1);
      insert into REQ_FOR_QUOTE_HAZ values (1815,1)
      *
      ERROR at line 1:
      ORA-00001: unique constraint (ITS.REQ_FOR_QUOTE_HAZ_HAZMAT_RFQ) violated

      SQL>
      Any ideas ?

      Thanks,
      Vu