Связи таблиц SQL

Когда речь заходит о реляционных базах данных, читатели представляют себе множество таблиц переплетенных связями и зависимостями. Так оно, по сути, и есть. База данных не может состоять из отдельных таблиц без какой либо связи между собой. Сегодня мы узнаем: какие существуют связи в мире реляционных баз данных, научимся из различать и программировать.

После того как мы научились создавать и работать с таблицами, необходимо понять: как связывать их между собой. Ведь по отдельности они почти не несут полезной информации.

Существуют такие типы связей между таблицами в SQL:

Мы продолжим работать с реляционной моделью магазин обуви, которую создали в рамках цикла статей по SQL. Для тех, кто тут впервые я напомню, что у нас для примера очень упрощенная и примитивная предметная область, по которой мы учим SQL. Вот ее модель:

усложненная модель

Для полного понимания темы советую ознакомиться с темой ключи sql. Без знания ключей понять примеры будет сложно.

Связь один ко многим (one-to-many)

Когда мы говорим о связи один ко многим то это означает: что в одной записи в таблице может отвечать множество записей другой таблицы. Например у одного поставщика может быть много обуви. Когда мы создавали таблицу shoes (обувь), то указывали поставщика именно в связи один ко многим:

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, 
foreign key (supplier) references supplier (supplier_id));

Чтобы еще раз продемонстрировать Вам связь один ко многим, предположим, что поле size подразумевает нечто больше чем просто число о размере обуви. Ведь если магазин будет работать по всему миру, то единого стандарта размеров не будет. Пусть size будет ссылкой на отдельную таблицу, которую так и назовем: shoes_size. В ней для примера будут поля: size_id, european_size, american_size. Модифицируем таблицу согласно обновленных условий:

create table shoes_size(size_id int auto_increment primary key, european_size int, american_size int);
alter table shoes add foreign key (size) references shoes_size(size_id);

добавление внешнего ключа

Теперь, у нас таблица shoes ссылается на таблицы supplier и size по связи многие к одному: много обуви может иметь одного поставщика и один размер. И наоборот: один поставщик может иметь много обуви; один размер может быть у большого количества обуви.

Один к одному (one-to-one)

Связь один к одному это когда одной записи в таблице отвечает только одна запись из другой таблицы. Чтобы продемонстрировать связь один к одному возьмем таблицу supplier:

supplier table

и вынесем колонку full_address в отдельную таблицу. Таким образом в таблице supplier будет ссылка на таблицу address, в которой будут такие поля: address_id, coutry, city, street.

Не теряя времени сделаем нужные изменения.

alter table supplier modify column full_address int;

Так как поле full_address будет ссылкой на таблицу address сделаем его тип целочисленным.

Далее создадим саму таблицу address.

create table address(address_id int auto_increment primary key, country text, city text, street text);

Теперь укажем, что поле full_address будет внешним ключом:

alter table supplier add foreign key (full_address) references address(address_id);

Связь один к одному означает, что в таблице supplier будет уникальный идентификатор записи с таблицы address. Поэтому, нужно сделать поле full_address уникальным:

alter table supplier add foreign key (full_address) references address(address_id);

Теперь, каждый адрес будет относиться только к одному поставщику и все попытки добавить поставщику адрес другого поставщика будет приводить к ошибке.

describe table supplier

Многие ко многим (many-to-many)

Связь многие ко многим подразумевает, что записи в одной таблице могут иметь множество ссылок на другую таблицу и наоборот. Когда есть такой тип связи нужно создавать дополнительную таблицу, которая сведет связь многие ко многим до связи один ко многим.

Для примера предположим, обувь может быть нескольких типов (кроссовки-кеды или туфли-мокасины). Может пример и не самый удачный, однако для тренировочных целей будет то что нужно.

shoe describe

 

Для тех, кто все еще не понял: мы хотим чтобы поле type в таблице shoes было ссылкой на некоторую таблицу. Притом не просто ссылкой, а несколькими ссылками. На первый взгляд кажется что это не реально. Пример все пояснит.

Создаем таблицу type с полями: type_id, name:

create table type(type_id int auto_increment primary key, name text);

Теперь удаляем поле type с таблицы shoes:

alter table shoes drop type;

Далее создаем таблицу shoes_type с полями type_id, shoes_id, который будут ссылками на таблицы type и shoes соответственно.

create table shoes_type(type_id int, shoes_id int, foreign key (type_id) references type(type_id), foreign key (shoes_id) references shoes(shoes_id));

many to many relation

Вот так просто можно смоделировать отношение многие ко многим.

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

На этом пока все. Следите за новыми уроками по SQL, не забывайте учить Java и комбинируйте все это в многослойных веб приложениях.

Понравилась статья? Поделиться с друзьями:
Комментарии: 6
  1. Павел

    Спасибо за Ваш труд, очень классное описание пошаговое по SQL. Без воды и всякой липы.

    Остановился после того, как увидел у Вас, что после текста:
    «Связь один к одному означает, что в таблице supplier будет уникальный идентификатор записи с таблицы address. Поэтому, нужно сделать поле full_address уникальным»
    Идет не та строка, которую нужно выполнить, чтобы сделать уникальным значение в таблице supplier.

    Я сделал этот пункт следующим образом:
    alter table supplier modify column full_adress int not null unique;

    Правильно так?

    1. Denys (автор)

      Спасибо за замечание. Вы все верно сделали. Можно было еще сделать поле первичным ключем. Это как вариант.

      1. Павел

        Спасибо за ответ. Очень много полезных статей, спасибо Вам за труд. Читаю и читаю :)
        В нашем случае получается, что unique — самый подходящий вариант, потому как первичный ключ подразумевает, что в будущем эта колонка будет использоваться другой таблицей с помощью внешнего ключа — правильно ли я понимаю?

        1. Denys (автор)

          В принципе, unique можно здесь и не делать если представить например, что 2 фирмы имеют одинаковый адрес. Но все же Вы правы: unique будет здесь наиболее уместно. Поскольку первичный ключ может быть только один, то если делать address_id уникальным с помощью ключа — нужно составлять тогда композитный ключ. Так как у нас первичным ключом выступает айди. Поэтому да: unique здесь наиболее уместно

  2. Павел

    Спасибо.

  3. Валерка

    Совет, в конце статьи сделайте типа «нашли ошибку выделите их Ctrl+Shift (или как там)» а то у вас много орфографических ошибок.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: