Как связать таблицы в phpmyadmin

Обновлено: 15.04.2024

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

1.1. Для кого эта статья?

Эта статья будет полезна тем, кто хочет разобраться со связями между таблицами базы данных. В ней я постарался рассказать на понятном языке, что это такое. Для лучшего понимания темы, я чередую теоретический материал с практическими примерами, представленными в виде диаграммы и запроса, создающего нужные нам таблицы. Я использую СУБД Microsoft SQL Server и запросы пишу на T-SQL. Написанный мною код должен работать и на других СУБД, поскольку запросы являются универсальными и не используют специфических конструкций языка T-SQL.

1.2. Как вы можете применить эти знания?

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

2. Благодарности

Учтены были советы и критика авторов jobgemws, unfilled, firnind, Hamaruba.
Спасибо!

3.1. Как организовываются связи?

Связи создаются с помощью внешних ключей (foreign key).
Внешний ключ — это атрибут или набор атрибутов, которые ссылаются на primary key или unique другой таблицы. Другими словами, это что-то вроде указателя на строку другой таблицы.

3.2. Виды связей

Связи делятся на:

Многие ко многим.
Один ко многим.
- с обязательной связью;
- с необязательной связью;
Один к одному.
- с обязательной связью;
- с необязательной связью;

4. Многие ко многим

Представим, что нам нужно написать БД, которая будет хранить работником IT-компании. При этом существует некий стандартный набор должностей. При этом:

Работник может иметь одну и более должностей. Например, некий работник может быть и админом, и программистом.
Должность может «владеть» одним и более работников. Например, админами является определенный набор работников. Другими словами, к админам относятся некие работники.

4.1. Как построить такие таблицы?

Мы уже имеем две таблицы, описывающие работника и профессию. Теперь нам нужно установить между ними связь многие ко многим. Для реализации такой связи нам нужен некий посредник между таблицами «Employee» и «Position». В нашем случае это будет некая таблица «EmployeesPositions» (работники и должности). Эта таблица-посредник связывает между собой работника и должность следующим образом:

Слева указаны работники (их id), справа — должности (их id). Работники и должности на этой таблице указываются с помощью id’шников.

На эту таблицу можно посмотреть с двух сторон:

Таким образом, мы говорим, что работник с id 1 находится на должность с id 1. При этом обратите внимание на то, что в этой таблице работник с id 1 имеет две должности: 1 и 2. Т.е., каждому работнику слева соответствует некая должность справа.
Мы также можем сказать, что должности с id 3 принадлежат пользователи с id 2 и 3. Т.е., каждой роли справа принадлежит некий работник слева.

4.2. Реализация

С помощью ограничения foreign key мы можем ссылаться на primary key или unique другой таблицы. В этом примере мы

ссылаемся атрибутом PositionId таблицы EmployeesPositions на атрибут PositionId таблицы Position;
атрибутом EmployeeId таблицы EmployeesPositions — на атрибут EmployeeId таблицы Employee;

4.3. Вывод

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

5. Один ко многим

Эта самая распространенная связь между базами данных. Мы рассматриваем ее после связи многие ко многим для сравнения.

Предположим, нам нужно реализовать некую БД, которая ведет учет данных о пользователях. У пользователя есть: имя, фамилия, возраст, номера телефонов. При этом у каждого пользователя может быть от одного и больше номеров телефонов (многие номера телефонов).

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

Другими словами, телефон принадлежит только одному пользователю. А пользователю могут принадлежать 1 и более телефонов (многие).

Как мы видим, это отношение один ко многим.

5.1. Как построить такие таблицы?

Пользователей будет представлять некая таблица «Person» (id, имя, фамилия, возраст), номера телефонов будет представлять таблица «Phone». Она будет выглядеть так:

PhoneId	PersonId	PhoneNumber
1	5	11 091-10
2	5	19 124-66
3	17	21 972-02

Данная таблица представляет три номера телефона. При этом номера телефона с id 1 и 2 принадлежат пользователю с id 5. А вот номер с id 3 принадлежит пользователю с id 17.
Заметка. Если бы у таблицы «Phones» было бы больше атрибутов, то мы смело бы их добавляли в эту таблицу.

