Как работает ON DELETE CASCADE в MySQL?
В случае удаления строки данных по конкретному report_id что произойдет в дочерних таблицах? Будут удалены данные в одной только «ячейке» report_id (с обращением в NULL, тогда у этого аргумента должно быть допустимо NULL; тогда соответственно в чем разница с SET NULL)? Или будет удалена вся строка данных в дочерней таблице, связанная с удаленным конкретным report_id (т.е. и конкретный report_id и соответствующий ему user_id)?
PS: не судите строго за качество вопроса и кода — учусь в процессе.
- Вопрос задан более трёх лет назад
- 3556 просмотров
Комментировать
Решения вопроса 0
Ответы на вопрос 1
ON DELETE CASCADE — удалит строку в подчиненной таблице при удалении в главной.
SET NULL — установить значение ID в NULL если удалить строку в главной таблице. Для этого в подчиненной таблице для этой колонки должно быть позволено NULL значение.
Ограничения устанавливаются в каждой подчиненной таблице. Что установите ON DELETE CASCADE или SET NULL — то и произойдет.
Предложение CONSTRAINT
Таблицы ограничение аналогичны индекс, однако их также можно использовать для создания связи отношение другой таблицей.
Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания и удаления ограничений. Существует два типа предложений CONSTRAINT: для создания ограничения на одно поле и на несколько полей.
Примечание: Ядро СУБД Microsoft Access не поддерживает использование CONSTRAINT или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого применяйте методы Create DAO.
Синтаксис
Ограничение на одно поле:
CONSTRAINT имя REFERENCES внешняя_таблица [(внешнее_поле1, внешнее_поле2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Ограничение на несколько полей:
CONSTRAINT имя
первичный_ключ1[, первичный_ключ2 [, . ]]) |
UNIQUE (уникальный_ключ1[, уникальный_ключ2 [, . ]]) |
NOT NULL (непустое1[, непустое2 [, . ]]) |
FOREIGN KEY [NO INDEX] (ссылка1[, ссылка2 [, . ]]) REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, . ]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT включает в себя следующие элементы:
Имя создаваемого ограничения.
первичный_ключ1, первичный_ключ2
Имена полей, определяемых как первичный ключ.
уникальный_ключ1, уникальный_ключ2
Имена полей, определяемых как уникальный ключ.
непустое1, непустое2
Имена полей, в которых не может быть значений NULL.
ссылка1, ссылка2
Имя поля или полей внешнего ключа, которые ссылаются на поля в другой таблице.
внешняя_таблица
Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.
внешнее_поле1, внешнее_поле2
Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.
Замечания
Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.
Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.
В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:
- Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- Зарезервированные слова PRIMARY KEY используются для назначения одного поля или совокупности полей таблицы в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и отличными от NULL, причем в таблице может быть только один первичный ключ.
Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))
Пример определения для таблицы Orders, в котором задается отношение внешнего ключа, ссылающееся на первичный ключ таблицы Customers:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
Для внешнего ключа определены предложения ON UPDATE CASCADE и ON DELETE CASCADE. Предложение ON UPDATE CASCADE означает, что при обновлении в таблице Customers идентификатора клиента (CustId) будет выполнено каскадное обновление в таблице Orders. В каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически внесено новое значение. Предложение ON DELETE CASCADE означает, что при удалении клиента из таблицы Customers все строки таблицы Orders, содержащие его идентификатор, также будут удалены.
Другое определение для таблицы Orders, в котором вместо действия CASCADE используется SET NULL:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Предложение ON UPDATE SET NULL означает, что при обновлении в таблице Customers идентификатора клиента (CustId) соответствующие значения внешнего ключа в таблице Orders автоматически изменятся на NULL. Аналогично предложение ON DELETE SET NULL означает, что при удалении клиента из таблицы Customers все соответствующие внешние ключи в таблице Orders принимают значение NULL.
Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
¶ Каскадное удаление записей
Каскадное удаление записей — это функциональность, предоставляемая PostgreSQL, которая позволяет автоматически удалять связанные записи при удалении родительской записи. Эта функция очень полезна для поддержания целостности данных и упрощает процесс удаления связанных данных.
Когда строка удаляется из родительской таблицы, PostgreSQL проверяет наличие связанных строк в дочерней таблице. Если такие строки найдены, они будут удалены автоматически.
Важно быть осторожным при использовании ON DELETE CASCADE , так как это может привести к к удалению большого количества связанных строк из дочерней таблицы, что может быть нежелательным. Перед использованием ON DELETE CASCADE необходимо тщательно продумать и протестировать его воздействие на данные в базе данных.
Подробнее про ON DELETE CASCADE:
¶ Использование ON DELETE CASCADE в ozma.io
В ozma.io для колонок с типом reference можно указать опцию ON DELETE CASCADE . Это означает, что при удалении «связанной» записи, на которую есть ссылка, будет удалена и текущая запись.
¶ Пример
Рассмотрим использование ON DELETE CASCADE на примере колонки таблицы public.column_fields :
| Name | Type | Is nullable |
|---|---|---|
| entity_id | reference(public.entities) on delete cascade | false |
| name | string | false |
| type | string | false |
| default | string | true |
| is_immutable | bool | false |
| is_nullable | bool | false |
В данном примере колонка entity_id является ссылкой на сущность ( public.entities ). Она задана с опцией on delete cascade , что означает, что при удалении сущности, на которую ссылается entity_id , будут каскадно удалены все связанные с ней колонки.
¶ Вызов FunDB.recursiveDeleteEntity
Использование ON DELETE CASCADE добавляет ограничения на создание и использование ON DELETE триггеров.
Чтобы обойти это ограничение, в FunAPI существует вызов функции FunDB.recursiveDeleteEntity , который позволяет удалить запись и все ссылки на нее. Его можно использовать в DELETE триггере, отказавшись от использования опции ON DELETE CASCADE .
Более подробную информацию о сигнатуре этой и других функций FunAPI можно найти по ссылке ниже:
¶ Пример
// action: "user"."delete_recursive" export default async function deleteRecursive(args) < const entity = args.entity; const const info = await FunDB.getRelatedEntities(entity, id); FunDB.writeEvent(JSON.stringify(info, null, 3)); await FunDB.recursiveDeleteEntity(entity, id); return < ok: true >> В данном примере функция deleteRecursive удаляет запись и все ссылки на нее. Она вызывает функцию FunDB.getRelatedEntities , которая возвращает объект со ссылками на указанную запись, а затем вызывает функцию FunDB.recursiveDeleteEntity , которая удаляет все ссылки и саму запись.
Кнопка на форме:
< $id reference(base.people) >: SELECT @type = 'form', @buttons = [< caption: 'Delete Record', display: 'desktop', action: < schema: 'user', name: 'delete_recursive' >, args: < entity: < schema: 'base', name: 'people' >, id: $id > >] . Синтаксис добавления ограничения. Параметр ON DELETE CASCADE
Используйте инструкцию ALTER TABLE в следующих целях:
- добавление или удаление ограничения без изменения структуры;
- включение или отключение ограничений;
- добавление ограничения NOT NULL с помощью предложения MODIFY .
| ALTER TABLE ADD [CONSTRAINT ] type (); |
|---|
Для существующих таблиц можно добавить ограничение, используя инструкцию ALTER TABLE с предложением ADD .
- table имя таблицы;
- constraint имя ограничения;
- type тип ограничения;
- column имя столбца, на который распространяется ограничение.
Синтаксис имени ограничения является необязательным, хотя рекомендуется его придерживаться. Если ограничениям не присваивать имена, имена ограничений создаются системой.
- Ограничение можно добавить, удалить, включить или отключить, однако невозможно изменить структуру ограничения.
- В существующий столбец можно добавить ограничение NOT NULL , используя предложение MODIFY инструкции ALTER TABLE .
Примечание. Столбец NOT NULL можно определить только в том случае, если таблица является пустой или если в столбце имеется значение для каждой строки.
Добавление ограничения
Добавьте в таблицу EMP2 ограничение внешнего ключа ( FOREIGN KEY ), указывающее, что менеджер уже должен существовать в таблице EMP2 как допустимый сотрудник.
В первом примере на рисунке изменяется таблица EMP2 для добавления ограничения первичного ключа ( PRIMARY KEY ) на столбец EMPLOYEE_ID . Обратите внимание, что поскольку имя ограничения не указано, ограничение автоматически именуется сервером Oracle. Во втором примере на рисунке создается ограничение внешнего ключа ( FOREIGN KEY ) на таблицу EMP2 . Ограничение гарантирует, что менеджер будет существовать в таблице EMP2 как допустимый сотрудник.
Параметр ON DELETE CASCADE
Удаляет дочерние строки при удалении родительского ключа:
Действие ON DELETE CASCADE позволяет удалять, но не обновлять данные родительского ключа, на которые имеются ссылки из дочерней таблицы. Когда данные в родительском ключе удаляются, все строки в дочерней таблице, которые зависят от значений удаленного родительского ключа, также удаляются. Чтобы задать это ссылочное действие, включите параметр ON DELETE CASCADE в определение ограничения FOREIGN KEY .
Далее: Сведения о таблицах