Нарушено ограничение уникальности что это
Нарушено ограничение уникальности
выдает ora-00001 «нарушено ограничение уникальности (metrology.pk_attr)» При этом при попытке обратиться к таблице metrology.pk_attr выводится сообщение что таблицы не существует. Как разрешить проблему? Благодарен заранее.
1 ответ 1
Oracle в сообщениях об ошибках указывает имена объектов, которые эту ошибку обнаружили. Эти объекты чаще всего не таблицы. В сообщениях о нарушении уникальности указывается имя первичного ключа или уникального индекса обнаружившего дублирование информации.
В вашем случае pk_attr это (судя по названию) первичный ключ таблицы attr и скорее всего этот ключ сделан по полю id таблицы. Значит в таблице attr уже есть запись с и добавление еще одной такой же приводи к ошибке нарушения уникальности (ORA-00001)
Всё ещё ищете ответ? Посмотрите другие вопросы с метками sql plsql plsql-developer или задайте свой вопрос.
Похожие
Подписаться на ленту
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
дизайн сайта / логотип © 2021 Stack Exchange Inc; материалы пользователей предоставляются на условиях лицензии cc by-sa. rev 2021.12.22.41046
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Ограничения уникальности и проверочные ограничения
В этом разделе содержатся следующие подразделы.
Ограничения UNIQUE
Ограничения представляют собой правила, которые принудительно применяются в Компонент SQL Server Database Engine от имени пользователя. Например, ограничения UNIQUE можно использовать для обеспечения того, чтобы в указанные столбцы, не входящие в состав первичного ключа, не вводились повторяющиеся значения. Хотя уникальность значений ограничения UNIQUE и PRIMARY KEY гарантируют в равной степени, в случае, когда необходимо обеспечить уникальность в столбце или комбинации столбцов, которые не являются первичными ключевыми, вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE.
В отличие от PRIMARY KEY, ограничения UNIQUE допускают значение NULL. Однако, как и всякое другое значение столбца с ограничением UNIQUE, NULL может встречаться только один раз. На ограничение UNIQUE могут ссылаться ограничения FOREIGN KEY.
При добавлении ограничения UNIQUE на уже существующий столбец или группу столбцов в таблице, компонент Компонент Database Engine по умолчанию проверяет уникальность всех существующих значений в указанных столбцах. При попытке добавить ограничение UNIQUE к столбцу, содержащему повторяющиеся значения, компонент Компонент Database Engine возвращает ошибку, а ограничение не добавляется.
Компонент Компонент Database Engine автоматически создает индекс UNIQUE, что обеспечивает выполнение требований уникальности значений для ограничений UNIQUE. Поэтому, при попытке вставки в таблицу строки с повторяющимися данными, компонент Компонент Database Engine выдает сообщение об ошибке, в котором сообщается о нарушении ограничения UNIQUE, а строка в таблицу не вставляется. Для обеспечения выполнения ограничения UNIQUE по умолчанию создается уникальный некластеризованный индекс, если явно не указано создание кластеризованного индекса.
Ограничения CHECK
Проверочные ограничения подобны ограничениям внешнего ключа, так как они управляют значениями, которые присваиваются столбцу. Однако они по-разному определяют допустимые значения: ограничения внешнего ключа получают список допустимых значений из другой таблицы, а проверочные ограничения определяют допустимые значения по логическому выражению.
Ограничения, которые включают явное или неявное преобразование данных, могут вызывать ошибки в операциях такого рода. Например, ограничения, заданные для таблиц, которые являются исходными при переключении секций, могут приводить к ошибкам при использовании оператора ALTER TABLE. SWITCH. Следует избегать преобразования типов данных в определениях ограничений.
Ограничения проверочных ограничений
Проверочные ограничения отклоняют значения, вычисляемые в FALSE. Поскольку значения NULL вычисляются как UNKNOWN, то их наличие в выражениях может переопределить ограничение. Например, предположим, что на столбец MyColumn типа int установлено следующее ограничение: MyColumn может содержать только значение 10 (MyColumn=10). При вставке значения NULL в столбец MyColumn компонент Компонент Database Engine вставит значение NULL и не возвратит ошибку.
Проверочное ограничение возвращает TRUE, если для проверяемого условия в любой строке таблицы отсутствует значение FALSE. Проверочное ограничение работает на уровне строки. Если в только что созданной таблице отсутствуют строки, то любое проверочное ограничение на эту таблицу считается допустимым. В результате могут возвращаться неожиданные результаты, как в следующем примере.
Ограничение CHECK показывает, что в таблице CheckTbl должна быть хотя бы одна строка. Однако поскольку в таблице нет ни одной строки, над которой можно было бы произвести проверку ограничения, инструкция ALTER TABLE завершается успешно.
Инструкция DELETE выполняется успешно, даже если ограничение CHECK определяет, что в таблице CheckTbl должна быть хотя бы 1 строка.
Связанные задачи
Если таблица опубликована для репликации, то изменения схемы следует проводить при помощи инструкции языка Transact-SQL ALTER TABLE или объектов SMO. При изменении схемы с помощью конструктора таблиц или конструктора диаграмм баз данных конструктор пытается удалить и затем вновь создать таблицу. Но поскольку удалять опубликованные объекты нельзя, изменения схемы не будут применены.
issue ORA-00001: нарушено уникальное ограничение, входящее в INSERT/UPDATE
Я пытаюсь вставить некоторые значения в таблицу через приложение и получить проблему ORA-00001: уникальное ограничение нарушено. Я вижу, что последовательности не синхронизированы с самым высоким идентификатором таблицы, но даже после исправления порядкового номера ошибка по-прежнему сохраняется. Как я могу отлаживать эту ошибку больше, делает ли журналы оракула больше ошибок? как я могу видеть журналы оракулов? Спасибо Priyank
update: мы используем плагин аудита аудита, а в классе домена для пользователя мы ловим событие сохранения и регистрируем запись в журнале аудита
Итак, в классе User мы делаем:
Если у нас нет вышеуказанного кода в событии onSave, пользователь будет создан успешно.
Я предполагаю, что он связан с транзакцией hibernate, которую мы используем на aInstance, который умирает или текущая транзакция умирает из-за этого сохранения.
Если мы не используем транзакцию, получаем исключение «org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here» Не знаю, как исправить эту проблему.. Спасибо
Сообщение об ошибке будет содержать имя ограничения, которое было нарушено (может быть более одного единственного ограничения в таблице). Вы можете использовать это имя ограничения для идентификации столбцов (столбцов), в которых объявляется уникальное ограничение
Как только вы узнаете, какие столбцы (столбцы) затронуты, вы можете сравнить данные, которые вы пытаетесь использовать INSERT или UPDATE отношении данных, уже находящихся в таблице, чтобы определить, почему нарушается ограничение.
Эта ошибка ORA возникает из-за нарушения уникального ограничения.
ORA-00001: уникальное ограничение (constraint_name) нарушено
Это вызвано попыткой выполнить инструкцию INSERT или UPDATE, которая создала дублирующее значение в поле, ограниченном уникальным индексом.
Вы можете решить это либо путем изменения ограничения, чтобы разрешить дубликаты, либо удалить уникальное ограничение, либо вы можете изменить свой SQL, чтобы избежать дублирования вставок.
Сообщение об ошибке Oracle должно быть несколько дольше. Обычно это выглядит так:
Имя в круглых скобках – это имя прохода. Он сообщает вам, какое ограничение было нарушено.
Сообщение об ошибке выглядит следующим образом
ORA-00001 происходит, когда: “запрос пытается вставить” дублирующую “строку в таблицу”. Это накладывает уникальное ограничение на неудачу, следовательно, запрос не выполняется, и строка НЕ добавляется в таблицу. “
Решение:
Найти все столбцы, используемые в unique_constraint, например, столбец a, столбец b, столбец c, столбец d совместно создают unique_constraint, а затем найти запись из исходных данных, которая является дубликатом, используя следующие запросы:
Затем используйте запрос Justin Cave (вставленный ниже), чтобы найти все столбцы, используемые в unique_constraint:
Вы можете либо удалить эту дублирующую запись из ваших исходных данных (это был запрос на выборку в моем конкретном случае, как я испытал его с “Вставить в выборку”), либо изменить, чтобы сделать ее уникальной, или изменить ограничение.
Нарушено ограничение уникальности что это
Введение
Для программ, работающих с базами данных, важным является не только корректность обработки их ошибок, но и формирование информативных сообщений об этих ошибках. Наличие таких сообщений позволяет быстрее выявлять причины и исправлять ошибки. Особенно это актуально при работе с программой конечного пользователя, так как ему в большинстве случаев не известна не только структура конкретной БД, но и теоретические основы реляционных баз данных.
Совокупность этих факторов обычно приводит к тому, что формирование сообщений даже об однотипных ошибках реализуется индивидуально для каждой транзакции. В результате код для формирования сообщений об ошибках оказывается распределенным по всему приложению, что усложняет его сопровождение. Из-за необходимости написания кода практически для каждой возможной ошибки, часть ошибок, о которых известно разработчику, оказываются без соответствующих сообщений для пользователя. В результате достаточно информативные сообщения для конечного пользователя формируются только для некоторой части ошибок, в остальных же случаях ему остается довольствоваться в лучшем случае сообщениями от самого сервера базы данных. Информативность таких сообщений для обычного пользователя в большинстве случаев недостаточна для выявления причины возникшей проблемы и её устранения.
Описываемый в статье метод формирования сообщений об ошибках БД может быть применён для многих серверов реляционных баз данных. Пример его использования для баз данных сервера Firebird рассматривается в статье [1]. Если клиентское приложение разработано на Object Pascal (Delphi, Kylix, Free Pascal), то для выявления причин непредвиденных ошибок могут быть полезны возможности библиотеки JEDI [2].
1. Универсальные сообщения об ошибках, вызванных ограничениями БД
Как уже говорилось выше, основная идея создания универсальных сообщений заключается в том, чтобы на основе данных из сообщения об ошибке от Oracle и о структуре базы данных сформировать достаточно информативное и понятное для конечного пользователя сообщение. Предположим, в таблицу “GOODS” (скрипт 1.1) пользователь пытается добавить товар с названием (столбец “TITLE”), которое уже имеется в таблице. Скрипт 1.1. Создание таблицы “GOODS”.
Сервер в этом случае сгенерирует ошибку, так как столбец “TITLE”, в котором хранится название товара, включено в уникальный индекс “DEMO.IDX_GOODS_TITLE”:
Хотя эти сообщения и различаются, но в них всех указывается информация об объекте, для которого нарушено ограничение уникальности – это поле “Название” таблицы “Товары”.
Одна из проблем формирования такого типа сообщений, заключается в том, что пользовательские названия полей и таблиц, отличаются от имен таблиц и столбцов в базе данных. Чтобы пользователю было понятно сообщение об ошибке, в нем должны использоваться именно пользовательские названия. Для сопоставления имен таблиц и полей и их пользовательских названий может использоваться отдельная таблица или комментарии для таблиц и столбцов. Последний вариант можно считать более предпочтительным, так как это позволяет одновременно документировать базу данных. Именно поэтому в скрипте 1.1 в качестве комментариев для таблицы и её столбцов приведены их пользовательские названия. Если сравнить выше приведённые сообщения и комментарии для таблицы и столбцов, то можно заметить, что формирование первого сообщения является наиболее простым вариантом. Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная задача. Хочется обратить внимание, что в дальнейшем в статье приводится только один из возможных вариантов сообщения для каждого случая ошибки. На практике выбор стиля сообщения и его содержания может зависеть от целого ряда факторов и будет определяться разработчиком системы.
Конечно, нельзя исключать ситуацию, когда для таблицы или столбца отсутствуют комментарии, которые должны быть указаны в сообщении. В этой ситуации в сообщении об ошибке возможно отображение непосредственно имени таблицы или столбца.
Далее рассматривается формирование универсальных сообщений для наиболее часто встречающихся ошибок, обусловленных ограничениями БД.
2. Не указано значение поля, обязательного для заполнения (ограничение NOT NULL)
Во всех этих случаях сервер генерирует ошибку:
Для получения описания таблицы и столбца из сообщения об ошибке, можно использовать запрос 2.1. Запрос 2.1. Получение описания таблицы и столбца
В качестве параметров запроса “owner”, ”table_name”, ”column_name” необходимо указать соответственно имя схемы, таблицы и столбца из сообщения об ошибке. Запрос возвращает комментарии для таблицы и столбца.
Используя результаты этого запроса, может быть сформировано сообщение об ошибке, например, следующего содержания:
Необходимо указать значение столбца “ ” в таблице “ ” при записи.
3. Нарушена уникальность значения поля или набора столбцов
Во всех трех случаях Oracle Database генерирует одну и ту же ошибку:
В качестве параметров запросам передаётся имя схемы (“owner“), имя ключа (“key_name“) или индекса (“index_name“). Запросы возвращают имена и комментарии для таблиц и столбцов, входящих в ограничение. Запрос 3.1 возвращает так же тип ограничения (“constraint_type”): “P” – главный ключ, “U” – уникальный ключ. Количество записей, возвращаемых запросами, соответствует количеству столбцов в ограничении уникальности.
На основе полученной информации об ограничении уникальности для пользователя могут быть сформированы варианты сообщений об ошибке, например, приведенные в разделе 1.
4. Ошибки, вызываемые ограничениями внешних ключей
Для получения информации о столбцах главной и подчиненной таблиц, входящих во внешний ключ, можно использовать приведенный ниже запрос 4.1. Запрос 4.1. Получение информации о внешнем ключе.
На основе этой информации могут быть сформированы сообщения об ошибках внешних ключей для пользователя.
5. Специальные сообщения об ошибках, вызванных ограничениями БД
Необходимость использования специальных сообщений может возникнуть в случае, если универсальное сообщение об ошибке по каким-то причинам не может использоваться или не может быть сформировано. Примером последнего случая являются ограничения CHECK для таблиц. В условиях ограничений могут использоваться запросы и условия, анализ которых может оказаться довольно сложной задачей. Поэтому для этих ограничений часто удобнее использовать сообщения, которые определяются на этапе разработки.
Можно выделить две группы специальных сообщений об ошибках. Первый тип специальных сообщений предназначен для использования во всех приложениях, которые работают c общей базой данных. Их можно условно назвать “специальные сообщения об ошибках уровня базы данных”. Вторая группа сообщений специфична для конкретного приложения. Они могут быть необходимы, когда различные приложения должны выдавать пользователю различные сообщения об одной и той же ошибке. Их можно условно назвать “специальные сообщения об ошибках уровня приложения”. Информацию о первой группе сообщений удобно хранить в самой базе данных и использовать для этого отдельную таблицу. Сообщения, специфичные для программы могут храниться в её ресурсах, например, в виде отдельного файла или также в БД. Идентификация специальных сообщений может выполняться на основе кода ошибки, имени схемы и одного или нескольких ключевых слов из сообщения об ошибке.
6. Сообщения об ошибках ограничений CHECK для таблиц
Как уже говорилось выше, для таких ошибок часто удобно использовать специальные сообщения. Например, для ограничения «CK_PRICE» таблицы “GOODS” может использоваться специальное сообщение, хранимое в таблице специальных сообщений:
7. Комплексное использование специальных и универсальных сообщений об ошибках
Возможны более сложные случаи, чем приведенный в этой статье. Например, если сообщение формируется в хранимой процедуре, которая в свою очередь может вызываться из триггера или другой хранимой процедуры. В этом случае может потребоваться так же информация, о том как вызывалась процедура, формирующая сообщение об ошибке. И поэтому исходное сообщение может быть дополнено или изменено, например, на основе информации о стеке вызова хранимых процедур и триггеров.
В ряде случаев такие сообщения могут быть даже более информативными, чем сформированные на предыдущих этапах. Например, вместо ограничения CK_PRICE для таблицы DEMO.GOODS (скрипт 1.1) можно в триггере перед вставкой и обновлением записи выполнять необходимую проверку и генерировать сообщение для пользователя в уже “готовом” виде:
В случае цены товара меньшей или равной нулю сервер сгенерирует ошибку, например:
Клиентское приложение может сразу передать это сообщение пользователю без изменения.
Другой причиной может быть появление ошибки, для которой формирование сообщения не предусмотрено.
Рис. 1. Последовательность формирования сообщения об ошибке базы данных.
Хочется обратить внимание, что даже если в приложении используются только специальные сообщения об ошибках, то использование общей функции для формирования сообщений позволит улучшить структуру программы. При необходимости формат специальных сообщений может иметь поддержку ссылок на справочную систему, рисунки и т.д. Описываемый метод формирования сообщений об ошибках базы данных ориентирован в большей степени на реализацию в клиентском приложении. В то же время он может использоваться на стороне сервера в хранимых процедурах, триггерах таблиц, а так же в системных триггерах для события SERVERERROR базы данных или схемы.
Заключение
Целью данной статьи является показать основные идеи метода, который может использоваться для формирования информативных сообщений об ошибках базы данных Oracle для конечного пользователя. Хотя за рамками статьи остались некоторые моменты реализации, хочется надеяться, что описанный в статье подход позволит уменьшить трудозатраты при разработке программного обеспечения, повысить его надежность и качество.
Ограничения
Ограничения являются средством, с помощью которого БД может принуждать к выполнению бизнес-правил и гарантировать что данные соответсвуют модели сущность-связь определённой системным анализом определяя структуры данных приложения. Например, бизнес-аналитик в компании решил что каждый покупатель и каждый заказ должен определяться уникальным числом, что нельзя создать заказ до создания покупателя и каждый заказ должен иметь валидную дату и сумму большую нуля. Это может быть достигнуто путём создания первичных ключей для столбца CUSTOMER_ID таблицы CUSTOMERS и столбца ORDER_ID таблицы ORDERS, создания внешнего ключа для таблица ORDERS ссылающегося на таблицу CUSTOMERS, создания ограничения “not null” для столбца ORDER_DATE (тип данных DATE сам проверит удовлятворяет ли введённое значение типа дата или нет) и ограничения проверки для столбца ORDER_AMOUNT таблицы ORDERS.
Если какой либо DML запрос к таблице с ограничениями нарушит ограничение, тогда весь запрос будет отменем автоматически. Надо помнить что DML запрос может обновлять много строк и частично обновление было успешным до нарушения ограничения для конкретной строки. Если запрос это часть транзакции состоящей из нескольких запросов, то результат выполнения запросов которые уже выполнились успешно остаётся но не подтверждённым.
A constraint violation will force an automatic rollback of the entire statement that hit the problem, not just the single action within the statement, and not the entire transaction.
Типы ограничений
Ограничения поддерживаемые Orace это
У ограничений есть имя. Хорошей практикой считается именовать их согласно конвенции имен, но если явно не указывать имя, Oracle будет использовать сгенерированное имя.
Ограничение уникальности
Ограничение уникальности назначает столбец (или группу столбцов) для которых значение должно быть разным для каждой строки в таблице. Если ограничение настроено для одного столбца, это называется столбец-ключ (key column). Если ограничение состоит из нескольких столбцов (называется составной ключ (composite key)), столбцы не должны быть одинакового типы и располагаться последовательно в таблице.
Странностью уникального ограничения можно считать возможность добавления значения NULL в столбцы ключа; более того можно иметь неограниченное количество строк со значение NULL в столбцах ключа. Таким образом запрос строк по ключу ограничения гарантирует одну строку для значения – пока не используется NULL, в таком случае все строки со значением NULL будут результатом запроса.
It is possible to insert many rows with NULLs in a column with a unique constraint. This is not possible for a column with a primary key constraint.
Ограничения уникальности работают с помощью индексов. Когда мы определяем ограничение, Oracle проверит существует ли индекс для столбцов ограничения, и если индекс не сущетвует, он будет создан. Затем когда происходи попытка вставки новой строки, Oracle просматривает индекс на существование значения ключа; если значение сущесвтует – строка будет отклонена. Индекс ограничения – B* Tree индекс, и он не хранит значения NULL, вот почему ограничение не работает для NULL (т.е. можно добавлять много строк со значением NULL). Как мы обсуждали выше, целью индексов является не только обеспечение работоспособности механизма ограничений, но и повышение производительности запросов с секцией WHERE и объединением таблиц. Если в запросе используется WHERE key_column IS NULL – Oracle не может использовать индекс (потому что индекс не хранит значения NULL) и всегда будет использовано полное чтение таблицы.
Ограничения NOT-NULL
Ограничения NOT-NULL принуждают указать данные для столбцов ключа. Эти ограничения накладываются на столбец и иногда их называют обязательными для заполнения столбцами (mandatory columns). Если несколько столбцов должны иметь значения, то вы не можете создать одно ограничение для группы – необходимо создавать ограничения для каждого столбца.
Любая попытка записи строки без указания значения для обязательного столбца вернёт ошибку. Возможно обойти эту проверку указав директиву DEFAULT при создании таблицы.
Первичный ключ
Первичный ключ является средством для поиска любой одной строки в таблице. Реляционная парадигма утверждает что каждая таблица должна иметь первичный ключ: столбец (или группу столбцов) которые можно использовать для выделения каждой строки. Oracle позволяет создавать таблицы (как и некоторые другие РСУБД) без первичного ключа.
Реализацией первичного ключа является комбинация ограничений целостности и обязательности. Значение в столбцах ключе должно быть уникальным и не NULL. Так же как и для ограничения уникальности индекс должен существовать для столбцов ключа, иначе он будет создан. У таблицы может быть только один первичный ключ. Но у таблицы может быть сколько угодно ограничений уникальности и not-null ограничений. Если у вас есть несколько столбцов которые должны быть уникальны и обязательны вы можете использовать часть из них для создания первичного ключа, а для остальных создать ограничения уникальности и обязательности. Например таблица сотрудников: все сотрудники должны иметь уникальный номер (первичный ключ) и также значения email, номер соц. страха должны быть уникальны и обязательны для заполнения.
Unique and primary key constraints need an index. If one does not exist, one will be created automatically.
Внешние ключи
Внешний ключ определяет отношение родитель-наследник. Это ограничение назначает столбец (или столбцы) в дочерней таблице которые должны соответствовать столбцу(ам) первичного ключа родительской таблицы. Соответствующие столбцы необязательно должны иметь одинаковые названия, но они должны быть одного типа данных. Внешний ключ определяет отношения один-ко-многим в третьей нормальной форме.
Если у родительской таблицы есть ограничение уникальности вместе (или вместо) первичного ключа, эти столбцы тоже могут использоваться как основа для внешнего ключа, даже если они не обязательны (nullable).
A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
Как и ограничение уникальности внешний ключ допускает значение NULL в столбцах ключа. Вы можете вставить строки в дочернюю таблицу со значением NULL в столбцах внешнего ключа – даже если в родительской таблице нет строки со значением NULL. Это создаст строки призраки (orphaned rows) и может вызвать определённые недоразмения. Как правило все столбцы ограничения уникальности и все столбцы внешнего ключа лучше определять вместе с ограничениями not-null: так же это обычно требование бизнеса.
При попытки вставить строку в дочернюю таблицу со значением ключа которого нет в родительской таблице – произойдёт ошибка выполнения запроса. Также попытка удаления строки в родительской таблице вернёт ошибку если существуют строки в дочерней которые ссылаются на эту строке. Существует два способа обойти это ограничение. Во-первых, ограничения могут быть созданы с директивой ON DELETE CASCADE. Это значит что если мы удаляется строка из родительской таблицы – Oracle автоматически найдёт все строки ссылающиеся на эту строку в дочерней таблице и удалит их тоже. Менее кардинальный способ – использование директивы ON DELETE SET NULL. В этом случае если строка родительской таблицы удаляется – Oracle найдёт все соответствующие строки в дочерней таблице и установит значение ключа в NULL. Это значит что строки в дочерней таблице станут строками-призраками – но они будут существовать в таблице. Если столбцы внешнего ключа имеют ограничение not-null – то удаление из родительской таблицы вернёт ошибку. Нельзя ни удалить ни выполнить операцию TRUNCATE для родительской таблицы, даже если нет ни одной строки в дочерней таблице. Даже если использованы ON DELETE SET NULL или ON DELETE CASCADE.
Отдельным видом внешнего ключа является внешний ключ ссылающийся сам на себя (self-referencing). Такой ключ определяет отношение где родительский и дочернии строки находятся в одной таблице. Хорошим примером является таблица сотрудников, в которой есть столбец MANAGER_ID. И менеджер и сотрудник являются сотрудниками. Если первичный ключ EMPLOYEE_ID то для внешнего ключа используем EMPLOYEE_ID как родительский столбец и MANAGER_ID как дочерний. Если у сотрудника нет менеджера, то такая строка указывает сама на себя.
Ограничения проверки значения (Check constraints)
Ограничения проверки значения используются для проверки данных на удовлетворение простым правилам, к примеру введённое значение должно попадать в период значений. Правилом должно быть выражение результатом проверки которого будет либо ПРАВДА либо ЛОЖЬ. Правила могут использовать литералы, или другие столбцы той же строки и они могут использовать некоторые функции. Сколько угодно правил можно применить к одному ограничение проверки значения, но нельзя использовать подзапросы или такие функции как SYSDATE.
The not-null constraint is in fact implemented as a preconfigured check constraint.
Определение ограничений
Ограничение можно определить при создании таблицы или добавить позже. При создании таблицы, ограничения можно указать в строке определяющей столбец используемые в ограничении, либо в конце оператора создания таблицы. В конце оператора создания вы можете определять ограничения более гибко. В строке определения столбца вы не можете создать ограничение использующее другие столбцы таблицы. При определении ограничения требующего индекс в момент создания таблицы также создастся индекс.
Расмотрим два запроса создания таблицы
1 create table dept(
2 deptno number(2,0) constraint dept_deptno_pk primary key
3 constraint dept_deptno_ck check (deptno between 10 and 90),
4 dname varchar2(20) constraint dept_dname_nn not null);
5 create table emp(
6 empno number(4,0) constraint emp_empno_pk primary key,
7 ename varchar2(20) constraint emp_ename_nn not null,
8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),
11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
12 on delete set null,
13 email varchar2(30) constraint emp_email_uk unique,
14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
15 constraint emp_email_ck
16 check ((instr(email,’@’) > 0) and (instr(email,’.’) > 0)));
Эти примеры показывают некоторые возможности определения ограничения при создании таблицы. Остальные возможности это
Управление созданием индексов для внешних ключей и ограничений уникальности
Определение времени проверки ограничения: во время вставки (значение по умолчанию) или позже, в момент подтверждения транзакции
Включено ли ограничение (по умолчанию) или отключено
Возможно создать таблицу без ограничений а затем добавить ограничения командой ALTER TABLE. Результат будет одинаковый но определение таблицы будет разбито на несколько запросов вместо одного.
Состояния ограничений
В любой момент времени, каждое ограничение включено или выключена и проверяется или не проверяется. Любая комбинация этих двух состояний возможна
Идеальной ситуацией (и состоянием по умолчанию при определении ограничения) является ENABLE VALIDATE. Это гарантирует что данные корректны и некорректные данные не могут быть добавлены.
Статус DISABLE NOVALIDATE полезен когда загружается много данных в таблицу одним запросом. Данные могут содержать плохие значения, но вместо ошибки выполнения запроса этот статус позволит загрузить данные. Сразу после загрузки данных, переведите состояние в ENABLE NOVALIDATE чтобы предотвратить вставку других неправильных данных пока вы будете проверять уже добавленные данные на удовлетворение ограничениям. И затем опять установите правильное состояние ENABLE VALIDATE.
Примером такой задачи может быть скрипт, читающий данные из активных данных в архив. Преположим что есть ограничение NOT-NULL на столбце архивной таблицы, которого нет у столбца активной
alter table sales_archive modify constraint sa_nn1 disable novalidate;
insert into sales_archive select * from sales_current;
alter table sales_archive modify constraint sa_nn1 enable novalidate;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
alter table sales_archive modify constraint sa_nn1 enable validate;
Проверка ограничений
Ограничения могут проверятся на момент выполнения запроса (ограничение IMMEDIATE) или когда подтверждается вся транзакция (DEFERRED). По умолчанию все ограничения в режиме IMMEDIATE. Это может быть альтернативой использования состояний. Можно переписать скрипт из подпункта «Состояние ограничений» таким образом
set constraint sa_nn1 deferred;
insert into sales_archive select * from sales_current;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
set constraint sa_nn1 immediate;
Для поддержки отложенного (DEFERRED) ограничения оно должно быть создано используя синтаксис
alter table sales_archive add constraint sa_nn1
check (channel is not null) deferrable initially immediate;
То есть невозможно сделать ограничение отложенным если директива deferrable не была указана при создании. В нашем случае ограничение SA_NN1 по умолчанию проверяется на момент вставки строки (или изменения), но проверку можно отложить до подтверждения транзакции. Часто отложенные ограничения используются для внешних ключей. Если операции обновления и изменения затрагивают и дочернюю и родительскую таблицу, и если внешний ключ не отложенный то операция может выполниться с ошибкой в зависимости от порядка обработки строк.
Изменение состояния ограничения затрагивает все сессии. Изменение времени проверки ограничения (IMMEDIATE или DEFERRED) затрагивает текущую сессию, но начальное состояние применяется ко всем сессиям.
By default, constraints are enabled and validated, and they are not deferrable.