Как отключить constraint oracle

Обновлено: 07.07.2024

отключение и включение ограничений внешнего ключа поддерживаются в SQL Server? Или это мой единственный вариант drop а то re - create ограничения?

Если вы хотите, чтобы отключить все ограничения в базе данных, просто запустите этот код:

чтобы снова включить их, запустите: (печать, конечно, необязательна, и она просто перечисляет таблицы)

Я нахожу его полезным при заполнении данных из одной базы данных в другую. Это гораздо лучший подход, чем снятие ограничений. Как вы упомянули, это удобно при удалении всех данных в базе данных и ее повторном заполнении (скажем, в тестовой среде).

Если вы удаляете все данные вы можете найти данное решение чтобы быть полезным.

также иногда удобно отключить все триггеры, а также, вы можете увидеть полное решение здесь.

ваш лучший вариант-удалить и создать ограничения внешнего ключа.

Я не нашел примеров в этом посте, которые будут работать для меня "как есть", один не будет работать, если внешние ключи ссылаются на разные схемы, другой не будет работать, если внешний ключ ссылается на несколько столбцов. Этот скрипт учитывает как несколько схем, так и несколько столбцов на внешний ключ.

вот скрипт, который генерирует операторы "добавить ограничение", для нескольких столбцов он будет их разделять запятая (обязательно сохраните этот вывод перед выполнением инструкций DROP):

вот скрипт, который генерирует операторы "Drop CONSTRAINT":

чтобы отключить ограничение, вы должны изменить таблицу с помощью последний

обратите внимание на двойной ПРОВЕРЬТЕ при включении.
все средства для всех ограничений в таблице.

после завершения, если вам нужно проверить состояние, используйте этот скрипт для списка состояния ограничения. Будет очень helpfull:

стандарт SQL-92 позволяет объявлять константу как отложенную, чтобы ее можно было отложить (неявно или явно) в рамках транзакции. К сожалению, SQL Server по-прежнему не хватает этой функции SQL-92.

для меня изменение ограничения на NOCHECK сродни изменению структуры базы данных "на лету" - безусловно, удаление ограничений-и что-то, чего следует избегать (например, пользователям требуются повышенные привилегии).

для OP решение Кристофа будет работать, если нет проблем с массивными данными и проблемами журнала транзакций на больших удалениях. Кроме того, даже с запасным хранилищем tlog, так как удаляет запись в tlog, операция может занять очень много времени для таблиц с сотнями миллионов строк.

Я часто использую ряд курсоров для усечения и перезагрузки больших копий одной из наших огромных производственных баз данных. Инженерное решение учетные записи для нескольких схем, нескольких столбцов внешнего ключа и лучше всего могут быть спроецированы для использования в службах SSIS.

Он включает в себя создание трех промежуточных таблиц (реальных таблиц) для размещения сценариев DROP, CREATE и CHECK FK, создание и вставку этих сценариев в таблицы, а затем цикл над таблицами и их выполнение. Прилагаемый сценарий состоит из четырех частей: 1.) создание и хранение скриптов в трех промежуточных (реальных) таблицах, 2.) выполнение сценариев drop FK через курсор по-одному, 3.) Использование sp_MSforeachtable для усечения всех таблиц в базе данных, кроме наших трех промежуточных таблиц и 4.) выполнение сценариев create FK и check FK в конце вашего пакета ETL SSIS.

запустите часть создания сценария в задаче Execute SQL в службах SSIS. Запустите часть "выполнение сценариев FK Drop" во второй задаче "выполнение SQL". Поместите сценарий усечения в третью задачу Execute SQL, а затем выполните любые другие процессы ETL, которые вам нужно сделать до чтобы прикрепить скрипты CREATE и CHECK в конечной задаче Execute SQL (или два, если необходимо) в конце потока управления.

Не повторяйте сценарий снова, если он не выполняется не убедившись, что вы повторно применяете все внешние ключи / проверки до этого, или вы, скорее всего, потеряете некоторое создание и проверьте сценарии fk, поскольку наши промежуточные таблицы удаляются и воссоздаются до создания сценариев для выполнения.

WITH CHECK CHECK почти наверняка требуется!

этот момент был поднят в некоторых ответах и комментариях, но я чувствую, что это достаточно важно, чтобы вызвать его снова.

повторное включение ограничения с помощью следующей команды (no WITH CHECK ) будет иметь некоторые серьезных недостатков.

С ПРОВЕРКОЙ / С NOCHECK

