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

📖 Описание

Внешний ключ (FOREIGN KEY) поддерживает ссылочную целостность. Дочерняя таблица содержит внешний ключ, который ссылается на PRIMARY KEY или UNIQUE родительской таблицы. Связь задаётся в CREATE TABLE или ALTER TABLE.


⚙️ Использование CREATE TABLE


🧩 Синтаксис

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

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

💻 Пример: ключ из одного столбца

Собственные учебные таблицы описывают студии и занятия. Родительский PRIMARY KEY уникален; несколько занятий могут ссылаться на одну студию.

CREATE TABLE demo_253_studios (
  studio_id NUMBER(6) CONSTRAINT demo_253_studios_pk PRIMARY KEY,
  studio_name VARCHAR2(60 CHAR) NOT NULL
);
CREATE TABLE demo_253_sessions (
  session_id NUMBER(6) PRIMARY KEY,
  studio_id NUMBER(6) NOT NULL,
  session_day DATE NOT NULL,
  CONSTRAINT demo_253_studio_fk FOREIGN KEY (studio_id)
    REFERENCES demo_253_studios(studio_id)
);
INSERT INTO demo_253_studios VALUES (17, 'River Clay');
INSERT INTO demo_253_studios VALUES (18, 'Garden Paper');
INSERT ALL
  INTO demo_253_sessions VALUES (81, 17, DATE '2026-12-05')
  INTO demo_253_sessions VALUES (82, 17, DATE '2026-12-12')
  INTO demo_253_sessions VALUES (83, 18, DATE '2026-12-19')
SELECT 1 FROM dual;
COMMIT;

Попробуем удалить студию 17, на которую ссылаются два занятия:

SAVEPOINT before_253_delete;
DELETE FROM demo_253_studios WHERE studio_id = 17;
SELECT session_id, studio_id FROM demo_253_sessions ORDER BY session_id;
ROLLBACK TO before_253_delete;

DELETE — намеренный отрицательный пример: при этих данных он завершается ORA-02292. Выполните его отдельно, затем продолжите SELECT и откат. Все три занятия и обе студии остаются.

SESSION_ID STUDIO_ID
81 17
82 17
83 18

После ROLLBACK TO исходные данные восстановлены. Изменения дочерних строк входят в ту же транзакцию.

💻 Пример: составной внешний ключ

Студия может иметь несколько помещений. Пара studio_id и room_code составляет первичный ключ; дочерняя строка должна ссылаться на соответствующую пару.

CREATE TABLE demo_253_rooms (
  studio_id NUMBER(6),
  room_code VARCHAR2(10 CHAR),
  room_name VARCHAR2(60 CHAR) NOT NULL,
  CONSTRAINT demo_253_rooms_pk PRIMARY KEY (studio_id, room_code)
);
CREATE TABLE demo_253_bookings (
  booking_id NUMBER(6) PRIMARY KEY,
  studio_id NUMBER(6) NOT NULL,
  room_code VARCHAR2(10 CHAR) NOT NULL,
  booking_day DATE NOT NULL,
  CONSTRAINT demo_253_room_fk FOREIGN KEY (studio_id, room_code)
    REFERENCES demo_253_rooms(studio_id, room_code)
);
INSERT INTO demo_253_rooms VALUES (17, 'CLAY', 'Clay room');
INSERT INTO demo_253_rooms VALUES (17, 'PAPER', 'Paper room');
INSERT INTO demo_253_bookings VALUES (91, 17, 'CLAY', DATE '2026-12-05');
INSERT INTO demo_253_bookings VALUES (92, 17, 'PAPER', DATE '2026-12-12');
COMMIT;
SAVEPOINT before_253_composite;
DELETE FROM demo_253_rooms WHERE studio_id = 17 AND room_code = 'CLAY';
SELECT booking_id, studio_id, room_code FROM demo_253_bookings ORDER BY booking_id;
ROLLBACK TO before_253_composite;

DELETE снова является отрицательным примером: ORA-02292, поскольку бронь 91 ссылается на удаляемую пару. После ошибки отдельно выполните SELECT и откат:

BOOKING_ID STUDIO_ID ROOM_CODE
91 17 CLAY
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);

💻 Один столбец

Родительские таблицы уже созданы выше. Для добавления ключа создадим отдельную дочернюю таблицу без этого ограничения:

CREATE TABLE demo_253_later_sessions (
  session_id NUMBER(6) PRIMARY KEY,
  studio_id NUMBER(6) NOT NULL,
  session_day DATE NOT NULL
);
ALTER TABLE demo_253_later_sessions
  ADD CONSTRAINT demo_253_later_studio_fk FOREIGN KEY (studio_id)
  REFERENCES demo_253_studios(studio_id);
INSERT INTO demo_253_later_sessions VALUES (84, 18, DATE '2026-12-26');
COMMIT;

💻 Несколько столбцов

CREATE TABLE demo_253_later_bookings (
  booking_id NUMBER(6) PRIMARY KEY,
  studio_id NUMBER(6) NOT NULL,
  room_code VARCHAR2(10 CHAR) NOT NULL,
  booking_day DATE NOT NULL
);
ALTER TABLE demo_253_later_bookings
  ADD CONSTRAINT demo_253_later_room_fk FOREIGN KEY (studio_id, room_code)
  REFERENCES demo_253_rooms(studio_id, room_code);
INSERT INTO demo_253_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_253_LATER_STUDIO_FK', 'DEMO_253_LATER_ROOM_FK')
ORDER BY constraint_name;

Оба ограничения имеют STATUS=ENABLED, VALIDATED=VALIDATED и DELETE_RULE=NO ACTION. Добавление проверяет существующие строки; неверные ссылки нужно исправить заранее.

📝 Примечание

Без ON DELETE CASCADE или ON DELETE SET NULL родителя со связанными дочерними строками удалить нельзя. Для обычного внешнего ключа применяются PRIMARY KEY или UNIQUE родителя, соответствие числа, порядка и типов столбцов. FOREIGN KEY сам по себе не заменяет NOT NULL: при NULL в компоненте составного ключа Oracle не требует соответствующей родительской строки.

DDL фиксирует транзакцию; все опыты с DELETE завершены откатом до следующего CREATE/ALTER. Новые имена разделяют четыре примера и позволяют изучать их последовательно.

См. внешние ключи, каскадное удаление, ON DELETE SET NULL, отключение и включение ключа.

См. документацию Oracle: constraint.