5.2. Почему мы не делаем тут таблицу-посредника?

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

Каждому работнику принадлежат несколько должностей (многие).
Каждой должности принадлежит несколько работников (многие).

5.3. Реализация

Наша таблица Phone хранит всего один внешний ключ. Он ссылается на некого пользователя (на строку из таблицы Person). Таким образом, мы как бы говорим: «этот пользователь является владельцем данного телефона». Другими словами, телефон знает id своего владельца.

6. Один к одному

Представим, что на работе вам дали задание написать БД для учета всех работников для HR. Начальник уверял, что компании нужно знать только об имени, возрасте и телефоне работника. Вы разработали такую БД и поместили в нее всю 1000 работников компании. И тут начальник говорит, что им зачем-то нужно знать о том, является ли работник инвалидом или нет. Наиболее простое, что приходит в голову — это добавить новый столбец типа bool в вашу таблицу. Но это слишком долго вписывать 1000 значений и ведь true вы будете вписывать намного реже, чем false (2% будут true, например).

Более простым решением будет создать новую таблицу, назовем ее «DisabledEmployee». Она будет выглядеть так:

Но это еще не связь один к одному. Дело в том, что в такую таблицу работник может быть вписан более одного раза, соответственно, мы получили отношение один ко многим: работник может быть несколько раз инвалидом. Нужно сделать так, чтобы работник мог быть вписан в таблицу только один раз, соответственно, мог быть инвалидом только один раз. Для этого нам нужно указать, что столбец EmployeeId может хранить только уникальные значения. Нам нужно просто наложить на столбец EmloyeeId ограничение unique. Это ограничение сообщает, что атрибут может принимать только уникальные значения.

Выполнив это мы получили связь один к одному.

Заметка. Обратите внимание на то, что мы могли также наложить на атрибут EmloyeeId ограничение primary key. Оно отличается от ограничения unique лишь тем, что не может принимать значения null.

6.1. Вывод

Можно сказать, что отношение один к одному — это разделение одной и той же таблицы на две.

6.2. Реализация

Таблица DisabledEmployee имеет атрибут EmployeeId, что является внешним ключом. Он ссылается на атрибут EmployeeId таблицы Employee. Кроме того, этот атрибут имеет ограничение unique, что говорит о том, что в него могут быть записаны только уникальные значения. Соответственно, работник может быть записан в эту таблицу не более одного раза.

7. Обязательные и необязательные связи

Связи можно поделить на обязательные и необязательные.

7.1. Один ко многим

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

А) У женщины необязательно есть свои дети. Соответственно, связь необязательна.
Б) У ребенка обязательно есть только одна биологическая мать – в таком случае, связь обязательна.

7.2. Один к одному

Один к одному с обязательной связью:
У одного гражданина определенной страны обязательно есть только один паспорт этой страны. У одного паспорта есть только один владелец.
Один к одному с необязательной связью:
У одной страны может быть только одна конституция. Одна конституция принадлежит только одной стране. Но конституция не является обязательной. У страны она может быть, а может и не быть, как, например, у Израиля и Великобритании.

У одного человека может быть только один загранпаспорт. У одного загранпаспорта есть только один владелец.

А) Наличие загранпаспорта необязательно – его может и не быть у гражданина. Это необязательная связь.
Б) У загранпаспорта обязательно есть только один владелец. В этом случае, это уже обязательная связь.

7.3. Многие ко многим

Человек может инвестировать в акции разных компаний (многих). Инвесторами какой-то компании являются определенные люди (многие).

А) Человек может вообще не инвестировать свои деньги в акции.
Б) Акции компании мог никто не купить.

8. Как читать диаграммы?

Выше я приводил диаграммы созданных нами таблиц. Но для того, чтобы их понимать, нужно знать, как их «читать». Разберемся в этом на примере диаграммы из пункта 5.3.

Мы видим отношение один ко многим. Одной персоне принадлежит много телефонов.

В этой заметке мы научимся создавать связи между таблицами в базе данных MySQL с помощью phpmyadmin. Если по какой-то причине вы не желаете использовать phpmyadmin, смотрите приведенные ниже SQL-запросы.

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

