MySQL FOREIGN KEY зовнішні ключі


Зовнішні ключі дозволяють встановити зв'язки між таблицями. Зовнішній ключ встановлюється для стовпців з залежною, підлеглою таблицею, і вказує на один із стовпців з головної таблиці. Як правило, зовнішній ключ вказує на первинний ключ з пов'язаною головною таблицею.

Загальний синтаксис установки зовнішнього ключа на рівні таблиці:

[CONSTRAINT імя_обмежувача]
FOREIGN KEY (стовпчик1, стовпчик2, ... стовпчикN) 
REFERENCES головна_таблиця (стовпчик_головної_таблиці1, стовпчик_головної_таблиці2, ... стовпчик_головної_таблиціN)
[ON DELETE дія]
[ON UPDATE дія]

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

Наприклад, визначимо дві таблиці і зв'яжемо їх за допомогою зовнішнього ключа:

CREATE TABLE Customers
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Age INT, 
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    Phone VARCHAR(20) NOT NULL UNIQUE
);
 
CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

В даному випадку визначені таблиці Customers і Orders . Customers є головною і представляє клієнта. Orders є залежною і являє замовлення, зроблене клієнтом. Таблиця Orders через стовпець CustomerId пов'язана з таблицею Customers і її стовпцем Id . Тобто стовпець CustomerId є зовнішнім ключем, який вказує на стовпець Id з таблиці Customers .

За допомогою оператора CONSTRAINT можна задати ім'я для обмеження зовнішнього ключа:

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    CONSTRAINT orders_custonmers_fk 
    FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

ON DELETE і ON UPDATE

За допомогою виразів ON DELETE і ON UPDATE можна встановити дії, які виконуються відповідно при видаленні і зміні пов'язаної рядки з головної таблиці. Як дії можуть використовуватися такі опції:

  • CASCADE : автоматично видаляє або змінює рядки з залежною таблиці при видаленні або зміні пов'язаних рядків в головній таблиці.

  • SET NULL : при видаленні або оновленні пов'язаної рядки з головної таблиці встановлює для стовпця зовнішнього ключа значення NULL . (В цьому випадку стовпець зовнішнього ключа повинен підтримувати установку NULL )

  • RESTRICT : відхиляє видалення або зміна рядків в головній таблиці при наявності пов'язаних рядків у залежною таблиці.

  • NO ACTION : те ж саме, що і RESTRICT .

  • SET DEFAULT : при видаленні пов'язаного рядка з головної таблиці встановлює для стовпця зовнішнього ключа значення за замовчуванням, яке задається за допомогою атрибути DEFAULT . Незважаючи на те, що дана опція в принципі доступна, проте движок InnoDB не підтримує цей вислів.


Каскадне видалення

Каскадне видалення дозволяє при видаленні рядка з головної таблиці автоматично видалити всі пов'язані рядки з залежною таблиці. Для цього застосовується опція CASCADE :

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);

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


Установка NULL

При установки для зовнішнього ключа опції SET NULL необхідно, щоб стовпець зовнішнього ключа допускав значення NULL :

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL
);

 


Наш партнер:
beta test mp3 playlist downloader