I have a table A that has a one to many relationship to table B. (A->>B) At the time A is created a user may choose to create multiple B records before they all get saved.
Problem is that the B records are causing a "unique constraint" error. The first sequence number is always an already chosen ID in the table.
Here is a snippet of the code generated:
SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL
SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL
INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)1:204(submittedby), 2:"Y"(uservisible), 3:14456(jobid),
4:2014-02-27 13:34:27(submitteddate), 5:"2"(notesdescription), 6:37427(notesid), 7:"eyetv serial number.rtf"(attachmentname)>:
INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)1:204(submittedby), 2:"Y"(uservisible), 3:14456(jobid),
4:2014-02-27 13:34:27(submitteddate), 5:"2"(notesdescription), 6:37467(notesid), 7:"eyetv serial number.rtf"(attachmentname)>:
Next exception:SQL State:23000 -- error code: 1 -- msg: ORA-00001: unique constraint (CRE_MASTER.SYS_C001646) violated
The number 37467 is correct but the first number 37427 is not because it is an already existing primary key. huh?
Anyone have any suggestions? Why is the first number invalid and why is the second one valid?
Calven