Например, можно запретить удалять категорию, если с ней связана хотя б одна заметка. Или удалить все заметки, если удалена категория. Или установить NULL в связующее поле. В любом случае, с помощью связей повышается отказоустойчивость и надежность приложения.

Для начала, движок таблиц должен быть InnoDB . Только он поддерживает внешние ключи ( foreign key ). Если у вас таблицы MyISAM , почитайте как их конвертировать в InnoDB .

Для того, чтобы связать таблицы по полям, необходимо сначала добавить в индекс связываемые поля:

В phpmyadmin выбираем таблицу, выбираем режим структуры, выделяем поле, для которого будем делать внешнюю связь и кликаем Индекс.

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

Это же действие можно сделать с помощью SQL-запроса:

Аналогично добавляем индекс (только в моем случае теперь уже уникальный или первичный) для таблицы, на которую ссылаемся, для поля id. Поскольку поле id у меня идентификатор, для него делаем первичный ключ. Уникальный ключ мог бы понадобится для других уникальных полей.

С помощью SQL-запроса:

Теперь осталось только связать таблицы. Для этого кликаем внизу на пункт Связи:

Теперь для доступных полей (а доступны только проиндексированные поля) выбираем связь с внешними таблицами и действия при изменении записей в таблицах:

Через SQL-запрос:

Далее научимся создавать связи между таблицами в базе данных MySQL с помощью phpmyadmin. Если по какой-то причине вы не желаете использовать phpmyadmin, смотрите приведенные ниже SQL-запросы.

Для того, чтобы связать таблицы по полям, необходимо сначала добавить в индекс связываемые поля:

В phpmyadmin выбираем таблицу Country , выбираем режим структуры, выделяем поле Id_Continent, для которого будем делать внешнюю связь и кликаем Индекс.

Это же действие можно сделать с помощью SQL-запроса:

Аналогично добавляем индекс для поля Id_Language таблицы Country .

Аналогично можно добавить индекс (только в моем случае теперь уже уникальный или первичный) для таблицы, на которую ссылаемся, для поля id. Поскольку поле id у меня идентификатор, для него делаем первичный ключ. Уникальный ключ мог бы понадобится для других уникальных полей. Но так как мы его уже создали, принимаем информацию к сведению и идём дальше

С помощью SQL-запроса:

Теперь осталось только связать таблицы. Для этого кликаем внизу на пункт Связи:

Помогите разобраться. Как с помощью SQL установить связь между таблицами:

один к одному;
один к многим;
много к многим;

Например есть таблица пользователь с уникальным ID
таблица 1 пользователь id primary key

есть вторая таблица с внешнем ключом на 1-ю таблицу
таблица2 направление user_id FOREIGN KEY на id 1 таблицы Должность . и т.д.
данный пример связи 1 к 1

одной записи из 1 таблицы будет соответствовать 1 запись из второй

связь 1 ко многим например нам нужно хранить какие задачи назначены на пользователе первая таблица без изменений
таблица 3 работы
work_ID primary key user_id FOREIGN KEY на id 1 таблицы
1 пользователю могут быть назначены несколько заданий

связь многие ко многим например нам нужно хранить оборудование записанное для конкретного пользователя первая таблица без изменений, добавляем две новые таблицы таблицу с перечнем оборудования
таблица 4 оборудование
device_ID primary key Name .

и таблицу со связью пользователя с оборудованием
таблица 5 Перечень оборудования
user_ID FOREIGN KEY из 1 таблицы
device_ID FOREIGN KEY из 4 таблицы

Как создать связи в sql

Для работы Node.js с базами данных SQL используется модуль sequelize.

Node.js sequelize поддерживает следующие СУБД: MySQL, PostgreSQL, MSSQL и MariaDB.

Модуль имеет единое API для всех перечисленных СУБД, поскольку все перечисленные СУБД используют единый язык описания запросов — SQL.

Подключение¶

Рассмотрим подключение к базе данных PostgreSQL.

Во всех последующих примерах будет подразумеваться, что переменная sequelize хранит соединение с БД.

За подключение отвечает класс Sequelize , при создании экземпляра которого задаются следующие параметры:

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

