0

The below insert statement is not working saying:unique primary key violated ora-0001. Do you know why it is not working ? primary key is the seq id

INSERT INTO VITA_TRANSACTION ( VTR_SEQ_ID,VTR_QUELLE_SEQ_ID,VTR_QUELLE,
  VTR_PROVIS_EXPORT_LOG_ID,VTR_DWH_EXPORT_LOG_ID,VTR_SYS_DATE,
  VTR_PROCESSSTATE,VTR_IMEI_NR,VTR_IMEI_ZUORDNUNG,VTR_ZUORDNUNG_DATE,
  VTR_IMEI_KNZ,VTR_SUBSCRIBER_NO,VTR_INSERT_TYPE,VTR_ERFASSUNG_DATUM,
  VTR_VOID_ACTIVATE,VTR_QUELLSYSTEM,VTR_VORGANG,VTR_STORNO,VTR_SALES_PRICE,
  VTR_ARTICLE_NO)
values (SEQ_VITA_TRANSACTION.nextval,418912,'M-ABVK',null,null,sysdate,
  'UM','352523003062648','352523003062648',
  to_date('20160118194708', 'YYYYMMDDhh24miss'),null,32927785,'AK',
  to_date('20160118000000', 'YYYYMMDDhh24miss'),'60000661','Activate',
  'Act','N',2000,'123123')
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
hhamaky
  • 1
  • 2
  • That's a little hard to understand but usually you omit the primary key from the statement if the column is set to auto increment – Matt Mombrea Jan 18 '16 at 18:36
  • Which one exactly is the primary key here? I can see VTR_SEQ_ID and VTR_QUELLE_SEQ_ID and no just "seq_id". – theDima Jan 18 '16 at 18:43
  • the primary key here is VTR_SEQ_ID, isn't that SEQ_VITA_TRANSACTION.nextval increments the seq id to avoid violation ? I didn't write that code. I am trying to understand to know what is the problem with that violation – hhamaky Jan 18 '16 at 18:45
  • Do you have records in the table which were created with manually-set PKs, either before the sequence was created or in parallel? Are records *still* inserted without using the sequence - are you trying to mix both user-supplied and sequence values (which is a bad idea)? If not then you can reset your sequence higher than any existing PK value. – Alex Poole Jan 18 '16 at 19:08

3 Answers3

0

PK violation means that your table already contains the id that your sequence is creating. Try check the value of SEQ_VITA_TRANSACTION.nextval and the max(VTR_SEQ_ID) from VITA_TRANSACTION: select 'seq', SEQ_VITA_TRANSACTION.nextval from dual union all select 'tab', max(VTR_SEQ_ID) from VITA_TRANSACTION

Aleksej
  • 22,443
  • 5
  • 33
  • 38
0

This may not the exact answer to your question but I am trying to debug your problem.

ORA-0000

1: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

So duplicate entry already exists in VITA_TRANSACTION table.

First you should retrieve the current value of the sequence without increment it

Sample 

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = '<sequence owner>'
   AND sequence_name = '<sequence_name>';

For detail follow this question How to retrieve the current value of an oracle sequence without increment it?

Then do a search in the VITA_TRANSACTION where VTR_SEQ_ID= retrieved current value + 1

I am quite sure you will get one row.Now to avoid this error you can increment your sequence value by executing the below sql.

select SEQ_VITA_TRANSACTION.nextval from dual;

It will increment the value of your current sequence by 1(if your sequence INCREMENT BY 1) . Then try to again execute your insert query.

Community
  • 1
  • 1
Bacteria
  • 8,406
  • 10
  • 50
  • 67
0

Primary Key violation usually occurs if you are inserting already exist values in the table. So you can check the records on the table VITA_TRANSACTION for the next sequence i.e SEQ_VITA_TRANSACTION.nextval, you can use following query:

Select 
   count(*) 
From **VITA_TRANSACTION** 
where 
   VTR_SEQ_ID = **SEQ_VITA_TRANSACTION.nextval**;

If the above query returns count as 1 then there is already a record for that sequence. Then try by increasing the sequence value or you can delete the record. It's your call. Hope this answers your question.

PPoudel
  • 1
  • 1