Unique constraint violated

oracle

I have code create table

CREATE TABLE OFPHONE
(
  PHONEID     INTEGER                           NOT NULL,
  HOTLINE     VARCHAR2(20 CHAR),
  EXT         VARCHAR2(20 CHAR),
  PHONE       VARCHAR2(30 CHAR),
  PASSWORD    VARCHAR2(30 BYTE),
  NAME        VARCHAR2(30 BYTE),
  CREATEDATE  TIMESTAMP(6)                      DEFAULT CURRENT_TIMESTAMP
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

COMMENT ON COLUMN OFPHONE.PASSWORD IS 'Password';



CREATE UNIQUE INDEX OFPHONE_PK ON OFPHONE
(PHONEID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE OR REPLACE TRIGGER TRIGGER_OFPHONE_AFTER_INSERT2
AFTER INSERT
ON OFPHONE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp varchar2(20);


begin


    insert into ofphone_tmp (PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
    values (:new.PHONEID,:new.HOTLINE,:new.EXT,:new.PHONE,:new.PASSWORD,:new.NAME, sysdate); 

    insert into OFUSER(USERNAME,PLAINPASSWORD,NAME,EMAIL,CREATIONDATE,MODIFICATIONDATE)
    values(:NEW.PHONE,:NEW.PASSWORD,:NEW.NAME,trim(' ' from :NEW.NAME)||'@localhost',sysdate,sysdate);

    --commit;

   for x in 
   (select  phone,name,hotline from ofphone_tmp where hotline = :NEW.hotline and phone <> :new.phone) loop

        insert into ofRoster values (SEQ_OFROSTER.nextval,:new.PHONE,x.phone||'@localhost',3,-1,-1,X.name);

        insert into ofRoster values (SEQ_OFROSTER.nextval,x.phone,:new.PHONE||'@localhost',3,-1,-1,:new.name);


   end loop;

end;
/

I tried debug code snippet

But I checked the details of the value of the block
for cur in (has data)
and select SEQ_OFPHONE (has data)
are valid.

Please tell me this error case.

begin

    for cur in(select
                c.COMPANY_MOBILE,
                e.EMPLOYEE_NAME,
                E.MOBILE_PHONE,
                E.EMPLOYEE_NO,
                E.EMPLOYEE_PWD,
                E.Email
             from TAB_XN_EMPLOYEE e
            inner join TAB_XN_COMPANY c
            on e.COMPANY_ID = c.COMPANY_ID
            and c.COMPANY_MOBILE  = '0912400604'
            ) loop
            insert into ofphone( PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
            values(SEQ_OFPHONE.nextval,cur.COMPANY_MOBILE,cur.EMPLOYEE_NO,
            cur.MOBILE_PHONE,cur.EMPLOYEE_PWD,cur.EMPLOYEE_NAME,sysdate);

    end loop;

end;

Error

ORA-00001: unique constraint (MEG.OFUSER_PK) violated ORA-06512: at
"MEG.TRIGGER_OFPHONE_AFTER_INSERT2", line 20 ORA-04088: error during
execution of trigger 'MEG.TRIGGER_OFPHONE_AFTER_INSERT2' ORA-06512: at
line 15

Best Answer

Given your error ("OFUSER_PK"), this is an indication that the primary key on the OFUSER table is the problem. Check your insert statements for OFUSER - something seems wonky there.