Evidance table has foreign key (edate, eID) referring table REGISTER.
While inserting value into table Evidance, ensure those values exist in Reigster table.
Otherwise error will be thrown,
ORA-00001: 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.
Action: Either remove the unique restriction or do not insert the key.
1 person found this helpful
you can do either of the below:
Option 1: create exceptions table (script is in $ORACLE_HOME\rdbms\admin\utlexcpt.sql). Disable the constraint. Insert the value. Enable the constraint with EXCEPTIONS INTO EXCEPTIONS clause. you will get error still but then select from EXCEPTIONS table will give you the row creating problem. Once you have the rowid which is creating problem, you can decide whether to change value in your insert statement or delete record already in the table.
Option 2: run the select statement without insert and see if you are getting duplicate value out of the select statement.
Option 3: use
/*+ ignore_row_on_dupkey_index(tab, tab_pk) */. see this Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior - Stack Overflow
Btw, do you think , this insert is correct "insert into evidences (edate,eID,crime, infraction idreg,datereg)", I guess comma(,) is missing between infraction and idreg columns. Also, columns "idreg,datereg" are defined as NOT NULL in EVIDENCES table but columns "judgdement,judgedate" in CONTROLS table are not defined as NOT NULL. you may also have to look into this structural changes or modify your query accordingly so that you don't get NULL as output for these two columns.