Создание внешнего ключа Oracle
Обновлено: 04.07.2024
Вопрос: мне нужно понять, как добавить оператор ограничения внешнего ключа, чтобы убедиться, что все строки существуют в главной таблице. Как изменить значение столбца, чтобы превратить его в ограничение внешнего ключа?
Ограничение внешнего ключа используется для обеспечения связи между двумя таблицами. В качестве примера возьмем две таблицы, ITEM и PART. Эти таблицы имеют отношение (у элемента может не быть ни одной части, одна или несколько частей). Ограничения внешнего ключа помогают обеспечить соблюдение этих отношений. В мире администраторов баз данных мы используем диаграммы, называемые диаграммами отношений сущностей (ERD), чтобы визуально показать, как связаны таблицы. Вот ERD, который показывает, как соотносятся таблицы ITEM и PART.
Обратите внимание, что таблицы ITEM и PART представлены в виде двух рамок. Между ними есть линия. Теперь обратите внимание на две строки из таблицы PART в основную строку между таблицами ITEM и PART. Мы называем это «гусиными лапками». Когда вы видите гусиные лапки, это означает, что между таблицами существует связь один ко многим.
На приведенной выше диаграмме между таблицами ITEM и PART существует связь "один ко многим". У предмета может не быть частей, предназначенных для его сборки (возможно, он новый и находится в разработке), или у него может быть одна или несколько частей, которые используются для сборки этой части. Обратите внимание на круг в верхней части гусиных лапок. Поскольку он не заполнен, это означает, что в таблице деталей может не быть деталей. Если бы кружок был затемнен, это означало бы, что с каждым элементом должна быть связана хотя бы одна часть.
Итак, как это связано с ограничениями внешнего ключа? Что ж, ограничения внешнего ключа помогают обеспечить соблюдение типов отношений между таблицами, которые мы только что продемонстрировали. В этом примере мы создадим таблицы ITEM и PART. В процессе этого мы создадим отношение внешнего ключа между ними:
В этом примере нас действительно интересует создание таблицы ITEM. Во-первых, обратите внимание, что мы определили первичный ключ как нестандартный первичный ключ. Это связано с тем, что это составной первичный ключ, а составные первичные ключи должны быть определены вне очереди.
Теперь нас интересует определение внешнего ключа. Вы должны определить ограничения внешнего ключа как внешние ограничения, как мы сделали в нашем примере. Вот фрагмент команды, которую мы использовали:
ОГРАНИЧЕНИЕ fk_item_part ВНЕШНИЙ КЛЮЧ (part_no) ССЫЛКИ часть (part_no);
Обратите внимание, что мы начинаем с ключевого слова ограничения. Это сообщает Oracle, что мы собираемся определить ограничение. Затем мы называем ограничение. В данном случае мы назвали ограничение FK_ITEM_PART. Имена ограничений должны быть уникальными для каждой таблицы и могут содержать не более 30 символов. Затем мы используем ключевое слово внешнего ключа, чтобы указать, что мы определяем ограничение внешнего ключа. Затем мы определяем столбец в создаваемой таблице, которому принадлежит этот внешний ключ.
Далее мы используем ключевое слово references, чтобы указать, что мы собираемся определить таблицу и столбец, на которые ссылается этот внешний ключ. Имя ссылочной таблицы указано следующим. В данном случае это таблица PART. Наконец, указан столбец в таблице PART, на который делается ссылка, PART_NO.
ИЗМЕНИТЬ ТАБЛИЦУ my_status ДОБАВИТЬ ОГРАНИЧЕНИЕ fk_my_status
ВНЕШНИЙ КЛЮЧ (part_no) REFERENCES часть (part_no);
Пример ограничения внешнего ключа для применения бизнес-правила
Ссылочная целостность, ограничение внешнего ключа, идентифицирует столбец таблицы или представления как внешний ключ. Этот столбец внешнего ключа устанавливает прямую связь с первичным ключом или столбцом уникального ключа (ссылочный ключ), обычно в другой таблице. Таблица, содержащая внешний ключ, называется дочерней, а таблица, содержащая указанный ключ, — родительской. Внешний ключ и ключ, на который указывает ссылка, могут находиться в одной и той же таблице (родительская и дочерняя — это одна и та же таблица); в противном случае родительская и дочерняя таблицы должны находиться в одной базе данных.
Вот синтаксис для создания ограничения внешнего ключа:
ALTER TABLE (имя_таблицы)
ДОБАВИТЬ
CONSTRAINT (имя ограничения внешнего ключа)
FOREIGN KEY
(имя поля)
REFERENCES primary_table_name
(primary_table_primary_index_field )
Используя наш пример, указанный выше в ограничении ссылок, кодирование будет выглядеть следующим образом:
ALTER TABLE зависимо
ДОБАВИТЬ
ОГРАНИЧЕНИЕ ( emp_num_fk )
ВНЕШНИЙ КЛЮЧ
( emp_num )
ССЫЛКИ сотрудника ( emp_num );
Несмотря на то, что ограничения внешнего ключа всегда следует использовать вместо кодированной проверки целостности приложения, важно понимать, что использование ограничений ссылочной целостности увеличивает нагрузку на ввод-вывод для insert и update< /i> операции. Дополнительный ввод-вывод связан с тем, что внешний ключ должен быть проверен с помощью родительской таблицы. Эти накладные расходы можно свести к минимуму, убедившись, что столбцы, на которые ссылаются, индексируются.Во время массовой загрузки может быть полезно отключить ограничения целостности перед загрузкой и повторно включить ограничения после завершения загрузки. После повторного включения ограничений любые ошибки могут быть отмечены и обработаны.
Получите полную
информацию о настройке Oracle SQL
Знаменитая книга "Расширенная настройка Oracle SQL. Полное руководство" содержит ценную информацию о настройке Oracle SQL. Эта книга включает сценарии и инструменты для повышения производительности Oracle 11g, и вы можете купить ее со скидкой 30 % непосредственно у издателя.
Бурлесон — американская команда
Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.
Проверьте опыт! Любой, кто рассматривает возможность использования услуг эксперта службы поддержки Oracle, должен самостоятельно проверить свои полномочия и опыт, а не полагаться на рекламу и самопровозглашенный опыт. Все законные эксперты Oracle публикуют свои квалификации Oracle.
Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:
и укажите URL-адрес страницы.
Burleson Consulting
Оракул поддержки баз данных
Вывод: в этом руководстве вы узнаете, как использовать внешний ключ Oracle для установления связи между таблицами.
Введение в ограничение внешнего ключа Oracle
Внешний ключ связан с отношениями. Давайте начнем с примера, чтобы четко понять его концепцию.
Предположим, у нас есть две таблицы supplier_groups и Suppliers :
В таблице supplier_groups хранятся группы поставщиков, например разовые поставщики, сторонние поставщики и межкорпоративные поставщики. В каждой группе поставщиков может быть ноль, один или несколько поставщиков.
В таблице поставщиков хранится информация о поставщиках. Каждый поставщик должен принадлежать к группе поставщиков.
Связь между supplier_groups и таблицей Suppliers — один ко многим. Другими словами, у одной группы поставщиков много поставщиков, а каждый поставщик должен принадлежать к группе поставщиков.
Идентификатор группы в таблице поставщиков используется для установления связи между строками в таблицах поставщиков и групп поставщиков.
Прежде чем вставлять строку в таблицу поставщиков, необходимо найти существующий group_id в таблице supplier_groups и использовать это значение для вставки.
Предположим, что таблица supplier_groups содержит следующие данные:
Чтобы вставить нового стороннего поставщика, вы должны использовать group_id 2 следующим образом:
Все работает отлично. Однако следующий оператор также работает:
В таблице supplier_groups нет строки с идентификатором группы 4, но ничто не мешает вам вставить ее в таблицу поставщиков, что является проблемой.
Например, следующий запрос не может получить всех поставщиков и их группы:
Как видите, поставщик WD отсутствует в наборе результатов.
Решением этой проблемы является использование ограничения внешнего ключа Oracle для обеспечения связи между строками из таблиц supplier_groups и Suppliers.
Сначала удалите таблицу поставщиков:
Во-вторых, заново создайте таблицу поставщиков с ограничением внешнего ключа:
В этом заявлении было добавлено следующее предложение:
Это предложение определяет столбец group_id в таблице поставщиков как внешний ключ, ссылающийся на столбец group_id таблицы supplier_groups.
Таким образом, ограничение применяется Oracle. Другими словами, попытка вставить в таблицу поставщиков строку, не соответствующую какой-либо строке в таблице supplier_groups, потерпит неудачу, как и попытка удалить строку из таблицы supplier_groups, в которой существуют зависимые строки в таблице Suppliers.
Таблица поставщиков называется дочерней, а таблица supplier_groups — родительской. Чтобы расширить аналогию родитель-потомок, значение первичного ключа берется из родительской таблицы ( supplier_groups ) и вставляется в дочернюю таблицу ( Suppliers ), т. е. дочерний элемент несет ВНЕШНИЙ КЛЮЧ как ДНК родителя или генетический код.
Кстати, концепция ссылочной целостности заключается в поддержании и обеспечении соблюдения этих отношений родитель-потомок.
Ограничение внешнего ключа Oracle в действиях
Следующее утверждение работает, потому что в таблице supplier_groups есть строка с идентификатором group_id 1:
Однако следующее выражение не будет выполнено:
Поскольку в группе supplier_groups нет строки с идентификатором 4. Ниже приводится сообщение об ошибке:
Аналогичным образом попытка удалить строку с group_id 1 в таблице supplier_groups завершится ошибкой:
Oracle выдал следующее сообщение об ошибке:
Потому что в таблице поставщиков (дочерней таблице) есть строка, ссылающаяся на удаляемую строку.
Синтаксис ограничения внешнего ключа Oracle
Oracle позволяет создавать, добавлять, удалять, отключать и включать ограничение внешнего ключа.
Создайте ограничение внешнего ключа
Следующее утверждение иллюстрирует синтаксис создания ограничения внешнего ключа при создании таблицы:
Давайте подробно рассмотрим это заявление.
Во-первых, чтобы явно присвоить ограничению внешнего ключа имя, вы используете предложение CONSTRAINT, за которым следует имя. Предложение CONSTRAINT является необязательным. Если вы его не укажете, Oracle присвоит ограничению внешнего ключа сгенерированное системой имя.
Во-вторых, укажите предложение FOREIGN KEY, чтобы определить один или несколько столбцов как внешний ключ и родительскую таблицу со столбцами, на которые ссылаются столбцы внешнего ключа.
В-третьих, используйте предложение ON DELETE, чтобы указать последствия удаления строк в родительской таблице.
- НА КАСКАДНОМ УДАЛЕНИИ: если строка в родительской таблице удалена, все строки в дочерней таблице, которые ссылаются на удаленную строку, будут удалены.
- ON DELETE SET NULL: если строка в родительской таблице удалена, то все строки в дочерней таблице, ссылающиеся на удаленную строку, будут установлены в NULL для столбцов внешнего ключа.
В отличие от ограничения первичного ключа, таблица может иметь более одного ограничения внешнего ключа.
Добавить ограничение внешнего ключа в таблицу
Если вы хотите добавить ограничение внешнего ключа к существующей таблице, используйте оператор ALTER TABLE следующим образом:
Удалить ограничение внешнего ключа
Чтобы удалить ограничение внешнего ключа, используйте приведенный ниже оператор ALTER TABLE:
Отключить ограничение внешнего ключа
Чтобы временно отключить внешнее ограничение, используйте следующую инструкцию ALTER TABLE:
Включить внешнее ограничение
Аналогичным образом вы также используете оператор ALTER TABLE, чтобы включить отключенное ограничение внешнего ключа:
В этом руководстве вы узнали, как использовать ограничение внешнего ключа Oracle для обеспечения связи между таблицами.
Присоединяйтесь к сообществу DZone и получите все возможности участника.
В этой статье я объясню, как создать внешний ключ для таблицы в Oracle SQL.
Что такое внешний ключ?
Во-первых, давайте объясним, что такое внешний ключ.
Внешний ключ — это ограничение, добавляемое к таблице. Он позволяет указать, что столбец в таблице ссылается на первичный ключ другой таблицы.
Он используется для связывания данных в двух таблицах и улучшения целостности ваших данных.
Существует два способа создания внешнего ключа для таблицы в Oracle: встроенный метод и внешний метод. Я задокументировал их оба в своем руководстве по оператору CREATE TABLE здесь, и я объясню их в этой статье для вас.
Внешний ключ с использованием встроенного ограничения
Чтобы создать внешний ключ с использованием синтаксиса встроенного ограничения, используйте следующую команду:
Используя этот синтаксис, вы указываете ключевое слово CREATE, затем имя таблицы, а затем открываете квадратные скобки. Для столбца, который вы хотите указать в качестве внешнего ключа, добавьте слово REFERENCES в конец (после типа данных).
Затем укажите имя другой таблицы. Это таблица, содержащая первичный ключ, на который будет ссылаться ваш новый внешний ключ. Например, если у вас есть таблица отделов и вы создаете новую таблицу сотрудников, в которой есть поле идентификатора отдела, вашей «другой таблицей» будет таблица отдела.
Далее укажите имя столбца, на который ссылается внешний ключ в другой таблице, в квадратных скобках. Если мы используем таблицу сотрудников и отделов, это будет столбец Department_id (или то, что вы назвали первичным ключом в таблице отделов).
Наконец, продолжайте определять столбцы и таблицу как обычно. Ваш внешний ключ будет создан при запуске инструкции.
Внешний ключ с использованием внестрочного ограничения
Еще один способ объявления внешнего ключа — использование метода внешнего ограничения.
Это делается путем объявления ограничения после объявления всех ваших столбцов.
Преимущество этого метода заключается в том, что вы можете присвоить ограничению внешнего ключа имя, которое будет полезно для его изменения, включения и отключения позднее.При использовании встроенного метода имя создается автоматически базой данных Oracle.
Синтаксис для создания внешнего ключа с использованием внешнего метода:
Вы начинаете с объявления имени таблицы и всех необходимых столбцов.
После того, как все столбцы объявлены, но все еще находятся в квадратных скобках, вы добавляете слово CONSTRAINT . Это означает, что вы будете определять ограничение.
Затем укажите имя для нового ограничения. Это может быть до 30 символов. Я предпочитаю придерживаться стандарта при именовании своих ограничений, поэтому я начинаю имена с fk .
После названия ограничения добавьте слова FOREIGN KEY, чтобы указать, что это ограничение внешнего ключа.
Затем откройте квадратные скобки и добавьте имя столбца в этой таблице, который будет внешним ключом. Затем закройте скобки.
Затем добавьте слово REFERENCES , а затем название другой таблицы, на которую вы ссылаетесь. Используя приведенный выше пример, если вы создаете таблицу сотрудников и хотите, чтобы id_отдела ссылался на таблицу отделов, это будет таблица отделов.
Затем откройте квадратные скобки, добавьте имя столбца в другой таблице (в данном случае таблицы отдела) и закройте квадратные скобки.
Наконец, закройте скобки в операторе создания таблицы и добавьте точку с запятой. Оператор теперь готов к запуску. Внешний ключ будет создан с указанным вами именем.
Дополнительная информация о параметрах ограничения внешнего ключа доступна в документации Oracle.
Вот как вы можете создать внешний ключ для новой таблицы. Вопросы оставляйте в комментариях!
Ограничение FOREIGN KEY используется для предотвращения действий, которые могут разрушить связи между таблицами.
ВНЕШНИЙ КЛЮЧ – это поле (или набор полей) в одной таблице, которое ссылается на ПЕРВИЧНЫЙ КЛЮЧ в другой таблице.
Таблица с внешним ключом называется дочерней таблицей, а таблица с первичным ключом называется ссылочной или родительской таблицей.
Посмотрите на следующие две таблицы:
Таблица лиц
| PersonID | Фамилия | Имя | Возраст |
|---|---|---|---|
| 1< /td> | Хансен | Ола | 30 |
| 2 | Свендсон | Тове | 23 |
| 3 | Петтерсен | Кари | 20 |
Таблица заказов
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1< /td> | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Обратите внимание, что столбец "PersonID" в таблице "Заказы" указывает на столбец "PersonID" в таблице "Лица".
Столбец "PersonID" в таблице "Persons" является ПЕРВИЧНЫМ КЛЮЧОМ в таблице "Persons".
Столбец "PersonID" в таблице "Заказы" является ВНЕШНИМ КЛЮЧОМ в таблице "Заказы".
Ограничение FOREIGN KEY предотвращает вставку недопустимых данных в столбец внешнего ключа, поскольку это должно быть одно из значений, содержащихся в родительской таблице.
ВНЕШНИЙ КЛЮЧ SQL при создании таблицы
Следующий SQL создает FOREIGN KEY в столбце "PersonID" при создании таблицы "Заказы":
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID ) ССЫЛКИ Персоны(PersonID)
);
SQL Server/Oracle/MS Access:
CREATE TABLE Заказы (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
Чтобы разрешить именование ограничения FOREIGN KEY и определить ограничение FOREIGN KEY для нескольких столбцов, используйте следующий синтаксис 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)
ССЫЛКИ Persons(PersonID)
);
ВНЕШНИЙ КЛЮЧ SQL для ALTER TABLE
Чтобы создать ограничение FOREIGN KEY для столбца "PersonID", когда таблица "Заказы" уже создана, используйте следующий SQL:
MySQL/SQL Server/Oracle/MS Access:
Чтобы разрешить именование ограничения FOREIGN KEY и определить ограничение FOREIGN KEY для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL/SQL Server/Oracle/MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Читайте также: