Внешние ключи с ON DELETE SET NULL
📖 Описание
ON DELETE SET NULL сохраняет дочерние строки при удалении родительской строки и устанавливает NULL в столбцах их внешнего ключа. Это правило задаётся в CREATE TABLE или ALTER TABLE.
⚙️ Использование CREATE TABLE
🧩 Синтаксис
Синтаксис для создания внешнего ключа с ON DELETE SET NULL с помощью оператора CREATE TABLE в Oracle/PLSQL:
CREATE TABLE table_name
(
column1 datatype [NULL | NOT NULL],
column2 datatype [NULL | NOT NULL],
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL
);
💻 Пример: ключ из одного столбца
Собственные учебные таблицы описывают студии и занятия. Родительский PRIMARY KEY уникален; несколько занятий могут ссылаться на одну студию.
CREATE TABLE demo_252_studios (
studio_id NUMBER(6) CONSTRAINT demo_252_studios_pk PRIMARY KEY,
studio_name VARCHAR2(60 CHAR) NOT NULL
);
CREATE TABLE demo_252_sessions (
session_id NUMBER(6) PRIMARY KEY,
studio_id NUMBER(6),
session_day DATE NOT NULL,
CONSTRAINT demo_252_studio_fk FOREIGN KEY (studio_id)
REFERENCES demo_252_studios(studio_id) ON DELETE SET NULL
);
INSERT INTO demo_252_studios VALUES (17, 'River Clay');
INSERT INTO demo_252_studios VALUES (18, 'Garden Paper');
INSERT ALL
INTO demo_252_sessions VALUES (81, 17, DATE '2026-12-05')
INTO demo_252_sessions VALUES (82, 17, DATE '2026-12-12')
INTO demo_252_sessions VALUES (83, 18, DATE '2026-12-19')
SELECT 1 FROM dual;
COMMIT;
Попробуем удалить студию 17, на которую ссылаются два занятия:
SAVEPOINT before_252_delete;
DELETE FROM demo_252_studios WHERE studio_id = 17;
SELECT session_id, studio_id FROM demo_252_sessions ORDER BY session_id;
ROLLBACK TO before_252_delete;
Удаляется студия 17. Все занятия сохраняются, но у двух связанных строк studio_id становится NULL:
| SESSION_ID | STUDIO_ID |
|---|---|
| 81 | NULL |
| 82 | NULL |
| 83 | 18 |
После ROLLBACK TO исходные данные восстановлены. Изменения дочерних строк входят в ту же транзакцию.
💻 Пример: составной внешний ключ
Студия может иметь несколько помещений. Пара studio_id и room_code составляет первичный ключ; дочерняя строка должна ссылаться на соответствующую пару.
CREATE TABLE demo_252_rooms (
studio_id NUMBER(6),
room_code VARCHAR2(10 CHAR),
room_name VARCHAR2(60 CHAR) NOT NULL,
CONSTRAINT demo_252_rooms_pk PRIMARY KEY (studio_id, room_code)
);
CREATE TABLE demo_252_bookings (
booking_id NUMBER(6) PRIMARY KEY,
studio_id NUMBER(6),
room_code VARCHAR2(10 CHAR),
booking_day DATE NOT NULL,
CONSTRAINT demo_252_room_fk FOREIGN KEY (studio_id, room_code)
REFERENCES demo_252_rooms(studio_id, room_code) ON DELETE SET NULL
);
INSERT INTO demo_252_rooms VALUES (17, 'CLAY', 'Clay room');
INSERT INTO demo_252_rooms VALUES (17, 'PAPER', 'Paper room');
INSERT INTO demo_252_bookings VALUES (91, 17, 'CLAY', DATE '2026-12-05');
INSERT INTO demo_252_bookings VALUES (92, 17, 'PAPER', DATE '2026-12-12');
COMMIT;
SAVEPOINT before_252_composite;
DELETE FROM demo_252_rooms WHERE studio_id = 17 AND room_code = 'CLAY';
SELECT booking_id, studio_id, room_code FROM demo_252_bookings ORDER BY booking_id;
ROLLBACK TO before_252_composite;
До отката бронь 91 сохраняется, и оба столбца её внешнего ключа становятся NULL:
| BOOKING_ID | STUDIO_ID | ROOM_CODE |
|---|---|---|
| 91 | NULL | NULL |
| 92 | 17 | PAPER |
⚙️ Использование ALTER TABLE
🧩 Синтаксис
ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (child_column1, child_column2)
REFERENCES parent_table (parent_column1, parent_column2) ON DELETE SET NULL;
💻 Один столбец
Родительские таблицы уже созданы выше. Для добавления ключа создадим отдельную дочернюю таблицу без этого ограничения:
CREATE TABLE demo_252_later_sessions (
session_id NUMBER(6) PRIMARY KEY,
studio_id NUMBER(6),
session_day DATE NOT NULL
);
ALTER TABLE demo_252_later_sessions
ADD CONSTRAINT demo_252_later_studio_fk FOREIGN KEY (studio_id)
REFERENCES demo_252_studios(studio_id) ON DELETE SET NULL;
INSERT INTO demo_252_later_sessions VALUES (84, 18, DATE '2026-12-26');
COMMIT;
💻 Несколько столбцов
CREATE TABLE demo_252_later_bookings (
booking_id NUMBER(6) PRIMARY KEY,
studio_id NUMBER(6),
room_code VARCHAR2(10 CHAR),
booking_day DATE NOT NULL
);
ALTER TABLE demo_252_later_bookings
ADD CONSTRAINT demo_252_later_room_fk FOREIGN KEY (studio_id, room_code)
REFERENCES demo_252_rooms(studio_id, room_code) ON DELETE SET NULL;
INSERT INTO demo_252_later_bookings VALUES (93, 17, 'PAPER', DATE '2026-12-26');
COMMIT;
SELECT constraint_name, delete_rule, status, validated
FROM user_constraints
WHERE constraint_name IN ('DEMO_252_LATER_STUDIO_FK', 'DEMO_252_LATER_ROOM_FK')
ORDER BY constraint_name;
Оба ограничения имеют STATUS=ENABLED, VALIDATED=VALIDATED и DELETE_RULE=SET NULL. Добавление проверяет существующие строки; неверные ссылки нужно исправить заранее.
📝 Примечание
Все столбцы внешнего ключа должны допускать NULL: иначе удаление родителя попытается нарушить NOT NULL. Для составной связи NULL устанавливается во всех столбцах ключа затронутой строки. Само ограничение FOREIGN KEY не требует заполненности столбцов; при NULL в одном из компонентов составного ключа Oracle не требует соответствующей родительской строки.
DDL фиксирует транзакцию; все опыты с DELETE завершены откатом до следующего CREATE/ALTER. Новые имена разделяют четыре примера и позволяют изучать их последовательно.
См. внешние ключи, каскадное удаление, ON DELETE SET NULL, отключение и включение ключа.