указывает, проверяются ли данные в таблице или нет против недавно добавленное или повторно включенное ограничение внешнего ключа или проверки. Если нет указано, с проверкой предполагается для новых ограничений, а с NOCHECK предполагается для повторно включенных ограничений.

если вы не хотите проверять новые ограничения проверки или внешнего ключа против существующих данных используйте с NOCHECK. Мы не рекомендуем делать это, за исключением редких случаев. Новое ограничение будет оценено в все последующие обновления данных. Любые нарушения ограничений, которые подавляются от С NOCHECK при добавлении ограничения может привести к будущим обновлениям сбой, если они обновляют строки с данными, которые не соответствуют ограничение.

оптимизатор запросов не учитывает определенные ограничения С ПАРАМЕТР NOCHECK. Такие ограничения игнорируются до их повторного включения используя ALTER TABLE table с ограничением CHECK CHECK ALL.

Примечание: WITH NOCHECK является значением по умолчанию для повторного включения ограничений. Мне пришлось интересно, почему.

В общем дело такое.

Мне нужно удалить из некоторого набора таблиц все записи. Но, таблицы очень огромные, удаление делается очень долго. УМные люди посоветовали отключить ограничения перед удалением, мол должно пойти быстрее. Вот теперь помедленнее. Хотелось бы пошагово и желательно с примерами об этом. Я пока тока начинаю в оракле и хотелось бы все понять.

Т.е. фактически значит мне нужно отключить все внешние ключи, так? хорошо, вот нашала такой пост тут:
"Но это не дает сделать ограничения целостности и связи."

Связь - это имеется ввиду FOREIGN KEY? Тогда это тоже ограничение целостности.

А почему бы раз навсегда не написать скрипт, типа:
1) alter table table1 disable primary key cascade
2) alter table table1 disable constraint <имя> cascade -- столько команд сколько ограничений
3) truncate table table1
4) insert into table1 select * from table2
5) alter table table1 enable primary key
6) alter table table1 enable constraint <имя> -- столько команд сколько ограничений
7) Поднять все внешние ключи других таблиц выключенные в п.п. 1,2.

как реализовать пункт 7.

А еще бывает таблица блокируется, и оракл пишщет ошибку, че делать в этом случае?

2) alter table table1 disable constraint <имя> cascade -- столько команд сколько ограничений

Теперь можно делать усечение всех ненужных таблиц.

После этого также одним скриптом включаем все ограничения

