Внешние ключи MySQL (примеры)

Программирование Базы данных Внешние ключи MySQL (примеры)

В этой теме 0 ответов, 1 участник, последнее обновление  Васильев Владимир Сергеевич 1 месяц назад.

  • Автор
    Сообщения
  • #5422
    @admin

    Внешние ключи регламентируют отношения между таблицами. Благодаря им сильно упрощается контроль за структурой базы, уменьшается и упрощается код приложения, потому что большая часть ответственности за это мы перекладываем со своих плеч на саму базу данных. Правильно настроенные внешние ключи — это гарант того, что увеличится целостность данных за счет уменьшения избыточности.

    В двух словах — во внешних ключах много плюсов, поэтому полезно знать, как ими оперировать.

    Пример ManyToOne и OneToMany

    Дано: Две таблицы: goods(id, name, catalog_id) и catalogs(id, name). В одном каталоге множество товаров (OneToMany), и сколько угодно товаров может быть в одном каталоге (ManyToOne). Внешним ключом в этом примере является поле catalog_id в таблице goods.

    Создать таблицы можно следующими запросами:

    CREATE TABLE mybd.goods ( 
      id INT NOT NULL AUTO_INCREMENT , 
      name VARCHAR(255) NOT NULL , 
      catalog_id INT NOT NULL , 
      PRIMARY KEY (id), 
      INDEX (catalog_id)
    );
     
    CREATE TABLE mybd.catalogs ( 
      id INT NOT NULL AUTO_INCREMENT , 
      name VARCHAR(255) NOT NULL , 
      PRIMARY KEY (id)
    );

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

    ALTER TABLE goods ADD FOREIGN KEY (catalog_id) 
    REFERENCES mybd.catalogs(id) ON DELETE CASCADE ON UPDATE RESTRICT;

    Значения ON DELETE и ON UPDATE:

    • CASCADE — Каскадное удаление и редактирование. Эта настройка означает, что при удалении каталога, все товары из него тоже удалятся. При редактировании, если мы изменим id каталога, у товаров автоматически изменится поле «catalog_id».
    • RESTRICT — При этой настройке, если мы попытаемся удалить каталог, в котором есть товары, или изменить его id, база данных выдаст нам ошибку и удаление не состоится.
    • SET NULL — Из названия видно, что если исчезнет(удалится или изменится) каталог с таким id, то у товаров в поле «catalog_id» установится значение NULL. С этой настройкой нужно вести себя осторожно, потому что по умолчанию индексы «NOT NULL».
    • NO ACTION — Игнорируем удаление и редактирование каталога, и пусть в поле «catalog_id» будет несуществующий идентификатор, просто игнорируем это.

    Пример ManyToMany

    Случай посложнее, в нем, чтобы не дублировать записи, создают отдельную таблицу связей, которая обычно состоит из двух полей. Рассмотрим пример: authors(id, name), books(id, name), author_book(author_id, book_id). Книга может быть написана соавторами, и у автора может быть множество книг — отличный пример связи ManyToMany.

    Создание промежуточной таблицы связей:

    CREATE TABLE library.author_book ( 
      author_id INT NOT NULL , 
      book_id INT NOT NULL , 
      INDEX (author_id, book_id)
    );

    author_id и book_id вместе являются составным индексом, осталось только добавить ограничения на каждый из них (см. выше).

Для ответа в этой теме необходимо авторизоваться.