NOTE: This explanation only applies when the error message specifies "_PK"!


CAUSE: Biotics is trying to assign a primary key (PK) that has already been used.  This can happen if you were doing "back-door" database inserts and didn't use the right SQL syntax.


SOLUTION: Run the queries as instructed in the update-sequence-when-out-of-synch solution.


PREVENTION: When writing insert statements, set the primary key using the getnextseq funtion to prevent this problem. See the Solution: GETNEXTSEQ function for further details.


BACKGROUND: In Biotics 5, all primary keys are set by Oracle sequences.  If you were familiar with the next_id in the NEXT_SEQ table in Biotics 4, forget about it!  It no longer controls the primary key values. The sequence is created for each table the first time a row gets added to the table in Biotics 5. The sequence name is always [table name]_seq, except for ELEMENT_SEQ, which applies to ELEMENT_GLOBAL, ELEMENT_NATIONAL, and ELEMENT_SUBNATIONAL.


Once a sequence is created, there's no way to tell it to use a different 'next ID', so if it gets out of sync with the data records, the best way to fix it is to  drop and recreate the sequence. 


If running the FIX_NEXT_SEQ procedure does not resolve the problem, the sequence will need to be dropped and recreated, as explained below.  That being said, the biotics_dlink user does not have the appropriate permissions to do so currently.  Therefore, if this needs to be done, submit a ticket to the Biotics 5 help desk explaining that the fix_next_seq procedure has not fixed the error with the XXX sequence, requesting that the sequence be dropped and recreated.  Attach a screenshot of the error to the ticket.

SOLUTION:
1. Find the maximum primary key ID actually used in the table. Example: select max(visit_id) from visit

2. Find the next value that the sequence wants to assign:
select last_number from user_sequences where sequence_name = 'VISIT_SEQ'


******* If last_number is less than or equal to max(ID) and hence needs to be increased, resolve it according to this solution.


****** If last_number is greater than or equal to max(ID) and hence needs to be decreased, it must be dropped & recreated, according to the following instructions:


Drop and then recreate the sequence, using the number equal to max ID +1 as the "start with" and "MINVALUE" values (substitute for x):

DROP SEQUENCE visit_seq;

CREATE SEQUENCE visit_seq
INCREMENT BY 1
START WITH 7500
NOMAXVALUE
MINVALUE 7500
CACHE 20
 NOCYCLE;