10 Replies Latest reply: Sep 27, 2010 3:49 AM by Saubhik RSS

    ORA-00001: unique constraint violated error

    user640001
      Hi,

      Oracle9.2

      Table T has four columns A,B,C,D all are not null. Also, unique constraint is defined on column A & B

      but, one of the insert statement got failed with the below error.

      Getting DB Error: ORA-00001: unique constraint violated error on inserting the value.

      It means already record is existing in the table.

      As, thousands of records are getting inserted online in the table at a time, so could not able to trace which record is being inserted.

      So, how to find that record in the table which is already existing due to which above unique constraint violation error is raised ?


      With Regards
        • 1. Re: ORA-00001: unique constraint violated error
          CKPT
          hi


          Error: ORA 1
          Text: unique constraint <table.column> violated
          -------------------------------------------------------------------------------
          Cause: An update or insert statement attempted to insert a duplicate key.
          Action: Either remove the unique restriction or do not insert the key.
          For Trusted Oracle7 configured in DBMS MAC mode, you may see this
          message if a duplicate entry exists at a different level.


          check in the insert statement which was already in the table.. and also constraint assigned on that column.. if unique constarint then give another value
          Thanks
          • 2. Re: ORA-00001: unique constraint violated error
            user640001
            Hi,

            but, I want to find that record in the table and want to delete, so how to find that record ?

            With Regards

            Edited by: user640001 on Sep 27, 2010 12:00 AM
            • 3. Re: ORA-00001: unique constraint violated error
              CKPT
              can you please post the transaction which you are inserting...

              and also post
              select constraint_name, constraint_type from user_constraints where table_name='&table_name';
              • 4. Re: ORA-00001: unique constraint violated error
                user640001
                Hi,


                As all transactions are online, so neither can trace the transactions nor have any record details which is creating the problem.

                From the db, I came to know that a composite primary key is defined including two columns A,B which is restricting that record.

                ALTER TABLE T ADD (
                CONSTRAINT T_PK
                PRIMARY KEY
                (A,B));

                Just having the error details and knowing that insert statement is failing due to above constraint

                With Regards
                • 5. Re: ORA-00001: unique constraint violated error
                  Tubby
                  And why do you need to identify the statement which is trying to violate this constraint?

                  Perhaps starting there will give us a little background information we're presently missing.
                  • 6. Re: ORA-00001: unique constraint violated error
                    CKPT
                    so what was the issue now? did you understood what is the error and why?
                    • 7. Re: ORA-00001: unique constraint violated error
                      user640001
                      Hi,

                      I want to delete that record.

                      so, I need sql statement to find out that record.


                      With Regards
                      • 8. Re: ORA-00001: unique constraint violated error
                        CKPT
                        thats why iam asking which row you are trying to insert, which column has constraint enabled? post the query which you are trying to insert,
                        and the details of constraints of a table using user_constraints
                        select constraint_name, constraint_type from user_constraints where table_name='&table_name';
                        according to that you can delete the existing query... THanks
                        • 9. Re: ORA-00001: unique constraint violated error
                          Tubby
                          user640001 wrote:
                          Hi,

                          I want to delete that record.

                          so, I need sql statement to find out that record.


                          With Regards
                          The question of "why" still hasn't really been answered.

                          If the inserts are coming from an application, and the table has a unique constraint placed on it to protect the data, WHY do you think the application attempting to violate this constraint has "better" data?

                          If this is indeed the way the application needs to function, then you need to modify the application to account for this.
                          begin
                             insert into table1 (<columns>) values (<values>);
                          exception when dup_val_on_index
                          then
                             update table1 
                                set <columns_values>
                             where column_a = :variable_a
                             and column_b = :variable_b
                          end;
                          Instead of the current
                             insert into table1 (<columns>) values (<values>);
                          Which doesn't attempt to catch the unique constraint error.

                          Since you do not at present know the data trying to violate the constraint (that's your entire question) i find it difficult to understand how you could presume to know that it is correct ... you know nothing about it so the course you are on seems to be the incorrect one (my opinion anyway).
                          • 10. Re: ORA-00001: unique constraint violated error
                            Saubhik
                            As all gurus suggested, I am also not clear about what you want to do. However, to catch the constraint exceptions into a table, you should look at
                            1. ALTER TABLE table_name ENABLE CONSTRAINT con_name EXCEPTIONS INTO EXCEPTIONS
                            2. utlexcpt.sql
                            In that way you can find the errors and the data causing the error.