ORA-00001 unique constraint (string.string) violated

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.