Создание внешнего ключа oracle

Обновлено: 07.07.2024

FOREIGN KEY - это ключ, используемый для соединения двух таблиц вместе.

FOREIGN KEY - это поле (или набор полей) в одной таблице, которое ссылается на первичный ключ в другой таблице.

Таблица, содержащая внешний ключ, называется дочерней таблицей, а таблица, содержащая ключ кандидат, называется ссылочной или родительской таблицей.

Посмотрите на следующие две таблицы:

PersonID	LastName	FirstName	Age
1	Hansen	Ola	30
2	Svendson	Tove	23
3	Pettersen	Kari	20

OrderID	OrderNumber	PersonID
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Обратите внимание, что столбец "PersonID" в таблице "Orders" указывает на столбец "PersonID" в таблице "Persons".

Столбец "PersonID" в таблице "Persons" является первичным ключом в таблице "Persons".

Столбец "PersonID" в таблице "Orders" является внешним ключом в таблице "Orders".

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

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

SQL FOREIGN KEY в CREATE TABLE

Следующий SQL создает внешний ключ в столбце "PersonID" при создании таблицы "Orders":

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

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

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY в ALTER TABLE

Чтобы создать ограничение внешнего ключа для столбца "PersonID", когда таблица "Orders" уже создана, используйте следующий SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

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.

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

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

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

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации стуктуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.

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

Синтаксис ограничения таблицы FOREIGN KEY:

FOREIGN KEY <column list> REFERENCES

Первый список столбцов — это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой.

Pktable — это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой.

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

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

FOREIGN KEY Пример 1

CREATE TABLE Student
( Kod_stud integer NOT NULL PRIMARY KEY,
Kod_spec integer NOT NULL,
Fam char(30) NOT NULL UNIQUE,
Adres char(50),
Ball decimal),
FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec)
);

При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.

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

FOREIGN KEY Пример 2

CREATE TABLE Student (
Kod_stud integer NOT NULL PRIMARY KEY,
Fam char(30) NOT NULL UNIQUE,
Adres char(50),
Ball decimal),
Kod_spec integer REFERENCES Spec
);

Поддержание ссылочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).

Этого не достаточно для родительского ключа в случае выполнения такого требования, как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.

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

Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, необходимо создать внешний ключ, который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранения структуры базы данных ясной и простой. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или пустые (NULL). Попытка ввести другие значения в этот ключ будет отклонена.

FOREIGN KEY Пример 3

CREATE TABLE payment (
sh_payout integer,
sh_eml integer,
date_payout date,
summ_payout real,
FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid)
);

В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.

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, a constraint name name is generated automatically.

Prior to MySQL 8.0.16, if the CONSTRAINT symbol clause was not defined, or a symbol was not included following the CONSTRAINT keyword, both InnoDB and NDB storage engines would use the FOREIGN_KEY index_name if defined. In MySQL 8.0.16 and higher, the FOREIGN_KEY index_name is ignored.

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) .

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 the REFERENCES privilege on the parent table.

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.

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.

A foreign key constraint cannot reference a virtual generated column.

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

If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

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.

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 an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION .

As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump . RESTRICT , which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump .

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.

A foreign key constraint on a stored generated column cannot use CASCADE , SET NULL , or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot use CASCADE , SET NULL , or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

Foreign Key Constraint Examples

This simple example relates parent and child tables through a single-column foreign key:

Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:

Dropping Foreign Key Constraints

You can drop a foreign key constraint using the following ALTER TABLE syntax:

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

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. The foreign_key_checks variable has the same effect on NDB tables as it does for InnoDB tables.

The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.9, “Using System Variables”.