Меню

Mysql связи между таблицами on delete



MySQL. Внешние ключи

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

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

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

Общий синтаксис установки внешнего ключа на уровне таблицы:

Например, определим две таблицы и свяжем их посредством внешнего ключа:

В данном случае определены таблицы customers и orders . Таблица customers является главной и представляет клиента. Таблица orders является зависимой и представляет заказ, сделанный клиентом. Таблица orders через столбец customer_id связана с таблицей customers и ее столбцом id . То есть столбец customer_id является внешним ключом, который указывает на столбец id из таблицы customers .

Мы также могли бы определить внешний ключ на уровне таблицы:

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:

  • CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
  • SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL (столбец внешнего ключа должен поддерживать установку NULL ).
  • RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
  • NO ACTION : то же самое, что и RESTRICT .
  • SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибута DEFAULT . Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL .

Источник

Внешние ключи MySQL и их настройка в phpMyAdmin

Привет, первая запись в 2017 году! Пока все доедают салатики, я решил написать небольшую заметочку про ограничения внешних ключей в базе данных MySQL.

Зачем нужны внешние ключи в таблице

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

Читайте также:  Значения почвы для растений таблица

В двух словах — во внешних ключах много плюсов, поэтому полезно знать, как ими оперировать.

Настройка внешних ключей

Я буду демонстрировать настройку в своем любимом php админе потому что это очень удобно, кроме того, админ не утаивает сгенерированный код запросов и вы всегда сможете его посмотреть(запомнить, скопировать, раскритиковать).

Пример ManyToOne и OneToMany

Две таблицы: goods(id, name, catalog_id) и catalogs(id, name) . В одном каталоге множество товаров(OneToMany), и сколько угодно товаров может быть в одном каталоге(ManyToOne). Внешним ключом в этом примере является поле catalog_id в таблице goods .

Создание таблиц в phpmyadmin

Создание таблицы с внешним ключом в phpmyadmin

Создание таблицы в phpmyadmin

Сгенерированные запросы

Настройка внешнего ключа catalog_id

Пришло время создать связь между товарами и каталогами. Открываем таблицу goods , переходим во вкладку «структура» и ищем кнопку «связи».

Настройка связей таблиц в phpmyadmin

В разделе «ограничения внешнего ключа» заполняем строки и выбираем действия «ON DELETE» и «ON UPDATE».

Ограничения внешнего ключа в phpmyadmin

Сгенерированный запрос

Значения ON DELETE и ON UPDATE

CASCADE — Каскадное удаление и редактирование. Эта настройка означает, что при удалении каталога, все товары из него тоже удалятся. При редактировании, если мы изменим id каталога, у товаров автоматически изменится поле «catalog_id».

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

SET NULL — Из названия видно, что если исчезнет(удалится или изменится) каталог с таким id, то у товаров в поле «catalog_id» установится значение NULL. С этой настройкой нужно вести себя осторожно, потому что по умолчанию индексы «NOT NULL».

NO ACTION — Игнорируем удаление и редактирование каталога, и пусть в поле «catalog_id» будет несуществующий идентификатор, просто игнорируем это.

Пример ManyToMany

Случай посложнее, в нем, чтобы не дублировать записи, создают отдельную таблицу связей, которая обычно состоит из двух полей. Рассмотрим пример: authors(id, name) , books(id, name) , author_book(author_id, book_id) . Книга может быть написана соавторами, и у автора может быть множество книг — отличный пример связи ManyToMany.

Создание таблицы связей

В phpmyadmin

Создание промежуточной таблицы связей в phpmyadmin

Создание составного ключа в phpmyadmin

Сгенерированный запрос

author_id и book_id вместе являются составным индексом, осталось только добавить ограничения на каждый из них во вкладке «связи» и все готово!

Заключение

Не упускайте возможность сложить побольше обязанностей на базу данных, чтобы облегчить себе работу над приложением, и думать о его структуре, а не о контроле табличный связей. Удачи с проектированием баз данных, спасибо за внимание!

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

Create Foreign Keys with cascade delete SQL Server

В этом учебном пособии вы узнаете, как использовать внешние ключи с каскадным удалением в SQL Server с синтаксисом и примерами.

Описание

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

Внешний ключ с каскадным удалением может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.

Создание внешнего ключа с каскадным удалением — использование оператора CREATE TABLE

Синтаксис

Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):

CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
ON DELETE CASCADE
[ 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 CASCADE — указывает, что дочерние данные удаляются при удалении родительских данных.
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. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.

Пример

Давайте рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например:

Источник

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

Применимо к: SQL Server 2016 (13.x); и более поздние версии База данных SQL Azure Управляемый экземпляр SQL Azure

Можно удалить ограничение внешнего ключа в SQL Server при помощи среды SQL Server Management Studio или Transact-SQL. При удалении ограничения внешнего ключа удаляется требование принудительного создания ссылочной целостности.

В этом разделе

Перед началом работы

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

Перед началом

безопасность

Permissions

Требуется разрешение ALTER на таблицу.

Использование среды SQL Server Management Studio

Удаление ограничения внешнего ключа

Разверните в обозревателе объектов таблицу с ограничением, после чего разверните узел Ключи.

Щелкните правой кнопкой мыши ограничение и выберите команду Удалить.

В диалоговом окне Удаление объекта нажмите кнопку ОК.

Использование Transact-SQL

Удаление ограничения внешнего ключа

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

На стандартной панели выберите пункт Создать запрос.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL).

Источник

Для новичков о внешних ключах в базах данных

Victor Bolshov

Jan 26, 2017 · 3 min read

Поступил такой вопрос от девушки, которая изучает программирование, а именно PHP+MySQL:

Собственно, вот вопрос…

У меня есть табличка orders и табличка users. Сейчас в orders есть поле user_id, по которому и осуществляется связь. Вопрос — как правильно увязать их при помощи foreign key? И самое главное, как потом эту связь использовать в php? Идеально было бы получить ответ в виде текста (логики) и кода. То есть, например:

1) При текущем положении дел код пхп такой-то, чтобы достать что то из базы

2) При связности через foreign key код пхп такой-то…

Итак, “правильно увязать их при помощи foreign key”:

Читайте также:  Индекс амниотической жидкости норма 32 недели беременности таблица

Теперь подробнее. Что нам дает внешний ключ?

“С” — consistency.

По-русски “согласованность”. Data consistency — это очень большая тема, внешние ключи служат для обеспечения лишь одного ее аспекта, а именно “Referential integrity”, то есть “ссылочная целостность”. В нашем примере, если без внешнего ключа попробовать добавить заказ для несуществующего user_id, база данных спокойно его добавит. При условии наличия внешнего ключа это будет невозможно, БД выдаст ошибку:

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

Вернемся к нашему запросу, который добавляет внешний ключ. ON DELETE RESTRICT ON UPDATE CASCADE — это что еще такое? Таким образом мы определяем поведение поля, которое ссылается на другую таблицу, при изменении соответствующих данных в этой таблице. ON DELETE RESTRICT означает, что если попробовать удалить пользователя, у которого в таблице заказов есть данные, БД не даст этого сделать:

В данном случае, поскольку это заказы, то есть данные, которые, скорее всего, достаточно важны, я поставил ограничение (RESTRICT). Если бы я указал ON DELETE CASCADE, БД сама удалила бы все заказы пользователя при его удалении. Есть еще одна опция — ON DELETE SET NULL. При ее использовании БД запишет NULL в качестве user_id для всех заказов удаленного пользователя.

ON UPDATE CASCADE говорит о том, что в случае если кто-то решит изменить ID пользователя, все его заказы получат новый, измененный ID. Зачем может понадобиться менять ID — это другой вопрос, операция крайне редкая.

Теперь к PHP. Как ни странно, код, достающий данные из БД, никак не изменится при использовании внешних ключей. Внешние ключи лишь накладывают ограничения на данные, так что вы всегда уверены в том, что связи между данными в разных таблицах не нарушены.

Если вы решите, что при удалении пользователя можно удалить все его заказы, вы можете поставить поведение ON DELETE CASCADE, и тогда для удаления пользователя вам достаточно будет удалить один ряд из таблицы users. Об остальном позаботится СУБД.

Резюмируя: внешние ключи не делают ничего магического. СУБД с их помощью может выполнить небольшую часть работы за вас (каскадные удаления и изменения), но в целом на приложение это влияет незначительно. Зато дает 100% гарантию целостности связей между таблицами.

Стоит также отметить:

  1. То, что БД делает больше работы, означает больше накладных расходов. Некоторые операции могут замедлиться при использовании внешних ключей.
  2. В MySQL внешние ключи поддерживаются не всеми движками. Не забудьте проверить, что ваши таблицы имеют Engine= InnoDB. Есть и другие движки с поддержкой внешних ключей, но InnoDB самый распространенный.

Источник

Adblock
detector