Not null enable oracle что это
Summary: in this tutorial, you will learn how to use the Oracle IS NULL and IS NOT NULL operators to check if a value in a column or an expression is NULL or not.
Oracle IS NOT NULL operator
To negate the IS NULL operator, you use the IS NOT NULL operator as follows:
The operator IS NOT NULL returns true if the expression or value in the column is not null. Otherwise, it returns false.
For example, the following example returns all sales orders which have a responsible salesman:
This picture illustrates the partial output:
In this tutorial, you have learned how to use the Oracle IS NULL and IS NOT NULL to check if an expression or value in a column is null or not.
Ограничения целостности в реляционных базах данных (и Oracle Database в частности) позволяют легко и автоматически применять важные бизнес-правила к таблицам базы данных. Например, в таблице, используемой в системе управления кадрами, нельзя завести сотрудника, не назначив ему руководителя. При создании связанных таблиц можно объявить необходимые ограничения целостности, которые должны быть удовлетворены при каждом вводе или модификации данных в таблице.
Для выполнения бизнес-правил можно также задействовать логику приложения,однако ограничения целостности обычно применять проще, и обычно они хорошо выполняют свою работу, гарантируя, что вставки, обновления и удаления данных таблиц будут соответствовать определенным правилам. С другой стороны, логика приложения обладает тем преимуществом, что позволяет отклонить или подтвердить данные без обращения к содержимому таблицы. Таким образом, выбирать метод выполнения бизнес-правил — логикой приложения или ограничениями целостности — необходимо на основе потребностей конкретного приложения. В любом случае ограничения целостности настолько фундаментальны для операций в реляционных базах данных, что вы обречены на их использование в конкретной базе.
По умолчанию Oracle разрешает значения null во всех столбцах. Если эти значения недопустимы для некоторых столбцов таблицы, для них необходимо задавать ограничение NOT NULL. Обратите внимание, что налагать ограничения базы данных на таблицы можно как во время их создания, так и позднее, с помощью команды ALTER TABLE.Однако очевидно, что если в таблице уже имеются значения null или дублированные данные, то изменить таблицу, применив ограничение NOT NULL или ограничение уникальности, будет невозможно.
Для таблицы Oracle можно установить несколько типов ограничений. Упрощенно их можно разделить на пять основных типов:
Все перечисленные типы ограничений обсуждаются далее в этой статье. В дополнение будет также представлен краткий обзор состояний ограничений целостности.
Состояние Enable No Validate
При этом состоянии ограничения база данных проверяет все новые вставки и обновления на соответствие условию ограничения. Поскольку существующие данные на такое соответствие не проверяются, нет уверенности, что они удовлетворяют этому условию ограничения. Обычно эта опция применяется при загрузке крупных таблиц,когда есть основания для уверенности, что эти таблицы уже удовлетворяют условию ограничения. Вот пример:
Более подходящая стратегия заключалась бы в использовании состояния DISABLE NOVALIDATE при загрузке данных, с переходом к состоянию ENABLE NOVALIDATE и затем к ENABLE VALIDATE в конце цикла извлечения, трансформации и загрузки (extraction,transformation, loading — ETL).
Состояния ограничений целостности
Как было показано в предыдущем выше, ограничения целостности определяются на таблицах для гарантии, что данные, нарушающие заранее установленные правила,в таблицу не попадут. Однако иногда, например, при загрузке данных, ограничения целостности не должны поддерживаться в действительном состоянии, поскольку это приведет к определенным проблемам. При необходимости Oracle позволяет отключать ограничения и затем включать их, когда они снова понадобятся. Давайте рассмотрим некоторые способы изменения состояния ограничений таблиц.
При загрузке больших объемов данных с использованием SQL*Loader или утилиты Import может потребоваться значительное время на загрузку данных, каждую строку которых нужно проверять на предмет нарушения целостности. Более эффективная стратегия предусматривает отключение ограничения, загрузку данных и последующую проверку корректности загруженных данных. После завершения загрузки ограничения вновь вводятся в действие посредством включения. Когда ограничение отключается,как описано здесь, база данных уничтожает индекс. Подобным образом реализуется лучшая стратегия — предварительное создание неуникальных индексов для ограничений, которые база данных не должна уничтожать, поскольку они обрабатывают дублированные записи.
На заметку! Состояние enabled (включено) — это состояние ограничений Oracle по умолчанию
Отключаются ограничения двумя способами: указание в качестве состояния ограничения либо disable validate (отключить с проверкой), либо disable no validate (отключить без проверки) с использованием конструкции DISABLE VALIDATE и DISABLE NO VALIDATE, соответственно. Аналогично, для включения ограничения применяются конструкции ENABLE VALIDATE и ENABLE NO VALIDATE. Ниже кратко обсуждаются различные способы включения и отключения ограничений.
Параметры или аргументы
Ограничения ссылочной целостности
Ограничения ссылочной целостности гарантируют, что значения определенных важных столбцов будут иметь смысл. Предположим, что есть родительская таблица, которая ссылается на значения из другой таблицы, как в случае таблиц dept и employee.Сотрудник в таблице employee не может быть назначен в департамент, если такой департамент в таблице dept не существует.
Гарантировать существование действительного департамента можно с помощью ограничения ссылочной целостности. В этом случае столбец departament_id служит первичным ключом таблицы dept, а столбец dept_id в таблице employee, ссылающийся на соответствующий столбец таблицы department, называется внешним ключом. Таблица,содержащая внешний ключ, обычно называется дочерней таблицей, а таблица, содержащая ключ, на который ссылается внешний ключ — родительской таблицей. Как и со всеми прочими типами ограничений, ссылочные ограничения целостности можно создавать во время создания таблицы или позднее, с помощью оператора ALTER TABLE:
База данных назначает столбец dept_id таблицы employee внешним ключом, потому что он ссылается на столбец dept_id таблицы dept. Обратите внимание, что для того, чтобы столбец служил ссылочным (на который ссылаются), он должен быть уникальным или же быть первичным ключом в таблице, на которую установлена ссылка.
Ограничения NOT NULL
Обычно таблица имеет один или более столбцов, в которых не допускается значение null — т.е. без значений. Хорошим примером является столбец last_name таблицы employee. Заставить пользователей обязательно вносить значения в этот столбец можно при создании таблицы, указав опцию NOT NULL для столбца, который не должен быть null:
Если таблица уже создана, и требуется модифицировать столбец с допускающего null на не допускающий null, для этого можно использовать следующий оператор:
Oracle NOT NULL constraint examples
The following statement creates the surcharges table:
The surcharges table has three columns: surcharge id, surcharge name, and amount.
The surcharge_id column is the primary key column of the table specified by the PRIMARY KEY constraint, therefore, Oracle implicitly adds a NOT NULL constraint to this column.
The surcharge_name column has a NOT NULL constraint specified explicitly in the column definition.
The amount column can accept NULL values.
The following statement inserts a row into the surcharges table:
It works as expected.
However, the following statement does not work:
Because it attempts to insert a NULL value into the surcharge column which has a NOT NULL constraint.
The following statement works because the amount column accepts NULL values:
The following statement displays all constraints of the surcharges table:
If you want to add a NOT NULL constraint to the amount column, you use the following ALTER TABLE statement:
The following error occurred:
Because the surcharges table contains a NULL value.
So before adding the NOT NULL constraint, you need to make sure that the existing data in the surcharges table does not violate the NOT NULL constraint:
Now, if you execute the ALTER TABLE statement again:
It should work as expected.
Отложенные и немедленные ограничения
В дополнение к указанию типа проверки достоверности ограничения можно специфицировать, когда именно должно проверяться ограничение в процессе загрузки данных.
Если хотите, чтобы ограничение проверялось немедленно после модификации данных, выберите опцию not defferable (не откладывая), которая, фактически задает поведение по умолчанию в базах данных Oracle. Если необходимо выполнить едино-временную проверку ограничения после фиксации всей транзакции, выберите опцию deferrable. Все ограничения и внешние ключи могут быть объявлены как deferrable или not deferrable.
В случае выбора опции deferrable появляются еще две дополнительных опции.Ограничение deferrable можно специфицировать либо как initially deferred, либо как initially immediate (изначально отложенное или изначально немедленное). В первом случае база данных откладывает проверки ограничения до окончания транзакции. Если же была выбрана опция initially immediate, то база данных проверит ограничения перед изменением любых данных. Обратите внимание, что если предварительно создается индекс, то он должен быть не уникальным, чтобы обработать дублированные значения.
Следующий пример показывает, как специфицировать такого рода ограничения в таблице employee:
В Oracle также предусмотрен способ изменения отложенного ограничения с immediate на deferred или наоборот с помощью следующих операторов:
Пример - использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
Summary: in this tutorial, you will learn how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.
An Oracle NOT NULL constraint specifies that a column cannot contain NULL values. The Oracle NOT NULL constraints are inline constraints which are typically used in the column definition of the CREATE TABLE statement.
It is possible to add a NOT NULL constraint to an existing table by using the ALTER TABLE statement.
In this case, the column_name must not contain any NULL value before applying the NOT NULL constraint.
Состояние Disable No Validate
При отключении ограничения без проверки ограничение отключается, и нет никаких гарантий соответствия данных требованиям ограничения, поскольку Oracle не предпринимает проверку его действительности. Это по существу то же самое, что команда DISABLE CONSTRAINT.
Состояние Enable Validate
Это состояние ограничения включает ограничение, гарантируя проверку всех данных на соответствие условию ограничения. Это состояние в точности то же, что обычное состояние “включено”. Следующий пример демонстрирует использование этого состояния:
DBA_CONSTRAINTS
Представление DBA_CONSTRAINTS предоставляет информацию обо всех типах ограничений таблицы в базе данных. Опрос этого представления делается, когда необходимо узнать, какого типа ограничения имеет таблица. Представление перечисляет несколько типов ограничений, как показано в следующем запросе:
Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы.
Обратите внимание, что если необходимо увидеть определенное ссылочное ограничение и правило удаления, то нужно применить следующую вариацию предыдущего запроса:
Introduction to the Oracle IS NULL operator
In the database world, NULL is special. It is a marker for missing information or the information is not applicable.
NULL is special in the sense that it is not a value like a number, character string, or datetime, therefore, you cannot compare it with any other values like zero (0) or an empty string (”). Generally speaking, NULL is even not equal to NULL.
Let’s see the orders table from the sample database.
The salesman_id column stores the salesman id of the salesman who is in charge of the sales order.
The following SELECT statement attempts to return all sales orders which do not have a responsible salesman:
It returns an empty row.
The query uses the comparison operator ( = ) to compare the values from the salesman_id column with NULL , which is not correct.
To check if a value is NULL or not, you should use the IS NULL operator as follows:
The IS NULL operator returns true if the expression or column is NULL . Otherwise, it returns false.
The following query returns all sales orders that do not have a responsible salesman:
Here is the partial output of the query:
Ограничения Rely
Обычно перед загрузкой в таблицы хранилища данных предполагается выполнение шагов ETL. Если есть уверенность в качестве данных, можно сэкономить время на загрузку, отключив проверку ограничений. Для этого применяется команда ALTER TABLE с конструкцией RELY DISABLE NOVALIDATE, как показано в следующем примере:
2 Answers 2
This is a known bug (description is not public unfortunately):
Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF
The NOT NULL constraints are copied as NOVALIDATE, and you have to set them to VALIDATE state manually, e.g:
The primary key issue is similar, but there is no bug reported for that. However I have found that disabling and reenabling it solves the problem.
The Not Null clause of a table column definition sets a flag in $sys$cols and generates a sys_xxxxxx constraint on the column.
dbms_redef however just copies the sys_xxxxxxx constraint and omits setting the flag. It's this flag that gets decoded into the "Nullable?" display (say) on SQLDeveloper columns tab of a table.
So what's the effect of this -
Well, referential integrity is maintained - trying to insert a null into a column where the constraint exists still fails.
However I read somewhere (I'll have to find that article again) that the CBO looks at the $sys$cols flag (not the constraint) as part of optimisation for certain queries.
Consider an index based on a not null column. This index may be a candidate for satisfying a select count(*) type query rather than doing a FTS. If the $sys$cols is itself null (not set by re-def) that index won't be considered.
I think it is worth the effort of adding steps to your re-def code
After re-def - save the names of not null cols from constraints
- drop not null constraints - reapply via alter table modify col not null clause
Even just to quieten the Java dev moaners who spot the before and after difference.
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
DBA_CONS_COLUMNS
Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:
Please note the not null constraint on the column ts1 .
Using dbms_redefinition , I specifically use copy_constraints => true .
Everything seems to work fine except that desc in SQL*Plus doesn't correctly display the not null constraint:
However, somewhere, the not null constraints is in place. I can see it by issuing a
Also, if I try to insert a record [ insert into tq84_redefinition (id) values (999999) ] I get the (correct) error message ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1") .
Edit: The version (v$version) is:
Edit 2 @Munchi, the result of your suggested select statement
The version is 11.2 (see my edit). I don't use alter table because I need to convert the timestamp s to date s. Besides, I don't want to change the nullableness of the columns.
just wondering, what's the output of the following?select column_name as "Name" , nullable as "Null?" , concat(concat(concat(data_type,'('),data_length),')') as "Type" from user_tab_columns where table_name = 'TQ84_REDEFINITION';
Drop NOT NULL constraints
Sometimes, you need to change a column with a NOT NULL constraint to accept NULL values.
To do this, you need to remove the NOT NULL constraint from the column by using the ALTER TABLE statement as below:
For example, to drop the NOT NULL constraint from the amount column of the surcharges table, you use the following statement:
In this tutorial, you have learned how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.
Пример - использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
Введите следующий запрос UPDATE:
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
Вот результаты, которые вы должны получить:
Ограничение уникальности
Ограничение уникальности (UNIQUE) очень распространено в реляционных базах данных. Эти ограничения гарантируют уникальность строк в реляционной таблице.В таблице могут существовать сразу несколько таких ограничений. Например, ограничение уникальности на столбце employee_id гарантирует, что ни один сотрудник не появится дважды в таблице employee.
В следующем примере первый оператор специфицирует ограничение уникальности на комбинации столбцов dept_name и location:
Создать уникальное ограничение на таблице department можно также с помощью такого оператора ALTER TABLE:
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
Представления, относящиеся к ограничениям и индексам
Ограничения первичного ключа
Первичный ключ — очень важный тип ограничения в таблице. Когда необходимо,чтобы значения столбца идентифицировались уникальным образом, это обеспечивается созданием первичного ключа по значению столбца. Столбец, на котором определен первичный ключ, должен быть уникальным и не null.
Таблица может иметь только один первичный ключ, который можно создать при создании таблицы, как показано в следующем примере:
Кроме того, можно добавить ограничение и к существующей таблице:
Поскольку в предыдущем примере ограничению не присвоено имя, Oracle даст ему имя, сгенерированное системой. Если требуется присвоить ограничению собственное имя, воспользуйтесь следующей командой, которая назовет ограничение dept_pk:
Обратите внимание, что если первичный ключ будет построен по более чем одному столбцу (т.е. будет составным), специфицировать назначение первичного ключа напротив имени столбца при создании таблицы нельзя. Столбцы первичного ключа должны быть указаны как отдельный элемент оператора CREATE TABLE после списка всех столбцов.
На заметку! В обоих предыдущих примерах Oracle автоматически создает индексы на столбце,который назначен первичным ключом.
Состояние Disable Validate
В случае использования команды DISABLE VALIDATE выполняются следующие два действия сразу. Во-первых, конструкция VALIDATE гарантирует, что все данные в таблице удовлетворяют условию ограничения. Во-вторых, конструкция DISABLE избавляет от необходимости поддерживать ограничение. Oracle сбрасывает индекс ограничения,но сохраняет его действительным. Вот пример:
После выдачи приведенного выше оператора SQL наличие только уникальных комбинаций уникальных ключей prod_id и customer_id в таблице гарантируется, однако уникальный индекс не поддерживается.
Обратите внимание, что поскольку было решено сохранить ограничение в отключенном состоянии, никаких DML-действий в отношении таблицы выполнять нельзя. Эта опция идеально подходит для крупных таблиц хранилищ данных, которые обычно используются только для извлечения данных по запросам.
Проверочные ограничения
используете Проверочные (CHECK) ограничения применяются для обеспечения соответствия данных столбца определенным параметрам, которые вы укажете. Например,предположим, что годовая зарплата сотрудника фирмы не может быть равна или превышать $100 000 при определенных обстоятельствах. Это ограничение можно навязать с помощью следующего оператора, который устанавливает ограничение CHECK на столбце SALARY:
Пример - использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
product_id | product_name | category_id |
---|---|---|
1 | Pear | 50 |
2 | Banana | 50 |
3 | Orange | 50 |
4 | Apple | 50 |
5 | Bread | 75 |
6 | Sliced Ham | 25 |
7 | Kleenex | NULL |
Введите следующий SQL оператор:
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
product_id | product_name | category_id |
---|---|---|
1 | Pear | 50 |
2 | Banana | 50 |
3 | Orange | 50 |
4 | Apple | 50 |
5 | Bread | 75 |
6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Читайте также: