66. Зв'язані таблиці. Зовнішні ключі FOREIGN KEY

Створимо в базі даних дві зв'язані таблиці. В першій буде список країн, а в другій - список грошових одиниць. Зв'яжемо країни з відповідними грошовими одиницями.

<?php
 $sql = "
  CREATE TABLE IF NOT EXISTS country_m
 (
    id INT (2) PRIMARY KEY AUTO_INCREMENT,
    country VARCHAR(20) NOT NULL
  
 )";
  $pdo->exec($sql);  
        echo "Таблиця `country_m` готова до використання";
        
  $sql="       
 CREATE TABLE IF NOT EXISTS money
 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    money VARCHAR(20) NOT NULL,
    country_id INT(2),
    // встановлюємо назву ключа (не обов'язково) 
    CONSTRAINT my_foreign_key
    FOREIGN KEY (country_id)  REFERENCES country_m (id) ON DELETE CASCADE
 )";
 $pdo->exec($sql);  
        echo "Таблиця `money` готова до використання";
?>

Таблиця `country_m` готова до використання
Таблиця `money` готова до використання

Заповнимо таблиці

<?php
 $data = $pdo->query ("INSERT INTO `country_m` (`country`) 
                        VALUES ('Бразилія'),('Турція'),('Польща') ");
                        
 $data = $pdo->query ("INSERT INTO `money` (`country_id`,`money`)
                         VALUES ('1','реал'),('2','ліра'),('3','злотий') ");
?>

Виведемо результат

<?php
 /* таблиця country_m */
$sql ="SELECT * FROM country_m";  
$stmt = $pdo->query($sql);
  
  echo "<table><tr><th>id</th><th>country</th></tr>";     
   while ($row = $stmt->fetch())  
    {  
     echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";    
    }  
   echo "</table>";
   
   /* таблиця money */
 $sql ="SELECT * FROM ";  
$stmt = $pdo->query($sql);
  
  echo "<table><tr><th>id</th><th>money</th><th>country_id</th></tr>";     
   while ($row = $stmt->fetch())  
    {  
     echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td><td>".$row[2]."</td></tr>";    
    }  
   echo "</table>"; 
?>

country_m

id	country
1	Бразилія
2	Турція
3	Польща

money

id	money	country_id
1	реал	1
2	ліра	2
3	злотий	3

Обмеження ключів ON DELETE та ON UPDATE

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

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

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

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

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

За замовчуванням встановлено RESTRICT:

При створенні таблиць було зазначено, що видалення рядка у головній таблиці приведе до видалення відповідного рядка у підлеглій таблиці

 FOREIGN KEY (country_id)  REFERENCES country_m (id) ON DELETE CASCADE

Видаляємо рядок з Бразилією і побачимо чи потягне це за собою видалення відповідного рядка у другій таблиці

<?php
   // видаляємо 
    $sql = "DELETE FROM `country_m` WHERE `country` = 'Бразилія'";  
        $query = $pdo->query($sql);     
 ?>

country_m

id	country
2	Турція
3	Польща

money

id	money	country_id
2	ліра	2
3	злотий	3

Розірвання зв'язку між таблицями

// вказуємо ім'я ключа, який зазначали в CONSTRAINT 
$stmt = $pdo->query("ALTER TABLE `money` DROP FOREIGN KEY `my_foreign_key`");

далі ✏