ORA-00001 unique constraint (string.string) violated
Error
ORA-00001 unique constraint (string.string) violated
CREATE TABLE country
(
id CHAR(2) PRIMARY KEY,
name VARCHAR2(90)
);
INSERT INTO country VALUES ('IN', 'INDIA');
INSERT INTO country VALUES ('US', 'USA');
SQL> INSERT INTO country VALUES ('US', 'USA');
INSERT INTO country VALUES ('US', 'USA')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C008205) violated
Cause
While we inserting or update the data into a table which has primary key or unique key define on it, then Oracle are not to insert because of uniqueness key present on the columne.
Solution
1. You can correct your data while inserting or updating.
2. You can disable the constraint if your data is write.
-- Fetch the table name having unique constraint
SQL> SELECT table_name FROM all_constraints WHERE constraint_name = 'SYS_C008205';
OR
SQL> SELECT DISTINCT table_name FROM all_indexes WHERE index_name = 'SYS_C008205' ;
TABLE_NAME
-----------------------
COUNTRY
-- Disable the constraint if you need to insert the table.
alter table COUNTRY DISABLE constraint SYS_C008205;
-- Insert duplicate entry for USA country.
SQL> INSERT INTO country VALUES ('US', 'USA');
1 row created.
Note:
You can enable only if no duplicate date in the table if you tried on enable got following error:
SQL> alter table COUNTRY ENABLE constraint SYS_C008205;
alter table COUNTRY ENABLE constraint SYS_C008205
*
ERROR at line 1:
ORA-02437: cannot validate (TEST.SYS_C008205) - primary key violated
Check with Oracle Utility OERR
#oerr ORA 00001
00001, 00000, “unique constraint (%s.%s) 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.