Через объект конфигурации можно задать множество параметров, вот лишь некоторые из них:

host — хост сервера БД (по умолчанию localhost );
port — порт сервера БД (по умолчанию порт по умолчанию выбранной СУБД);
dialect — тип используемой СУБД ( mariadb , mysql , mssql , postgres );
pool — настройка пула соединений;
scheme — используемая схема (по умолчанию null ).

Пример настройки пула соединений.

С полным перечнем задаваемых опций можно ознакомиться в документации Node.js sequelize.

Для проверки установки соединения в примере вызывается метод authenticate() , который возвращает объект Promise .

Не открывайте новое соединение пока не закроете текущее.

Модели¶

Модели используются для описания структуры таблицы. Одна модель описывает одну таблицу.

Модель создается с помощью метода init() класса, который является дочерним по отношению к Sequelize.Model . Метод init() принимает два объекта:

объект с описанием полей таблицы;
конфигурация создаваемой модели и соответствующей ей таблицы.

Описание поля задается объектом со следующими свойствами:

type — тип поля;
defaultValue — значение поля по умолчанию;
primaryKey — булевое значение, если true , то поле является первичным ключом (по умолчанию false );
autoIncrement — булевое значение, если true , то при добавлении новой записи значение поля будет значение предыдущей записи этого поля плюс единица (по умолчанию false );
allowNull — булевое значение, если false , запрещает создавать новую запись с этим пустым полем;
unique — булевое значение, если true , то значение указанное для этого поля в записи должно быть уникальным в пределах таблицы (по умолчанию false );
comment — комментарий к полю;
field — если указано, то в качестве названия поле будет использоваться именно это значение, а не ключ;
validate — объект с заданием для поля валидаторов, с полным списком можно ознакомиться в документации;
get() — функция, которая модифицирует значение поля при чтении записи;
set() — функция, преобразующая передаваемое значение при сохранении записи.

Реализация аналогичного функционала с использованием set() .

Конфигурация модели (второй параметр, передаваемый init() ) описывается следующим объектом:

modelName — имя модели;
timestamps — булевое значение, если true , то к таблице автоматически будут добавлены поля createdAt (дата и время создания записи) и updatedAt (дата и время обновления записи);
paranoid — булевое значение, если true , то вместо фактического удаления записи добавит поле deletedAt с датой и временем выполнения запроса на удаление (работает совместно с );
underscored — булевое значение, если true , то названия полей будут переименованы с использованием символа нижнего подчеркивания, например, если поле названо authorName , то в таблице оно фактически будет называться author_name (параметр не распространяется на значение поля field , указанное при описании поля таблицы);
freezeTableName — булевое значение, если true , то название таблицы будет таким, как указано в tableName (по умолчанию названия всех таблиц преобразуются в множественное число);
tableName — название таблицы;
hooks — определение триггеров (рассмотрены отдельно далее);
indexes — определение индексов (рассмотрены отдельно далее);
sequelize — экземпляр объекта активного соединения с БД.

Если описанной с помощью модели таблицы физически еще не существует, она будет создана автоматически в момент запуска приложения или вызова метода sync() в уже работающем приложении.

Создание связей¶

Создание связей между таблицами осуществляется с использованием моделей. Рассмотрим установление следующих типов связей:

один к одному;
один ко многим;
многие ко многим.

Пример установки связи один к одному.

Определение связи один к одному подразумевает, что у одного водителя имеется одна машина.

Разница между использованием belongsTo() и hasOne() в том, что в первом случае foreignKey будет добавлен в модель Car , а во втором — в модель Driver .

По умолчанию формат добавляемого foreignKey следующий: modelName + "Id" . Так, belongsTo() добавит в Car поле driverId , а hasOne() — в Driver поле carId .

Если вы хотите задать собственное наименование foreignKey или связать таблицы не по полю id , используйте следующий формат определения связи.

Значение поля sourceKey должно быть уникальным.

Теперь рассмотрим пример установки связи один ко многим. В качестве исходных моделей используем модели из примера связи один к одному.

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

Если необходимо указать пользовательские поля связывания, то придется указать полную связь.

Теперь посмотрим, как определить связь многие ко многим на примере все тех же моделей Car и Driver .