Извиняюсь, и впрямь не права, чет фигню пишу тут, чет с утречка это пока :((

ORA-00942: таблица или представление пользователя не существует, во какая ошибка, это значит что в CONSTRAINT_NAME существует какая то таблица, которой нет в базе? или я не так поняла? ошибка же в этом?

where r_constraint_name is not null

ORA-00942: таблица или представление пользователя не существует
ORA-06512: на line 17

Как я могу отключить все ограничения таблицы в Oracle с помощью одной команды? Это может быть как для одной таблицы, списка таблиц, так и для всех таблиц.

лучше избегать записи временных файлов катушки. Используйте блок PL / SQL. Вы можете запустить это из SQL*Plus или поместить эту вещь в пакет или процедуру. Соединение с USER_TABLES существует, чтобы избежать ограничений просмотра.

маловероятно, что вы действительно хотите отключить все ограничения (включая NOT NULL, первичные ключи и т. д.). Вы должны подумать о том, чтобы поместить constraint_type в предложение WHERE.

включение ограничений снова немного сложнее-вы необходимо включить ограничения первичного ключа, прежде чем вы сможете ссылаться на них в ограничении внешнего ключа. Это можно сделать с помощью ORDER by on constraint_type. 'P' = первичный ключ,' R ' = внешний ключ.

чтобы подсчитать зависимости между ограничениями:

фильтр на constraint_type для удаления только определенных типов ограничений

использовать следующий курсор, чтобы отключить все ограничения.. И изменить запрос для включения ограничений.

Это может быть написано в PL/SQL довольно просто на основе системного представления DBA/ALL / USER_CONSTRAINTS, но различные детали не так тривиальны, как кажется. Вы должны быть осторожны с порядком, в котором это делается, и вы также должны учитывать наличие уникальных индексов.

порядок важен, потому что вы не можете удалить уникальный или первичный ключ, на который ссылается внешний ключ, и могут быть внешние ключи в таблицах в других схемах, которые ссылаются на первичный ключ ключи в ваших собственных, поэтому, если у вас нет ALTER any table privilege, вы не можете удалить эти PKs и UKs. Также вы не можете переключить уникальный индекс на не уникальный индекс, поэтому вам нужно отбросить его, чтобы удалить ограничение (по этой причине почти всегда лучше реализовать уникальные ограничения как "реальное" ограничение, которое поддерживается не уникальным индексом).

не похоже, что вы можете сделать это с помощью одной команды, но здесьэто самое близкое к нему, что я мог найти.

Ограничения целостности в реляционных базах данных (и Oracle Database в частности) позволяют легко и автоматически применять важные бизнес-правила к таблицам базы данных. Например, в таблице, используемой в системе управления кадрами, нельзя завести сотрудника, не назначив ему руководителя. При создании связанных таблиц можно объявить необходимые ограничения целостности, которые должны быть удовлетворены при каждом вводе или модификации данных в таблице.

Для выполнения бизнес-правил можно также задействовать логику приложения,однако ограничения целостности обычно применять проще, и обычно они хорошо выполняют свою работу, гарантируя, что вставки, обновления и удаления данных таблиц будут соответствовать определенным правилам. С другой стороны, логика приложения обладает тем преимуществом, что позволяет отклонить или подтвердить данные без обращения к содержимому таблицы. Таким образом, выбирать метод выполнения бизнес-правил — логикой приложения или ограничениями целостности — необходимо на основе потребностей конкретного приложения. В любом случае ограничения целостности настолько фундаментальны для операций в реляционных базах данных, что вы обречены на их использование в конкретной базе.

По умолчанию Oracle разрешает значения null во всех столбцах. Если эти значения недопустимы для некоторых столбцов таблицы, для них необходимо задавать ограничение NOT NULL. Обратите внимание, что налагать ограничения базы данных на таблицы можно как во время их создания, так и позднее, с помощью команды ALTER TABLE.Однако очевидно, что если в таблице уже имеются значения null или дублированные данные, то изменить таблицу, применив ограничение NOT NULL или ограничение уникальности, будет невозможно.

Для таблицы Oracle можно установить несколько типов ограничений. Упрощенно их можно разделить на пять основных типов:

Все перечисленные типы ограничений обсуждаются далее в этой статье. В дополнение будет также представлен краткий обзор состояний ограничений целостности.

Ограничения первичного ключа

Первичный ключ — очень важный тип ограничения в таблице. Когда необходимо,чтобы значения столбца идентифицировались уникальным образом, это обеспечивается созданием первичного ключа по значению столбца. Столбец, на котором определен первичный ключ, должен быть уникальным и не null.

Таблица может иметь только один первичный ключ, который можно создать при создании таблицы, как показано в следующем примере:

Кроме того, можно добавить ограничение и к существующей таблице:

Поскольку в предыдущем примере ограничению не присвоено имя, Oracle даст ему имя, сгенерированное системой. Если требуется присвоить ограничению собственное имя, воспользуйтесь следующей командой, которая назовет ограничение dept_pk:

Обратите внимание, что если первичный ключ будет построен по более чем одному столбцу (т.е. будет составным), специфицировать назначение первичного ключа напротив имени столбца при создании таблицы нельзя. Столбцы первичного ключа должны быть указаны как отдельный элемент оператора CREATE TABLE после списка всех столбцов.

На заметку! В обоих предыдущих примерах Oracle автоматически создает индексы на столбце,который назначен первичным ключом.

Ограничения NOT NULL

Обычно таблица имеет один или более столбцов, в которых не допускается значение null — т.е. без значений. Хорошим примером является столбец last_name таблицы employee. Заставить пользователей обязательно вносить значения в этот столбец можно при создании таблицы, указав опцию NOT NULL для столбца, который не должен быть null:

Если таблица уже создана, и требуется модифицировать столбец с допускающего null на не допускающий null, для этого можно использовать следующий оператор:

Проверочные ограничения

используете Проверочные (CHECK) ограничения применяются для обеспечения соответствия данных столбца определенным параметрам, которые вы укажете. Например,предположим, что годовая зарплата сотрудника фирмы не может быть равна или превышать $100 000 при определенных обстоятельствах. Это ограничение можно навязать с помощью следующего оператора, который устанавливает ограничение CHECK на столбце SALARY:

Ограничение уникальности

Ограничение уникальности (UNIQUE) очень распространено в реляционных базах данных. Эти ограничения гарантируют уникальность строк в реляционной таблице.В таблице могут существовать сразу несколько таких ограничений. Например, ограничение уникальности на столбце employee_id гарантирует, что ни один сотрудник не появится дважды в таблице employee.

В следующем примере первый оператор специфицирует ограничение уникальности на комбинации столбцов dept_name и location:

Создать уникальное ограничение на таблице department можно также с помощью такого оператора ALTER TABLE:

Ограничения ссылочной целостности

Ограничения ссылочной целостности гарантируют, что значения определенных важных столбцов будут иметь смысл. Предположим, что есть родительская таблица, которая ссылается на значения из другой таблицы, как в случае таблиц dept и employee.Сотрудник в таблице employee не может быть назначен в департамент, если такой департамент в таблице dept не существует.

Гарантировать существование действительного департамента можно с помощью ограничения ссылочной целостности. В этом случае столбец departament_id служит первичным ключом таблицы dept, а столбец dept_id в таблице employee, ссылающийся на соответствующий столбец таблицы department, называется внешним ключом. Таблица,содержащая внешний ключ, обычно называется дочерней таблицей, а таблица, содержащая ключ, на который ссылается внешний ключ — родительской таблицей. Как и со всеми прочими типами ограничений, ссылочные ограничения целостности можно создавать во время создания таблицы или позднее, с помощью оператора ALTER TABLE:

База данных назначает столбец dept_id таблицы employee внешним ключом, потому что он ссылается на столбец dept_id таблицы dept. Обратите внимание, что для того, чтобы столбец служил ссылочным (на который ссылаются), он должен быть уникальным или же быть первичным ключом в таблице, на которую установлена ссылка.

Состояния ограничений целостности

Как было показано в предыдущем выше, ограничения целостности определяются на таблицах для гарантии, что данные, нарушающие заранее установленные правила,в таблицу не попадут. Однако иногда, например, при загрузке данных, ограничения целостности не должны поддерживаться в действительном состоянии, поскольку это приведет к определенным проблемам. При необходимости Oracle позволяет отключать ограничения и затем включать их, когда они снова понадобятся. Давайте рассмотрим некоторые способы изменения состояния ограничений таблиц.

При загрузке больших объемов данных с использованием SQL*Loader или утилиты Import может потребоваться значительное время на загрузку данных, каждую строку которых нужно проверять на предмет нарушения целостности. Более эффективная стратегия предусматривает отключение ограничения, загрузку данных и последующую проверку корректности загруженных данных. После завершения загрузки ограничения вновь вводятся в действие посредством включения. Когда ограничение отключается,как описано здесь, база данных уничтожает индекс. Подобным образом реализуется лучшая стратегия — предварительное создание неуникальных индексов для ограничений, которые база данных не должна уничтожать, поскольку они обрабатывают дублированные записи.

На заметку! Состояние enabled (включено) — это состояние ограничений Oracle по умолчанию

Отключаются ограничения двумя способами: указание в качестве состояния ограничения либо disable validate (отключить с проверкой), либо disable no validate (отключить без проверки) с использованием конструкции DISABLE VALIDATE и DISABLE NO VALIDATE, соответственно. Аналогично, для включения ограничения применяются конструкции ENABLE VALIDATE и ENABLE NO VALIDATE. Ниже кратко обсуждаются различные способы включения и отключения ограничений.

Состояние Disable Validate

В случае использования команды DISABLE VALIDATE выполняются следующие два действия сразу. Во-первых, конструкция VALIDATE гарантирует, что все данные в таблице удовлетворяют условию ограничения. Во-вторых, конструкция DISABLE избавляет от необходимости поддерживать ограничение. Oracle сбрасывает индекс ограничения,но сохраняет его действительным. Вот пример:

После выдачи приведенного выше оператора SQL наличие только уникальных комбинаций уникальных ключей prod_id и customer_id в таблице гарантируется, однако уникальный индекс не поддерживается.

Обратите внимание, что поскольку было решено сохранить ограничение в отключенном состоянии, никаких DML-действий в отношении таблицы выполнять нельзя. Эта опция идеально подходит для крупных таблиц хранилищ данных, которые обычно используются только для извлечения данных по запросам.

Состояние Disable No Validate

При отключении ограничения без проверки ограничение отключается, и нет никаких гарантий соответствия данных требованиям ограничения, поскольку Oracle не предпринимает проверку его действительности. Это по существу то же самое, что команда DISABLE CONSTRAINT.

Состояние Enable Validate

Это состояние ограничения включает ограничение, гарантируя проверку всех данных на соответствие условию ограничения. Это состояние в точности то же, что обычное состояние “включено”. Следующий пример демонстрирует использование этого состояния:

Состояние Enable No Validate

При этом состоянии ограничения база данных проверяет все новые вставки и обновления на соответствие условию ограничения. Поскольку существующие данные на такое соответствие не проверяются, нет уверенности, что они удовлетворяют этому условию ограничения. Обычно эта опция применяется при загрузке крупных таблиц,когда есть основания для уверенности, что эти таблицы уже удовлетворяют условию ограничения. Вот пример:

Более подходящая стратегия заключалась бы в использовании состояния DISABLE NOVALIDATE при загрузке данных, с переходом к состоянию ENABLE NOVALIDATE и затем к ENABLE VALIDATE в конце цикла извлечения, трансформации и загрузки (extraction,transformation, loading — ETL).

Ограничения Rely

Обычно перед загрузкой в таблицы хранилища данных предполагается выполнение шагов ETL. Если есть уверенность в качестве данных, можно сэкономить время на загрузку, отключив проверку ограничений. Для этого применяется команда ALTER TABLE с конструкцией RELY DISABLE NOVALIDATE, как показано в следующем примере:

Отложенные и немедленные ограничения

В дополнение к указанию типа проверки достоверности ограничения можно специфицировать, когда именно должно проверяться ограничение в процессе загрузки данных.

Если хотите, чтобы ограничение проверялось немедленно после модификации данных, выберите опцию not defferable (не откладывая), которая, фактически задает поведение по умолчанию в базах данных Oracle. Если необходимо выполнить едино-временную проверку ограничения после фиксации всей транзакции, выберите опцию deferrable. Все ограничения и внешние ключи могут быть объявлены как deferrable или not deferrable.

В случае выбора опции deferrable появляются еще две дополнительных опции.Ограничение deferrable можно специфицировать либо как initially deferred, либо как initially immediate (изначально отложенное или изначально немедленное). В первом случае база данных откладывает проверки ограничения до окончания транзакции. Если же была выбрана опция initially immediate, то база данных проверит ограничения перед изменением любых данных. Обратите внимание, что если предварительно создается индекс, то он должен быть не уникальным, чтобы обработать дублированные значения.

Следующий пример показывает, как специфицировать такого рода ограничения в таблице employee:

В Oracle также предусмотрен способ изменения отложенного ограничения с immediate на deferred или наоборот с помощью следующих операторов:

Представления, относящиеся к ограничениям и индексам

DBA_CONSTRAINTS

Представление DBA_CONSTRAINTS предоставляет информацию обо всех типах ограничений таблицы в базе данных. Опрос этого представления делается, когда необходимо узнать, какого типа ограничения имеет таблица. Представление перечисляет несколько типов ограничений, как показано в следующем запросе:

Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы.

Обратите внимание, что если необходимо увидеть определенное ссылочное ограничение и правило удаления, то нужно применить следующую вариацию предыдущего запроса:

DBA_CONS_COLUMNS

Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:

Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.

Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.

Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.

Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.

Рассмотрим на примере подробности реализации. Набор данных.

Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью 'CLERK' в одном городе не более 2х.

В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:

— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.

Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.

Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.

На все таблицы содержащие информацию для правила контроля вешаем триггера которыми будем вставлять в emp_chk идентификаторы модифицированных сотрудников. Некоторые комментарии к триггерам. Заказчик боевого применения функционала контроля потребовал совместимость с ORACLE-9, поэтому триггера не составные (compound).

Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.

Очистку таблицы emp_chk в начале новой транзакции осуществляют следующие триггера. Управляет очисткой пакетная переменная var_chk.first_dml_in_commit:

Создаем materialized view.

Триггер инициализирующий var_chk.first_dml_in_commit обеспечивает очистку EMP_CHK в начале транзакции.

Собственно триггер запускающий бизнес контроль.

Некоторые комментарии к тексту CHK_EMP_MV_AS. Отладка и контроль функционирования констрэйнта можно облегчить логированием. Учтем, что в случае возникновения ошибки набор данных предъявленный для commit-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для commit-a независимо от результата обработки. Снимки наборов данных помещаются в поле emp_chk_log.XML. Лог пишется пакетной функцией var_chk.write_log в таблицу:

Все бизнес правила реализованы в отдельной функции emp_logic. Функция не является членом пакета. Это позволяет разделить в разработке и сопровождении бизнес правила констрйнта и слой системных механизмов его функционирования. Ниже текст пакета var_chk.

Функция контроля бизнес правил.

Проверим велосипед на ходу.

Реальная реализация этого решения работает на трех десятках серверов центрального офиса и филиалов с весны 2015 года.

Читайте также: