ORA-00001 unique constraint violated is one of the common messages we often get while loading data.
This ORA-00001 unique constraint violated error occurs when You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
Check list to run to resolve ORA-00001
We can perform the following action items for this ORA-00001
(1) You can look at the error and find the constraint information with the below sql
SELECT column_name, position FROM all_cons_columns WHERE constraint_name = '<name of the constraint in error>' AND owner = '<table owner>' AND table_name = '<table name>'
Now we can check the existing data with the data we are inserting and then take action accordingly. You can change the keys so that they can be inserted
CREATE TABLE EXAMPLE_UNIQ(
EXAM_ID NUMBER NOT NULL ENABLE,
EXAM_NAME VARCHAR2(250) NOT NULL ENABLE
CONSTRAINT UK1_EXAMPLE UNIQUE (EXAM_ID, EXAM_NAME) ENABLE
);
table created.
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
1 rows inserted.
Commit;
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, 'XYZ');
SQL Error: ORA-00001: unique constraint (UK1_EXAMPLE) violated
Solution
INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1001, 'XYZ');
1 rows inserted.
(2) We can drop the constraint if duplicates are allowed in the table
Example
alter table <table name> drop constraint <constraint name>;
(3) The 11gr2 hint ignore_row_on_dupkey_index allows the statement to silently ignore ORA-00001 errors.
- The IGNORE_ROW_ON_DUPKEY_INDEX hint are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.
- The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.
The semantic effect of this hint results in error messages if specific rules are violated:
- If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.
- You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.
- You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.
- As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.
insert /*+ ignore_row_on_dupkey_index(unique_table, unique_table_idx) */ into unique_table (select * from non_unique_table);
Related articles
ORA-00911: invalid character
ORA-03113: end-of-file on communication channel
ORA-00257
ORA-29285: file write error
ORA-29913 with external tables
delete query in oracle
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm