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:
- Change your SQL so that the unique constraint is not violated.
- Change the constraint to allow for duplicate values
- Drop the constraint from the column.
- 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!
|
2009 г. Особенности обработки ошибок сервера базы данных OracleК.п.н. Владимир Лихачёв, Калужский педагогический университет им К.Э.Циолковского. Введение
Как ни странно, ситуация с формированием сообщений об ошибках в программах довольно часто сильно отличается от обработки самих ошибок. При обработке ошибок обычно удается выработать общую стратегию, что позволяет локализовать их обработку в одной или нескольких функциях. Аналогичный подход для сообщений об ошибках может быть реализован на основе того, что в сообщении об ошибке сервер Oracle указывает тип ошибки и объект базы данных, который явился причиной её возникновения. Такими объектами обычно являются ограничения, как, например, первичные, уникальные и внешние ключи, уникальные индексы, ограничения “not null” и др. Из системных таблиц и представлений базы данных может быть получена подробная информация об этих ограничениях и определены значения, изменение которых и привело к возникновению ошибки. Но проблема заключается в том, что реализация такого механизма формирования сообщений об ошибках в реальных приложениях встречает целый ряд сложностей:
Совокупность этих факторов обычно приводит к тому, что формирование сообщений даже об однотипных ошибках реализуется индивидуально для каждой транзакции. В результате код для формирования сообщений об ошибках оказывается распределенным по всему приложению, что усложняет его сопровождение. Из-за необходимости написания кода практически для каждой возможной ошибки, часть ошибок, о которых известно разработчику, оказываются без соответствующих сообщений для пользователя. В результате достаточно информативные сообщения для конечного пользователя формируются только для некоторой части ошибок, в остальных же случаях ему остается довольствоваться в лучшем случае сообщениями от самого сервера базы данных. Информативность таких сообщений для обычного пользователя в большинстве случаев недостаточна для выявления причины возникшей проблемы и её устранения. Рассматриваемый в статье метод формирования информативных сообщений об ошибках для пользователя является довольно универсальным, может быть реализован как в клиентских приложениях, так и на стороне сервера Oracle. Он может использоваться в различных типах программ, как, например:
Описанные выше проблемы формирования сообщений могут быть решены, если сообщения об ошибках условно разделить на две группы:
Описываемый в статье метод формирования сообщений об ошибках БД может быть применён для многих серверов реляционных баз данных. Пример его использования для баз данных сервера Firebird рассматривается в статье [1]. 1. Универсальные сообщения об ошибках, вызванных ограничениями БД
Как уже говорилось выше, основная идея создания универсальных сообщений заключается в том, чтобы на основе данных из сообщения об ошибке от Oracle и о структуре базы данных сформировать достаточно информативное и понятное для конечного пользователя сообщение.
CREATE TABLE DEMO.GOODS (
CODE INTEGER NOT NULL ,
TITLE VARCHAR2(50 byte) NOT NULL ,
PRICE NUMBER(16, 2) NOT NULL ,
CONSTRAINT CK_PRICE CHECK (PRICE > 0),
CONSTRAINT PK_GOODS PRIMARY KEY(CODE));
COMMENT ON TABLE DEMO.GOODS is 'Товары';
COMMENT ON COLUMN DEMO.GOODS.CODE is 'Код товара';
COMMENT ON COLUMN DEMO.GOODS.TITLE is 'Название';
COMMENT ON COLUMN DEMO.GOODS.PRICE is 'Цена';
CREATE UNIQUE INDEX DEMO.IDX_GOODS_TITLE ON DEMO.GOODS (TITLE);
Скрипт 1.1. Создание таблицы “GOODS”. Сервер в этом случае сгенерирует ошибку, так как столбец “TITLE”, в котором хранится название товара, включено в уникальный индекс “DEMO.IDX_GOODS_TITLE”: ORA-00001: нарушено ограничение уникальности (DEMO.IDX_GOODS_TITLE) Вместо этого сообщения для пользователя может быть сформировано, например, одно из сообщений:
Хотя эти сообщения и различаются, но в них всех указывается информация об объекте, для которого нарушено ограничение уникальности – это поле “Название” таблицы “Товары”. Одна из проблем формирования такого типа сообщений, заключается в том, что пользовательские названия полей и таблиц, отличаются от имен таблиц и столбцов в базе данных. Чтобы пользователю было понятно сообщение об ошибке, в нем должны использоваться именно пользовательские названия. Для сопоставления имен таблиц и полей и их пользовательских названий может использоваться отдельная таблица или комментарии для таблиц и столбцов. Последний вариант можно считать более предпочтительным, так как это позволяет одновременно документировать базу данных. Именно поэтому в скрипте 1.1 в качестве комментариев для таблицы и её столбцов приведены их пользовательские названия. Если сравнить выше приведённые сообщения и комментарии для таблицы и столбцов, то можно заметить, что формирование первого сообщения является наиболее простым вариантом. Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная задача. Хочется обратить внимание, что в дальнейшем в статье приводится только один из возможных вариантов сообщения для каждого случая ошибки. На практике выбор стиля сообщения и его содержания может зависеть от целого ряда факторов и будет определяться разработчиком системы. Конечно, нельзя исключать ситуацию, когда для таблицы или столбца отсутствуют комментарии, которые должны быть указаны в сообщении. В этой ситуации в сообщении об ошибке возможно отображение непосредственно имени таблицы или столбца. Далее рассматривается формирование универсальных сообщений для наиболее часто встречающихся ошибок, обусловленных ограничениями БД. 2. Не указано значение поля, обязательного для заполнения (ограничение NOT NULL)Эта ошибка генерируется сервером в нескольких случаях:
Во всех этих случаях сервер генерирует ошибку: ORA-01400: невозможно <вставить/заменить> NULL в ("<Схема>"."<Таблица>"."<Столбец>")
Для получения описания таблицы и столбца из сообщения об ошибке, можно использовать запрос 2.1. select tc.comments astable_comment, cc.comments ascolumn_comment from all_tab_columns c, all_tab_comments tc, all_col_comments cc where c.owner = :owner and c.table_name = :table_name and c.column_name = :column_name and tc.owner = c.owner and tc.table_name = c.table_name and cc.owner = c.owner and cc.table_name = c.table_name and cc.column_name = c.column_name Запрос 2.1. Получение описания таблицы и столбца В качестве параметров запроса “owner”, ”table_name”, ”column_name” необходимо указать соответственно имя схемы, таблицы и столбца из сообщения об ошибке. Запрос возвращает комментарии для таблицы и столбца. Используя результаты этого запроса, может быть сформировано сообщение об ошибке, например, следующего содержания: Необходимо указать значение столбца “<Описание поля>” в таблице “<Описание таблицы>” при <добавлении новой/изменении> записи. 3. Нарушена уникальность значения поля или набора столбцовНеобходимость ввода уникального значения столбца может требоваться в основном в трех случаях:
Во всех трех случаях Oracle Database генерирует одну и ту же ошибку: ORA-00001: нарушено ограничение уникальности (<Схема>.<Ограничение>) В сообщении об ошибке указывается ограничение, которое вызвало ошибку. Для получения информации о столбцах, входящих в главный или уникальный ключи, select dcs.constraint_type, cc.table_name, tc.comments astable_comment, cc.column_name, ccom.comments as column_comment from all_cons_columns cc join all_tab_comments tc on (tc.owner = cc.owner and tc.table_name = cc.table_name) join all_col_comments ccom on (ccom.owner = cc.owner and ccom.table_name = cc.table_name and ccom.column_name = cc.column_name ) join all_constraints dcs on (dcs.constraint_name = cc.constraint_name) where cc.owner = :owner and cc.constraint_name = :key_name
select ic.table_name, tc.comments astable_comment, ic.column_name, ccom.comments ascolumn_comment from all_ind_columns ic join all_tab_comments tc on (tc.owner = ic.table_owner and tc.table_name = ic.table_name) join all_col_comments ccom on (ccom.owner = ic.table_owner and ccom.table_name = ic.table_name and ccom.column_name = ic.column_name ) where table_owner = :owner and index_name = :index_name
В качестве параметров запросам передаётся имя схемы (“owner“), имя ключа (“key_name“) или индекса (“index_name“). Запросы возвращают имена и комментарии для таблиц и столбцов, входящих в ограничение. Запрос 3.1 возвращает так же тип ограничения (“constraint_type”): “P” – главный ключ, “U” – уникальный ключ. Количество записей, возвращаемых запросами, соответствует количеству столбцов в ограничении уникальности. На основе полученной информации об ограничении уникальности для пользователя могут быть сформированы варианты 4. Ошибки, вызываемые ограничениями внешних ключейПри выполнении операций над табличными данными, связанными внешними ключами,
Для получения информации о столбцах главной и подчиненной таблиц, входящих во внешний ключ, можно использовать приведенный ниже запрос 4.1. select a.constraint_name, a.table_name, tc1.comments astable_comment, a2.column_name, cc1.comments ascolumn_comment, b.owner asr_owner, b.table_name asr_table_name, tc2.comments asr_table_comment, b2.column_name asr_column_name, cc2.comments asr_column_comment from all_constraints a, all_constraints b, all_cons_columns a2, all_cons_columns b2, all_tab_comments tc1, all_col_comments cc1, all_tab_comments tc2, all_col_comments cc2 where a.owner = :owner and a.constraint_type = 'R' and a.constraint_name = :foreign_key and b.constraint_type in ('P','U') and b.constraint_name = a.r_constraint_name and b.owner = a.r_owner and a2.constraint_name = a.constraint_name and a2.table_name = a.table_name and a2.owner = a.owner and b2.constraint_name = b.constraint_name and b2.table_name = b.table_name and b2.owner = b.owner and b2.position = a2.position and tc1.owner = a.owner and tc1.table_name = a.table_name and cc1.owner = a2.owner and cc1.table_name = a2.table_name and cc1.column_name = a2.column_name and tc2.owner = b.owner and tc2.table_name = b.table_name and cc2.owner = b2.owner and cc2.table_name = b2.table_name and cc2.column_name = b2.column_name
Запрос имеет два параметра: “owner” и “foreign_key” – схема и внешний ключ, о котором необходимо получить информацию. Он возвращает информацию о столбцах, входящих во внешний ключ: «table_name», «table_comment» — имя и описание подчиненной таблицы; «column_name», «column_comment» — имя и описание столбца подчиненной таблицы. Столбцы запроса с префиксом “r_” возвращают информацию о главной таблице. Количество записей возвращаемых запросом соответствует количеству столбцов, входящих во внешний ключ. На основе этой информации могут быть сформированы сообщения об ошибках внешних ключей для пользователя. 5. Специальные сообщения об ошибках, вызванных ограничениями БДНеобходимость использования специальных сообщений может возникнуть в случае, если универсальное сообщение об ошибке по каким-то причинам не может использоваться или не может быть сформировано. Примером последнего случая являются ограничения CHECK для таблиц. В условиях ограничений могут использоваться запросы и условия, анализ которых может оказаться довольно сложной задачей. Поэтому для этих ограничений часто удобнее использовать сообщения, которые определяются на этапе разработки. Можно выделить две группы специальных сообщений об ошибках. Первый тип специальных сообщений предназначен для использования во всех приложениях, которые работают c общей базой данных. Их можно условно назвать “специальные сообщения об ошибках уровня базы данных”. Вторая группа сообщений специфична для конкретного приложения. Они могут быть необходимы, когда различные приложения должны выдавать пользователю различные сообщения об одной и той же ошибке. Их можно условно назвать “специальные сообщения об ошибках уровня приложения”. Информацию о первой группе сообщений удобно хранить в самой базе данных и использовать для этого отдельную таблицу. Сообщения, специфичные для программы могут храниться в её ресурсах, например, в виде отдельного файла или также в БД. Идентификация специальных сообщений может выполняться на основе кода ошибки, имени схемы и одного или нескольких ключевых слов из сообщения об ошибке. 6. Сообщения об ошибках ограничений CHECK для таблицПри возникновении ошибки, вызванной ограничением CHECK для таблицы, сервер генерирует ошибку: ORA-02290: нарушено ограничение целостности CHECK (<Схема>.<Имя ограничения>)
Как уже говорилось выше, для таких ошибок часто удобно использовать специальные сообщения. Например, для ограничения «CK_PRICE» таблицы “GOODS” может использоваться специальное сообщение, хранимое в таблице специальных сообщений: Цена товара в справочнике “Товары” должна быть больше нуля. 7. Комплексное использование специальных и универсальных сообщений об ошибкахГибкий механизм формирования информативных сообщений об ошибках для пользователя реализуется в несколько этапов (рис. 1):
Возможны более сложные случаи, чем приведенный в этой статье. Например, если сообщение формируется в
В ряде случаев такие сообщения могут быть даже более информативными, чем сформированные на предыдущих этапах. CREATE OR REPLACE TRIGGER DEMO.TRIGGER_GOODS BEFORE
INSERT
OR UPDATE OF PRICE ON DEMO.GOODS FOR EACH ROW BEGIN
IF :NEW.PRICE <= 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Цена товара "' || :NEW.TITLE || '
" должна быть больше 0 руб (указана цена '|| :NEW.PRICE ||' руб)');
END IF;
END TRIGGER_GOODS;
В случае цены товара меньшей или равной нулю сервер сгенерирует ошибку, например: ORA-20001: Цена товара "Лейка" должна быть больше 0 руб (указана цена 0 руб) Клиентское приложение может сразу передать это сообщение пользователю
Другой причиной может быть появление ошибки, для которой формирование
Хочется обратить внимание, что даже если в приложении используются только специальные сообщения об ошибках, то использование общей функции для формирования сообщений позволит улучшить структуру программы. При необходимости формат специальных сообщений может иметь поддержку ссылок на справочную систему, рисунки и т.д. ЗаключениеЦелью данной статьи является показать основные идеи метода, который может использоваться для формирования информативных сообщений об ошибках базы данных Oracle для конечного пользователя. Литература
|
Вопрос:
Я пытаюсь вставить некоторые значения в таблицу через приложение и получить проблему ORA-00001: уникальное ограничение нарушено. Я вижу, что последовательности не синхронизированы с самым высоким идентификатором таблицы, но даже после исправления порядкового номера ошибка по-прежнему сохраняется. Как я могу отлаживать эту ошибку больше, делает ли журналы оракула больше ошибок? как я могу видеть журналы оракулов? Спасибо Priyank
update: мы используем плагин аудита аудита, а в классе домена для пользователя мы ловим событие сохранения и регистрируем запись в журнале аудита
Итак, в классе User мы делаем:
class User {
//some attributes, constraints, mappings
def onSave = {
Graaudit aInstance = new Graaudit();
aInstance.eventType= "GRA User Create"
aInstance.eventDescription = "GRA User Created"
aInstance.objectid = username
aInstance.objecttype = 'GRAUSER'
aInstance.user_id = RequestContextHolder.currentRequestAttributes().session.username
aInstance.withTransaction{
aInstance.save()
}
}
}
Если у нас нет вышеуказанного кода в событии onSave, пользователь будет создан успешно.
Я предполагаю, что он связан с транзакцией hibernate, которую мы используем на aInstance, который умирает или текущая транзакция умирает из-за этого сохранения.
Если мы не используем транзакцию, получаем исключение "org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here" Не знаю, как исправить эту проблему.. Спасибо
Ответ №1
Сообщение об ошибке будет содержать имя ограничения, которое было нарушено (может быть более одного единственного ограничения в таблице). Вы можете использовать это имя ограничения для идентификации столбцов (столбцов), в которых объявляется уникальное ограничение
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
Как только вы узнаете, какие столбцы (столбцы) затронуты, вы можете сравнить данные, которые вы пытаетесь использовать INSERT или UPDATE отношении данных, уже находящихся в таблице, чтобы определить, почему нарушается ограничение.
Ответ №2
Эта ошибка ORA возникает из-за нарушения уникального ограничения.
ORA-00001: уникальное ограничение (constraint_name) нарушено
Это вызвано попыткой выполнить инструкцию INSERT или UPDATE, которая создала дублирующее значение в поле, ограниченном уникальным индексом.
Вы можете решить это либо путем изменения ограничения, чтобы разрешить дубликаты, либо удалить уникальное ограничение, либо вы можете изменить свой SQL, чтобы избежать дублирования вставок.
Ответ №3
Сообщение об ошибке Oracle должно быть несколько дольше. Обычно это выглядит так:
ORA-00001: unique constraint (TABLE_UK1) violated
Имя в круглых скобках – это имя прохода. Он сообщает вам, какое ограничение было нарушено.
Ответ №4
Сообщение об ошибке выглядит следующим образом
Error message => ORA-00001: unique constraint (schema.unique_constraint_name) violated
ORA-00001 происходит, когда: “запрос пытается вставить” дублирующую “строку в таблицу”. Это накладывает уникальное ограничение на неудачу, следовательно, запрос не выполняется, и строка НЕ добавляется в таблицу. “
Решение:
Найти все столбцы, используемые в unique_constraint, например, столбец a, столбец b, столбец c, столбец d совместно создают unique_constraint, а затем найти запись из исходных данных, которая является дубликатом, используя следующие запросы:
-- to find <<owner of the table>> and <<name of the table>> for unique_constraint
select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';
Затем используйте запрос Justin Cave (вставленный ниже), чтобы найти все столбцы, используемые в unique_constraint:
SELECT column_name, position
FROM all_cons_columns
WHERE constraint_name = <<name of constraint from the error message>>
AND owner = <<owner of the table>>
AND table_name = <<name of the table>>
-- to find duplicates
select column a, column b, column c, column d
from table
group by column a, column b, column c, column d
having count (<any one column used in constraint > ) > 1;
Вы можете либо удалить эту дублирующую запись из ваших исходных данных (это был запрос на выборку в моем конкретном случае, как я испытал его с “Вставить в выборку”), либо изменить, чтобы сделать ее уникальной, или изменить ограничение.