Nullable oracle что это

Обновлено: 07.07.2024

Nullable value type (тип значения, допускающий null) – это тип, который позволяет представить не только все значения своего нижележащего типа, но и значение null.

Общие сведения

Для чего нужны nullable value types? Например, переменная типа int может иметь значения в диапазоне от -2 147 483 648 до 2 147 483 647. Однако в некоторых случаях бывает необходимо указать, что значение переменной не определено или отсутствует (например, значение столбца в строке базы данных). Для подобных случаев и были созданы nullable value types. Эти типы представляют собой экземпляры структуры System.Nullable<T>.

Определить переменную типа int, допускающую значение null, можно следующим образом:

Однако чаще используют сокращённый вариант записи:

Как значения нижележащего типа, так и значение null записываются в переменную через простое присвоение:

Свойства

Свойство HasValue позволяет выяснить, содержит ли nullable value type переменная значение нижележащего типа:

Кроме вызова свойства HasValue, проверить на наличие значение можно через сравнение с null. Следующие проверки эквивалентны, для них генерируется одинаковый IL код:

Свойство Value возвращает значение нижележащего типа, если оно имеется (Nullable<T>.HasValue - true). В противном случае будет выброшено исключение типа InvalidOperationException:

Методы

Метод T GetValueOrDefault() аналогичен свойству Value с тем отличием, что он не генерирует исключение, а возвращает default значение типа T, если отсутствует значение нижележащего типа:

Метод T GetValueOrDefault(T defaultValue) аналогичен свойству Value. Разница лишь в том, что он не генерирует исключение, а возвращает значение аргумента defaultValue, если отсутствует значение нижележащего типа:

Операторы преобразования

Для Nullable<T> определены оператор неявного преобразования из T в Nullable<T> и явного преобразования из Nullable<T> в T.

Присвоение значений T в переменные Nullable<T> возможно напрямую:

Для того чтобы записать значение из Nullable<T> в переменную T, потребуется выполнить явное приведение. Если в Nullable<T> будет отсутствовать нижележащее значение (Nullable<T>.HasValue - false), при выполнении явного приведения будет сгенерировано исключение типа InvalidOperationException.

Особенности использования nullable value types

Nullable<T> не может иметь значения null

Этот факт может сбить с толку с учётом того, что мы обсуждали выше и что следующий код успешно компилируется:

Однако следует помнить, что Nullable<int> — значимый тип. Следовательно, null здесь всего лишь синтаксический сахар. В данном случае переменная nullableInt будет проинициализирована значением default(Nullable<int>).

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

Более очевидным это становится, если посмотреть IL код, где для инициализации всех переменных явно используется одно и то же значение:

Упаковка и распаковка

Упаковка значений Nullable<T> обладает рядом особенностей:

если Nullable<T>.HasValue — true, при упаковке пакуется не сам экземпляр Nullable<T>, а значение нижележащего типа — Nullable<T>.Value;
если Nullable<T>.HasValue — false, результатом упаковки будет значение null;
если распаковывается значение null, результатом будет значение default(Nullable<T>).

Операторы, определённые для T

Если для T определены унарные или бинарные операторы (например, '+', '-'), то для Nullable<T> действует следующее правило:

если значение хотя бы одного операнда — null, результат — null;
если значение обоих операндов не null, результирующее значение — результат работы оператора для нижележащих значений операндов (Nullable<T>.Value).

Для операторов сравнения больше/меньше ('<', '<=', '>', '>='):

Ограничения целостности в реляционных базах данных (и 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 показывает имя столбца и позицию в таблице,где определено ограничение:

a bool переменная может содержать true или false, в то время как bool? также может быть null.

зачем нам третье значение для bool ? Если это не так true, что бы это ни было, это == false

можете ли вы предложить сценарий, в котором я бы хотел bool? вместо.

что-то может быть истинным, ложным или неопределенным по многим причинам. Как бы вы ответили: "ваш третий ребенок-девочка?"если только двое детей? И истина, и ложь неверны. Null было бы уместно сказать, что сравнение не применяется.

предположим, вы отслеживаете список или таблицу Shipments . А Shipment есть DeliveryDate , но, конечно, вы не знаете эту информацию до тех пор, после отгрузки было сделано, и, вероятно, по крайней мере несколько дней после отгрузка была фактически доставлена, когда UPS, наконец, добирается до уведомления вас. Так что, конечно, DeliveryDate это Nullable<DateTime> (или DateTime? ).

вы хотите получить список всех грузов, которые были доставлены в течение последней недели. Итак, вы пишете:

должны поставки с null дата доставки будет включена? (Ответ, конечно, нет.)

хорошо, так что об этом:

должны пересылки с а null дата доставки быть включены в эти результаты? Ответ еще нет.

Итак, теперь у вас есть любопытная ситуация. Вы можете подумать, что между этими двумя запросами вы получите все поставки в системе. A | !A всегда true , да? Не тогда, когда имеешь дело с нулевыми.

даже этот не может получить все результаты:

так как же это возможно?

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

Аха. теперь у нас null сроки поставки! Это неправильно! И прежде чем кто-нибудь скажет "@Aaronaught, о чем вы говорите, конечно, это правильно! Эти поставки не были доставлены до прошлой недели, поэтому условие должно покрыть их!", остановитесь и подумайте об этом на секунду.

на NULL на самом деле не означает, что они не были доставлены. The NULL означает, что мы не знаю, когда они были доставлен. Вполне возможно, что клерк заберет подтверждение завтра и заполнит DeliveryDate как две недели назад, аннулируя данные, которые мы только что получили. Там должно быть не быть нулевыми экземплярами, возвращающимися из этого запроса, и все же есть. Если вы написали тот же запрос в SQL, эти результаты будут исключены.

это немного неудобно, но правильно. Мы написали запрос, который более или менее правильно передает свое намерение, и (bool?)null не равно true или false , поэтому мы получаем правильные результаты в обоих случаях.

Если вам когда - либо нужно оценить условие, в котором ответ может быть "я не знаю" - используйте bool? (он же Nullable<bool> ) в результате.

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

Ограничения являются средством, с помощью которого БД может принуждать к выполнению бизнес-правил и гарантировать что данные соответсвуют модели сущность-связь определённой системным анализом определяя структуры данных приложения. Например, бизнес-аналитик в компании решил что каждый покупатель и каждый заказ должен определяться уникальным числом, что нельзя создать заказ до создания покупателя и каждый заказ должен иметь валидную дату и сумму большую нуля. Это может быть достигнуто путём создания первичных ключей для столбца 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 это

UNIQUE
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK

У ограничений есть имя. Хорошей практикой считается именовать их согласно конвенции имен, но если явно не указывать имя, 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),

10 hiredate date,

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

Первая таблица создаётся с именем DEPT и предназначена для хранения одной строки для каждого департамента.
DEPTNO – числовое поле, 2 цифры, нет точек после запятой. Первичный ключ таблицы – имя ограничения DEPT_DEPTNO_PK.
Второе ограничение столбца DEPTNO это проверка значения на период от 10 до 90. Имя этого ограничения DEPT_DEPTNO_CK.
Столбец DNAME – это строка нефиксированной длины, обязательна для заполнения – ограничение DEPT_DNAME_NN.
Вторая табилца это EMP, предназаченная для хранения строки для каждого сотрудника.
Столбец EMPNO типа данных NUMERIC, 4 цифры без дробной части первичный ключ EMP_EMPNO_PK. ENAME
Столбец ENAME строка произвольной длины до 20 символов с not null ограничением EMP_ENAME_NN
Столбец MGR определён так же как и EMPNO плюс является внешним ключом таблицы к самой себе по полям MGR-EMP_NO. Нет ограничения на not-null поэтому может быть NULL
DOB – дата рождения сотрудника, тип данных дата нет ограничений
HIREDATE – дата приёма на работу, нет ограничений
DEPTNO департамент, определение столбца такое же как столбец DEPTNO таблицы DEPT. Внешний ключ к таблице департаментов: нельзя вставить строку со значением которого нет в таблице DEPT. Но значение может быть
Внешний ключ EMP_DEPTO_FK определён с директивой ON DELETE SET NULL, т.е. при удалении строки из таблицы DEPT значение столбца DEPTNO во всех относящиеся к удаляемой строке будет обновлено в значение NULL.
EMAIL – строка переменной длины и имеет ограничение уникальности (но может быть NULL)
Определение дополнительного ограничения EMP_HIREDATE_CK. Дата приёма на работу должна быть не раньше чем 16 лет после даты рождения. Данное ограничение невозможно создать в строке определения столбца HIREDATE, так как ограничение ссылается на другой столбец
Определение дополнительного ограничения проверки значения для строки EMAIL. Вызываются функции которые проверяют наличие @ и “.” в значении и если хотя бы одна функция вернёт ЛОЖЬ то строка будет отклонена.

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

Управление созданием индексов для внешних ключей и ограничений уникальности

Определение времени проверки ограничения: во время вставки (значение по умолчанию) или позже, в момент подтверждения транзакции

Включено ли ограничение (по умолчанию) или отключено

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

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

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

ENABLEVALIDATE – Невозможно добавить строку которая нарушает ограничение и все строки таблицы удовлетворяют ограничениям
DISABLENOVALIDATE – Любые данные возможно ввести и в таблицу уже могут быть данные нарушающие ограничения
ENABLENOVALIDATE – В таблице могут существовать данные не удовлетворяющие ограничениям, но все добавляемые данные должны удовлетворять ограничениям
DISABLEVALIDATE – невозможная ситуация: все данные в таблице удовлетворяют ограничениям, но новые данные должны неудовлетворять. Т.е. нельзя вставить данные в таблицу.

Идеальной ситуацией (и состоянием по умолчанию при определении ограничения) является 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;

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;

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.

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