Ошибка ora 00001 unique constraint

Have you gotten an “ORA-00001 unique constraint violated” error? Learn what has caused it and how to resolve it in this article.

ORA-00001 Cause

If you’ve tried to run an INSERT or UPDATE statement, you might have gotten this error:

ORA-00001 unique constraint violated

This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint.

There are a few solutions to the “ORA-00001 unique constraint violated” error:

1. Change your SQL so that the unique constraint is not violated.
2. Change the constraint to allow for duplicate values
3. Drop the constraint from the column.
4. Disable the unique constraint.

Solution 1: Modify your SQL

You can modify your SQL to ensure you’re not inserting a duplicate value.

If you’re using ID values for a primary key, it’s a good idea to use a sequence to generate these values. This way they are always unique.

You can use the sequence.nextval command to get the next value of the sequence.

So, instead of a query like this, which may not work if the employee_id value is already used:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (231, 'John', 'Smith');

You can use this:

INSERT INTO employee (employee_id, first_name, last_name)
VALUES (seq_emp_id.nextval, 'John', 'Smith');

Assuming the sequence is set up correctly, this should ensure that a unique value is used.

Find the constraint that was violated

The “ORA-00001 unique constraint violated” error usually shows a name of a constraint. This could be a descriptive name (if you’ve named your constraints when you create them) or a random-looking name for a constraint.

You can query the all_indexes view to find the name of the table and other information about the constraint:

SELECT *
FROM all_indexes
WHERE index_name = <constraint_name>;

This will give you more information about the specific fields and the table.

Solution 2: Change the constraint to allow for duplicates

If you have a unique constraint or primary key set up on your table, you could change the constraint to allow for duplicate values, to get around the ORA-00001 error.

Let’s say the unique constraint applies to first_name and last_name, which means the combination of those fields must be unique.

If you find that that rule is incorrect, you can change the constraint to say that the combination of first_name, last_name, and date_of_birth must be unique.

To do this, you need to drop and recreate the constraint.

To drop the constraint:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Then, recreate the constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2....);

Now your constraint will reflect your rules.

Solution 3: Remove the unique constraint

The third solution would be to drop the unique constraint altogether.

This should only be done if it is not required.

To do this, run the ALTER TABLE command:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

The constraint will be removed and you should be able to UPDATE or INSERT the data successfully.

Solution 4: Disable the unique constraint

The final solution could be useful if you’re doing a lot of data manipulation and you need to temporarily disable the constraint, with the aim of enabling it later.

Disabling the constraint will leave it in the data dictionary and on the table, with the same name, it just won’t be checked when data is inserted or updated.

To disable the constraint:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

If you need to enable the constraint in the future:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

So, that’s how you can resolve the “ORA-00001 unique constraint violated” error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

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.

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

ORA-00001 Error Message

Error messages in Oracle can seem like nagging roadblocks, but some are truly designed and signaled to aid in the efficiency of the database. While this may seem counterintuitive, if the program simply allowed the user to have free reign in making mistakes, Oracle would not be the dynamic and streamlined database software that it is known as. The ORA-00001 error message is indicative of this pattern of thinking.

The ORA-00001 message is triggered when a unique constraint has been violated. Essentially the user causes the error when trying to execute an INSERT or UPDATE statement that has generated a duplicate value in a restricted field. The error can commonly be found when a program attempts to insert a duplicate row in a table.

Before we progress to resolving the issue, it would be ideal to have some more information about the error. If you are unsure as to which constraint was violated to trigger the ORA-00001, there is an SQL statement that you can run to find out more about it. You can type the following command in:

SELECT DISTINCT table_name
FROM all_indexes
WHERE index_name = 'CONSTRAINT_NAME' ;

The constraint name can be found by looking at the error message itself. In parenthesis following the ORA-00001 notice, the constraint should be listed. This process will then return the name of the table that features the violated constraint.

Now that we know the constraint and table in question, we can move forward with fixing the problems itself. There are a few basic options. You can modify the SQL so that no duplicate values are created, thus no errors are triggered. If you do not wish to do this, you can also simply drop the table constraint altogether. This would only be recommended if the constraint is unnecessary for the foundation of your table.

Another option would be to modify the constraint so that it can allow duplicate values in your table. Depending on your version of Oracle, this can be done multiple ways. The first and universal method would be to manually adjust the constraint. However, if you’re using Oracle 11g or newer, you can use the ignore_row_on_dupkey_index hint. This feature will allow for insert SQL’s to enter as duplicates and be effectively ignored so that an ORA-00001 message will not be triggered.

By employing hints such as this, the ORA-00001 error can be sidestepped in many circumstances. A trigger method can also be a preventative approach to minimizing the frequency of ORA-00001 errors. These types of automatic increment columns can overwrite the value from an ID by inserting a value from the sequence in its place. On a more broad scale, remaining knowledgeable of the types of constraints that you are working with will help not only in preventing an error, but by also allowing you to respond to it quickly when it does occur. If you are unsure about some of the constraints on your tables or would like to know more about the most up-to-date versions of Oracle that include favorable hints the ignore_row_on_dupkey_index, it may be a good idea to speak with your licensed Oracle consultant for more information.