Создание внешнего ключа oracle
Summary: in this tutorial, you will learn how to use the Oracle foreign key to establish the relationship between tables.
Ограничения Rely
Обычно перед загрузкой в таблицы хранилища данных предполагается выполнение шагов ETL. Если есть уверенность в качестве данных, можно сэкономить время на загрузку, отключив проверку ограничений. Для этого применяется команда ALTER TABLE с конструкцией RELY DISABLE NOVALIDATE, как показано в следующем примере:
Состояние Enable No Validate
При этом состоянии ограничения база данных проверяет все новые вставки и обновления на соответствие условию ограничения. Поскольку существующие данные на такое соответствие не проверяются, нет уверенности, что они удовлетворяют этому условию ограничения. Обычно эта опция применяется при загрузке крупных таблиц,когда есть основания для уверенности, что эти таблицы уже удовлетворяют условию ограничения. Вот пример:
Более подходящая стратегия заключалась бы в использовании состояния DISABLE NOVALIDATE при загрузке данных, с переходом к состоянию ENABLE NOVALIDATE и затем к ENABLE VALIDATE в конце цикла извлечения, трансформации и загрузки (extraction,transformation, loading — ETL).
Enable a foreign constraint
Similarly, you use also use the ALTER TABLE statement to enable a disabled foreign key constraint:
In this tutorial, you have learned how to use the Oracle foreign key constraint to enforce the relationship between tables.
Внешний ключ - это способ обеспечения ссылочной целостности в вашей базе данных SQL Server. Внешний ключ означает, что значения в одной таблице также должны отображаться в другой таблице.
Указанная таблица называется родительской таблицей, а таблица с внешним ключом называется дочерней таблицей. Внешний ключ в дочерней таблице обычно ссылается на первичный ключ в родительской таблице.
Внешний ключ может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.
Ограничение уникальности
Ограничение уникальности (UNIQUE) очень распространено в реляционных базах данных. Эти ограничения гарантируют уникальность строк в реляционной таблице.В таблице могут существовать сразу несколько таких ограничений. Например, ограничение уникальности на столбце employee_id гарантирует, что ни один сотрудник не появится дважды в таблице employee.
В следующем примере первый оператор специфицирует ограничение уникальности на комбинации столбцов dept_name и location:
Создать уникальное ограничение на таблице department можно также с помощью такого оператора ALTER TABLE:
Oracle foreign key constraint in actions
The following statement works because the supplier_groups table has a row with group_id 1:
However, the following statement will fail:
Because the supplier_groups has no row with id 4. The following is the error message:
Similarly, attempting to delete a row with group_id 1 in the supplier_groups table will fail:
Oracle issued the following error message:
Because the suppliers table (child table) has a row that references to the row which is being deleted.
Ограничения ссылочной целостности
Ограничения ссылочной целостности гарантируют, что значения определенных важных столбцов будут иметь смысл. Предположим, что есть родительская таблица, которая ссылается на значения из другой таблицы, как в случае таблиц dept и employee.Сотрудник в таблице employee не может быть назначен в департамент, если такой департамент в таблице dept не существует.
Гарантировать существование действительного департамента можно с помощью ограничения ссылочной целостности. В этом случае столбец departament_id служит первичным ключом таблицы dept, а столбец dept_id в таблице employee, ссылающийся на соответствующий столбец таблицы department, называется внешним ключом. Таблица,содержащая внешний ключ, обычно называется дочерней таблицей, а таблица, содержащая ключ, на который ссылается внешний ключ — родительской таблицей. Как и со всеми прочими типами ограничений, ссылочные ограничения целостности можно создавать во время создания таблицы или позднее, с помощью оператора ALTER TABLE:
База данных назначает столбец dept_id таблицы employee внешним ключом, потому что он ссылается на столбец dept_id таблицы dept. Обратите внимание, что для того, чтобы столбец служил ссылочным (на который ссылаются), он должен быть уникальным или же быть первичным ключом в таблице, на которую установлена ссылка.
Drop a foreign key constraint
To drop a foreign key constraint, you use the ALTER TABLE statement below:
DBA_CONS_COLUMNS
Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:
Связь между таблицами в реляционной модели реализуется внешним ключом.
Внешний ключ — множество колонок подчинённой таблицы, соответствующее ключу главной таблицы
Я намеренно опустил уточняющее прилагательное «первичный», потому что связь в реляционной модели может проходить по любому ключу. Многие СУБД поддерживают декларативную ссылочную целостность, позволяющую определить внешний ключ со ссылкой не только на первичный ключ таблицы, но и на любой другой набор её колонок, объявленных уникальными.
Обязательная связь — строки в подчинённой таблице не могут существовать без соответствующих строк в главной таблице
Обязательная связь реализуется объявлением соответствующего столбца внешнего ключа как не допускающего неопределённых значений NOT NULL.
С таблицей очереди отправки (mailing_queue) связь осуществляется по ключу «адрес электропочты»; соответствующий столбец объявлен уникальным. Кроме того, вторая связь является ключевой.
Рис. 1. Примеры связей
Ключевая связь — связь, при которой внешний ключ таблицы входит в состав одного из её ключей.
Из этого определения следует также, что ключевая связь всегда является обязательной.
Ключевая связь на практике встречается часто, например, таблица жилых домов имеет ключ в виде идентификатора улицы и номера дома. Таким образом, между улицами и расположенными на них домами существует ключевая связь, даже если вы используете для домов суррогатный автоинкрементный первичный ключ, а колонки «Улица» и «Номер дома» забыли пометить ограничениями уникальности.
Если представить приведённую схему в виде SQL-скрипта, то в реализации SQL Server получим следующий код.
Ограничения NOT NULL
Обычно таблица имеет один или более столбцов, в которых не допускается значение null — т.е. без значений. Хорошим примером является столбец last_name таблицы employee. Заставить пользователей обязательно вносить значения в этот столбец можно при создании таблицы, указав опцию NOT NULL для столбца, который не должен быть null:
Если таблица уже создана, и требуется модифицировать столбец с допускающего null на не допускающий null, для этого можно использовать следующий оператор:
Состояние Enable Validate
Это состояние ограничения включает ограничение, гарантируя проверку всех данных на соответствие условию ограничения. Это состояние в точности то же, что обычное состояние “включено”. Следующий пример демонстрирует использование этого состояния:
Oracle foreign key constraint syntax
Oracle allows you to create, add, drop, disable and enable a foreign key constraint.
Create a foreign key constraint
The following statement illustrates the syntax of creating a foreign key constraint when you create a table:
Let’s examine the statement in detail.
First, to explicitly assign the foreign key constraint a name, you use the CONSTRAINT clause followed by the name. The CONSTRAINT clause is optional. If you omit it, Oracle will assign a system-generated name to the foreign key constraint.
Second, specify the FOREIGN KEY clause to defines one or more column as a foreign key and parent table with columns to which the foreign key columns reference.
Third, use the ON DELETE clause to specify consequence when the rows in the parent table are deleted.
- ON DELETE CASCADE : if a row in the parent is deleted, then all the rows in the child table that reference the removed row will be deleted.
- ON DELETE SET NULL : if a row in the parent is deleted, then all the rows in the child table reference the removed row will be set to NULL for the foreign key columns.
Unlike the primary key constraint, a table may have more than one foreign key constraint.
Ограничения первичного ключа
Первичный ключ — очень важный тип ограничения в таблице. Когда необходимо,чтобы значения столбца идентифицировались уникальным образом, это обеспечивается созданием первичного ключа по значению столбца. Столбец, на котором определен первичный ключ, должен быть уникальным и не null.
Таблица может иметь только один первичный ключ, который можно создать при создании таблицы, как показано в следующем примере:
Кроме того, можно добавить ограничение и к существующей таблице:
Поскольку в предыдущем примере ограничению не присвоено имя, Oracle даст ему имя, сгенерированное системой. Если требуется присвоить ограничению собственное имя, воспользуйтесь следующей командой, которая назовет ограничение dept_pk:
Обратите внимание, что если первичный ключ будет построен по более чем одному столбцу (т.е. будет составным), специфицировать назначение первичного ключа напротив имени столбца при создании таблицы нельзя. Столбцы первичного ключа должны быть указаны как отдельный элемент оператора CREATE TABLE после списка всех столбцов.
На заметку! В обоих предыдущих примерах Oracle автоматически создает индексы на столбце,который назначен первичным ключом.
Синтаксис
Синтаксис создания внешнего ключа с помощью оператора CREATE TABLE в SQL Server (Transact-SQL):
CREATE TABLE child_table
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
[ ON DELETE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
[ ON UPDATE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
);
child_table - имя дочерней таблицы, которую вы хотите создать.
column1 , column2 - столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name - имя ограничения внешнего ключа, которое вы хотите создать.
child_col1 , child_col2 , . child_col_n - столбцы в child_table , которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table - имя родительской таблицы, первичный ключ которой будет использоваться в child_table .
parent_col1 , parent_col2 , . parent_col3 - столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1 , child_col2 , . child_col_n в child_table .
ON DELETE - необязательный. Он определяет, что делать с дочерними данными при удалении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
ON UPDATE - необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.
DBA_CONSTRAINTS
Представление DBA_CONSTRAINTS предоставляет информацию обо всех типах ограничений таблицы в базе данных. Опрос этого представления делается, когда необходимо узнать, какого типа ограничения имеет таблица. Представление перечисляет несколько типов ограничений, как показано в следующем запросе:
Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы.
Обратите внимание, что если необходимо увидеть определенное ссылочное ограничение и правило удаления, то нужно применить следующую вариацию предыдущего запроса:
Introduction to Oracle foreign key constraint
A foreign key is all about the relationship. Let’s start with an example to clearly understand its concept.
Suppose, we have two tables supplier_groups and suppliers :
The supplier_groups table stores supplier groups e.g., one-time supplier, third-party supplier, and inter-co supplier. Each supplier group may have zero, one, or many suppliers.
The suppliers table stores the supplier information. Each supplier must belong to a supplier group.
The relationship between supplier_groups and suppliers table is one-to-many. In other words, one supplier group has many suppliers while each supplier must belong to a supplier group.
The group_id in the suppliers table is used for establishing the relationship between rows in the suppliers and supplier_groups tables.
Before inserting a row into the suppliers table, you have to look up an existing group_id in the supplier_groups table and use this value for insertion.
Assuming that the supplier_groups table contains the following data:
To insert a new third-party supplier, you have to use the group_id 2 as follows:
It works perfectly fine. However, the following statement also works:
The supplier_groups table has no row with group id 4 but nothing prevents you from inserting it into the suppliers table, which is an issue.
For example, the following query fails to get all suppliers and their groups:
As you can see, the WD supplier is missing in the result set.
A solution to fix this problem is to use the Oracle foreign key constraint to enforce the relationship between rows from the supplier_groups and suppliers tables.
First, drop the suppliers table:
Second, recreate the suppliers table with a foreign key constraint:
In this statement, the following clause was newly added:
This clause defines the group_id column in the suppliers table as a foreign key that references to the group_id column of the supplier_groups table.
This way, the constraint is enforced by Oracle. In other words, attempting to insert a row into the suppliers table that does not correspond to any row in the supplier_groups table will fail, as attempting to delete a row from the supplier_groups table where there exist dependent rows in the suppliers table.
The suppliers table is called a child table while the supplier_groups is referred to as a parent table. To extend the parent-child analogy, the primary key value is taken from the parent table ( supplier_groups ) and is inserted into the child table ( suppliers ) i.e., the child carries a FOREIGN KEY as the parent’s DNA or genetic code.
By the way, the concept of referential integrity is all about maintaining and enforcing this parent-child relationship.
Состояние Disable No Validate
При отключении ограничения без проверки ограничение отключается, и нет никаких гарантий соответствия данных требованиям ограничения, поскольку Oracle не предпринимает проверку его действительности. Это по существу то же самое, что команда DISABLE CONSTRAINT.
Add a foreign key constraint to a table
If you want to add a foreign key constraint to an existing table, you use the ALTER TABLE statement as follows:
Отложенные и немедленные ограничения
В дополнение к указанию типа проверки достоверности ограничения можно специфицировать, когда именно должно проверяться ограничение в процессе загрузки данных.
Если хотите, чтобы ограничение проверялось немедленно после модификации данных, выберите опцию not defferable (не откладывая), которая, фактически задает поведение по умолчанию в базах данных Oracle. Если необходимо выполнить едино-временную проверку ограничения после фиксации всей транзакции, выберите опцию deferrable. Все ограничения и внешние ключи могут быть объявлены как deferrable или not deferrable.
В случае выбора опции deferrable появляются еще две дополнительных опции.Ограничение deferrable можно специфицировать либо как initially deferred, либо как initially immediate (изначально отложенное или изначально немедленное). В первом случае база данных откладывает проверки ограничения до окончания транзакции. Если же была выбрана опция initially immediate, то база данных проверит ограничения перед изменением любых данных. Обратите внимание, что если предварительно создается индекс, то он должен быть не уникальным, чтобы обработать дублированные значения.
Следующий пример показывает, как специфицировать такого рода ограничения в таблице employee:
В Oracle также предусмотрен способ изменения отложенного ограничения с immediate на deferred или наоборот с помощью следующих операторов:
Создание внешнего ключа - использование оператора CREATE TABLE
Представления, относящиеся к ограничениям и индексам
Состояния ограничений целостности
Как было показано в предыдущем выше, ограничения целостности определяются на таблицах для гарантии, что данные, нарушающие заранее установленные правила,в таблицу не попадут. Однако иногда, например, при загрузке данных, ограничения целостности не должны поддерживаться в действительном состоянии, поскольку это приведет к определенным проблемам. При необходимости Oracle позволяет отключать ограничения и затем включать их, когда они снова понадобятся. Давайте рассмотрим некоторые способы изменения состояния ограничений таблиц.
При загрузке больших объемов данных с использованием SQL*Loader или утилиты Import может потребоваться значительное время на загрузку данных, каждую строку которых нужно проверять на предмет нарушения целостности. Более эффективная стратегия предусматривает отключение ограничения, загрузку данных и последующую проверку корректности загруженных данных. После завершения загрузки ограничения вновь вводятся в действие посредством включения. Когда ограничение отключается,как описано здесь, база данных уничтожает индекс. Подобным образом реализуется лучшая стратегия — предварительное создание неуникальных индексов для ограничений, которые база данных не должна уничтожать, поскольку они обрабатывают дублированные записи.
На заметку! Состояние enabled (включено) — это состояние ограничений Oracle по умолчанию
Отключаются ограничения двумя способами: указание в качестве состояния ограничения либо disable validate (отключить с проверкой), либо disable no validate (отключить без проверки) с использованием конструкции DISABLE VALIDATE и DISABLE NO VALIDATE, соответственно. Аналогично, для включения ограничения применяются конструкции ENABLE VALIDATE и ENABLE NO VALIDATE. Ниже кратко обсуждаются различные способы включения и отключения ограничений.
Пример
Рассмотрим пример создания внешнего ключа в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE. Например:
MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
13.1.17.5 FOREIGN KEY Constraints
MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.
A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.
The essential syntax for a defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following:
Foreign key constraint usage is described under the following topics in this section:
Identifiers
Foreign key constraint naming is governed by the following rules:
The CONSTRAINT symbol value is used, if defined.
If the CONSTRAINT symbol clause is not defined, or a symbol is not included following the CONSTRAINT keyword:
For InnoDB tables, a constraint name is generated automatically.
For NDB tables, the FOREIGN KEY index_name value is used, if defined. Otherwise, a constraint name is generated automatically.
The CONSTRAINT symbol value, if defined, must be unique in the database. A duplicate symbol results in an error similar to: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121) .
Table and column identifiers in a FOREIGN KEY . REFERENCES clause can be quoted within backticks ( ` ). Alternatively, double quotation marks ( " ) can be used if the ANSI_QUOTES SQL mode is enabled. The lower_case_table_names system variable setting is also taken into account.
Conditions and Restrictions
Foreign key constraints are subject to the following conditions and restrictions:
Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables.
Creating a foreign key constraint requires at least one of the SELECT , INSERT , UPDATE , DELETE , or REFERENCES privileges on the parent table as of 5.6.22.
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same . The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “ child table record ” refers to a dependent record within the same table.
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name , if given, is used as described previously.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 14.6.2.1, “Clustered and Secondary Indexes”).
NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.
Index prefixes on foreign key columns are not supported. Consequently, BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.
InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.
This restriction does not apply for NDB tables that are partitioned by KEY or LINEAR KEY (the only user partitioning types supported by the NDB storage engine); these may have foreign key references or be the targets of such references.
A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.
For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.
Referential Actions
When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Referential actions include:
CASCADE : Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.
Cascaded foreign key actions do not activate triggers.
SET NULL : Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL . Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL .
RESTRICT : Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION ) is the same as omitting the ON DELETE or ON UPDATE clause.
NO ACTION : A keyword from standard SQL. In MySQL, equivalent to RESTRICT . The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT .
SET DEFAULT : This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
For storage engines that support foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.
For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT .
For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.
InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.
Foreign Key Constraint Examples
This simple example relates parent and child tables through a single-column foreign key:
This is a more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:
Adding Foreign Key Constraints
You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:
The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE , remember to first create an index on the column(s) referenced by the foreign key.
Dropping Foreign Key Constraints
You can drop a foreign key constraint using the following ALTER TABLE syntax:
If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE :
Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE . ALGORITHM=INPLACE . It is not supported for ALTER TABLE . ALGORITHM=COPY .
Foreign Key Checks
The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.8, “Using System Variables”.
Disabling foreign key checking is useful when:
Dropping a table that is referenced by a foreign key constraint. A referenced table can only be dropped after foreign_key_checks is disabled. When you drop a table, constraints defined on the table are also dropped.
Reloading tables in different order than required by their foreign key relationships. For example, mysqldump produces correct definitions of tables in the dump file, including foreign key constraints for child tables. To make it easier to reload dump files for tables with foreign key relationships, mysqldump automatically includes a statement in the dump output that disables foreign_key_checks . This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys. Disabling foreign_key_checks also speeds up the import operation by avoiding foreign key checks.
Executing LOAD DATA operations, to avoid foreign key checking.
Performing an ALTER TABLE operation on a table that has a foreign key relationship.
When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:
Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.
Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table.
Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.
Creating a foreign key constraint where a column references a nonmatching column type.
Disabling foreign_key_checks has these additional implications:
It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database.
It is permitted to drop a table with foreign keys referenced by other tables.
Enabling foreign_key_checks does not trigger a scan of table data, which means that rows added to a table while foreign_key_checks is disabled are not checked for consistency when foreign_key_checks is re-enabled.
Foreign Key Definitions and Metadata
To view a foreign key definition, use SHOW CREATE TABLE :
Foreign Key Errors
In the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking SHOW ENGINE INNODB STATUS output.
ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables, even if the user has no parent table access privileges. To hide information about parent tables, include the appropriate condition handlers in application code and stored programs.
Ограничения целостности в реляционных базах данных (и Oracle Database в частности) позволяют легко и автоматически применять важные бизнес-правила к таблицам базы данных. Например, в таблице, используемой в системе управления кадрами, нельзя завести сотрудника, не назначив ему руководителя. При создании связанных таблиц можно объявить необходимые ограничения целостности, которые должны быть удовлетворены при каждом вводе или модификации данных в таблице.
Для выполнения бизнес-правил можно также задействовать логику приложения,однако ограничения целостности обычно применять проще, и обычно они хорошо выполняют свою работу, гарантируя, что вставки, обновления и удаления данных таблиц будут соответствовать определенным правилам. С другой стороны, логика приложения обладает тем преимуществом, что позволяет отклонить или подтвердить данные без обращения к содержимому таблицы. Таким образом, выбирать метод выполнения бизнес-правил — логикой приложения или ограничениями целостности — необходимо на основе потребностей конкретного приложения. В любом случае ограничения целостности настолько фундаментальны для операций в реляционных базах данных, что вы обречены на их использование в конкретной базе.
По умолчанию Oracle разрешает значения null во всех столбцах. Если эти значения недопустимы для некоторых столбцов таблицы, для них необходимо задавать ограничение NOT NULL. Обратите внимание, что налагать ограничения базы данных на таблицы можно как во время их создания, так и позднее, с помощью команды ALTER TABLE.Однако очевидно, что если в таблице уже имеются значения null или дублированные данные, то изменить таблицу, применив ограничение NOT NULL или ограничение уникальности, будет невозможно.
Для таблицы Oracle можно установить несколько типов ограничений. Упрощенно их можно разделить на пять основных типов:
Все перечисленные типы ограничений обсуждаются далее в этой статье. В дополнение будет также представлен краткий обзор состояний ограничений целостности.
Состояние Disable Validate
В случае использования команды DISABLE VALIDATE выполняются следующие два действия сразу. Во-первых, конструкция VALIDATE гарантирует, что все данные в таблице удовлетворяют условию ограничения. Во-вторых, конструкция DISABLE избавляет от необходимости поддерживать ограничение. Oracle сбрасывает индекс ограничения,но сохраняет его действительным. Вот пример:
После выдачи приведенного выше оператора SQL наличие только уникальных комбинаций уникальных ключей prod_id и customer_id в таблице гарантируется, однако уникальный индекс не поддерживается.
Обратите внимание, что поскольку было решено сохранить ограничение в отключенном состоянии, никаких DML-действий в отношении таблицы выполнять нельзя. Эта опция идеально подходит для крупных таблиц хранилищ данных, которые обычно используются только для извлечения данных по запросам.
Проверочные ограничения
используете Проверочные (CHECK) ограничения применяются для обеспечения соответствия данных столбца определенным параметрам, которые вы укажете. Например,предположим, что годовая зарплата сотрудника фирмы не может быть равна или превышать $100 000 при определенных обстоятельствах. Это ограничение можно навязать с помощью следующего оператора, который устанавливает ограничение CHECK на столбце SALARY:
Disable a foreign key constraint
To temporarily disable a foreign constraint, you use the following ALTER TABLE statement:
Читайте также:
- Может ли провайдер отключить интернет за посещение запрещенных сайтов
- Настольный считыватель usb используемые типы идентификаторов em marin hid pr eh08 parsec
- Вот мои документы все дело в том что компьютер в офисе
- Для чего на борту мкс нужно большое количество компьютеров и вычислительной техники
- Где в омске сдать акб от ибп