ORA-00001: unique constraint primary key violated

Question

DELETE from dbo.T_LIAV_AGENT_STATE_APPROVAL SAP
WHERE EXISTS (SELECT UNIQUE 1 FROM MCS_SYNC_STATE_APPR APP

                inner join MCS_SYNC_NAME SN on SN.SE_NAME_ID = APP.SE_NAME_ID
                                    and SN.ACTION in('U','S')
                Where APP.SE_name_id = SAP.AV_NAME_ID
                and APP.SYNC_INSTANCE_ID = param_inst_ID);
COMMIT;

INSERT INTO dbo.T_LIAV_AGENT_STATE_APPROVAL

SELECT  UNIQUE 

    APP.SE_NAME_ID            AS AV_NAME_ID,
    APP.STATE                 AS AV_STATE,
    APP.APPROVAL_TYPE         AS AV_APPROVAL_TYPE,
    APP.START_DATE            AS AV_START_DATE,
    APP.END_DATE              AS AV_END_DATE,
    APP.APPOINTED             AS AV_APPOINTED,
    APP.RENEWAL_DATE          AS AV_RENEWAL_DATE,
    APP.LICENSE               AS AV_LICENSE,
    COMPANY_NAME_ID           as AV_COMPANY_CODE,
    SYSDATE                   AS TSTAMP,
    SYNC_USER_NAME_ID         AS AV_FIRST_USER_ID,
    SYSDATE                   AS AV_FIRST_DATE,
    NULL                      AS AV_LAST_USER_ID,
    NULL                      AS AV_LAST_DATE

FROM MCS_SYNC_STATE_APPR APP

WHERE exists (select 1 from t_liag_agent AG
                     where AG.ag_name_id = APP.SE_NAME_ID)
and APP.SYNC_INSTANCE_ID = param_inst_ID;

Here is the SQL part and I am getting error when I debug the code:

ORA-00001: unique constraint primary key violated At T_LIAV_AGENT_STATE_APPROVAL...

and these are the 2 columns which the primary key constraint relies on AV_NAME_ID, AV_STATE ..

There is no duplicate data as per me ...Do you think any other reason?

"there is no duplicate data as per me" - but Oracle thinks there is duplicate data and I would trust Oracle here. — a_horse_with_no_name
One reason, you have UNIQUE over multiple columns including SYSDATE.. try TRUNC(SYSDATE) to nullify the time element all to 00:00:00 — Maheswaran Ravisankar
@MaheswaranRavisankar All SYSDATEs in a SQL statement will resolve to exactly the same time. See my answer to a similar question here. — Jon Heller
@JonHeller thanks for that great piece of info... I hope I am unaware of many things!!!! — Maheswaran Ravisankar

Dmitriy Dmitriy · Accepted Answer · 2014-11-04T21:58:11

If you have 'CREATE TABLE' privilege, use dbms_errlog package.

Run script:

begin
  dbms_errlog.create_error_log('DBO.T_LIAV_AGENT_STATE_APPROVAL');
end;
/

Run your INSERT script with an additional clause:

INSERT INTO dbo.T_LIAV_AGENT_STATE_APPROVAL
SELECT ...
FROM ...
LOG ERRORS INTO err$_T_LIAV_AGENT_STATE_APPROVAL REJECT LIMIT UNLIMITED;

Check error logging table for errors:

select * from err$_T_LIAV_AGENT_STATE_APPROVAL;

You will see all records, that violated constraints, and error messages.

ORA-00001: unique constraint primary key violated

3 Answers