Для хранения соответствия ключей водителей и автомобилей будет создана отдельная таблица, название которой указывается в поле through объекта, передаваемого методу belongsToMany() вторым параметром.

Задание полей в создаваемой таблице можно указать через свойство foreignKey .

Пример извлечения данных из таблицы вместе с данными связанной таблице приведен далее в разделе "Получение/создание/обновление/удаление записи".

Если необходимо, чтобы модель CarDriver содержала дополнительные поля, просто заранее определите модель с этими самыми полями. Поля car_id и driver_id будут добавлены автоматически в момент создания связи.

Получение/создание/обновление/удаление записи¶

Для получения данных таблицы, применительно к соответствующей модели используйте методы findAll() и findOne() .

С полным списком поддерживаемых Node.js sequelize операторов можно ознакомиться на официальном сайте.

Создание новых записей осуществляется с помощью методов create() и bulkCreate() .

За обновление записей отвечает метод update() , который первым параметром принимает новые значения для записей, попадающих под задаваемую вторым параметром выборку.

Для удаления записей имеется метод destroy() .

Для выполнения самописных запросов без использования модели таблицы имеется метод sequelize.query() , который первым параметром принимает сам запрос в строковом виде, а вторым параметром — конфигурационный объект.

Триггеры¶

Триггеры представляют собой функции, которые выполняются (если они определены) до/после/во время действий с данными. Список самых популярных триггеров в порядке их выполнения:

beforeValidate(данные, опции) — выполняется перед валидацией;
afterValidate(данные, опции) или validationFailed(данные, опции, ошибка) — выполняется после успешной или неуспешной проверки валидации соответственно;
beforeCreate(данные, опции) — вызывается перед созданием записи;
beforeDestroy(данные, опции) — выполняется перед удалением записи;
beforeUpdate(данные, опции) — вызывается перед обновлением записи;
beforeSave(данные, опции) — вызывается перед сохранением записи;
afterCreate(данные, опции) — вызывается после создания записи;
afterDestroy(данные, опции) — выполняется после удаления записи;
afterUpdate(данные, опции) — вызывается после обновления записи;
afterSave(данные, опции) — вызывается после сохранения записи.

Определение триггеров осуществляется в модели таблицы в объекте конфигурации в свойстве hooks .

Также имеется пара триггеров beforeConnect() и afetrConnect() для подключения к БД.

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

phpMyAdmin содержит инструмент под названием Дизайнер. Этот инструмент позволяет упростить работу по созданию таблиц в базе данных и связей между таблицами.

Для начала создайте базу данных и войдите с полученными данными пользователя в phpMyAdmin.

Здравствуйте!
В интернете почитал статье и смотрел видео уроки но у меня никак не получается создать связь между таблицам
итак допустим есть два таблица
1 - post (id, status, date)
2 - post_content (id, parent_id, language, name, content)

при удаления поста нужно удалить его все контенты которые в разных языках
как можно это сделать?
у меня версия MySQL 5.7
в phpMyAdmin как сделать? или нужен запрос
по моему я не очень хорошо понял связать их

Если я правильно понял, то здесь у вас связь один-ко многим, поле `post_content`.`parent_id` указывает на `post`.`id`.
Для того, чтобы создать такую связь (внешний ключ, FOREIGN KEY) в MySQL сначала необходимо убедиться, что оба этих поля имеют одинаковый тип и размер и в колонке `post_content`.`parent_id` нет значений, отсутствующих в `post`.`id`.
Затем надо создать саму связь

Этот запрос добавляет внешний ключ `fk_parent_id` с поля `parent_id` на `post`.`id` и говорит, что при удалении записи из таблицы `post` будут удалены связанные записи из `post_content`.

спасибо большой. я точно такое делал но в место CASCADE использовал RESTRICT, поэтому у меня не получилось. просто не понимал зачем это)

есть ещё один вопрос..
есть таблица категории
id, parent_id, status
а когда удаляю категорию, можно удалить её под категории с помощью связь?

Дилик Пулатов, Удалить можно, но не более 15 уровней вложенности. И записи верхнего уровня в такой таблице должны будут иметь `parent_id` = NULL.

Читайте также: