Излюбленные вопросы по SQL на собеседовании. Связи. Один ко многим.

Home » postgres » Излюбленные вопросы по SQL на собеседовании. Связи. Один ко многим.
postgres, SQL, Uncategorized Комментариев нет

Задачка по реализации связи один-ко-многим между таблицами.(Тема: один-ко-многим.)

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

Есть две таблицы: таблица Department содержит поля – id, name, таблица Employee, содержит поля id, name, salary. Каждое подразделение, имеет несколько работников.

(внимание! если вы рисуете таблички перед простатником вогоном руководителем собеседования – не рисуйте столбцы-столбцами, рисуйте именно схемы(ER-диаграмы), в которых таблица представляет из себя блок, где столбцы перечислены в несколько строк под названием таблицы, таким образом как на картинке снизу(если вы нарисуете иначе, некоторые, особенно одаренные работодатели могут решить, что вы никогда не видели схем данных базы данных, даже если вы справитесь с остальными вопросами))
260313011

Теория:

В каком-нибудь редакторе баз данных, где есть возможность визуального отображения базы данных зачастую есть возможность “протянуть” связи между таблицами вручную, запрос по созданию связи с внешним ключом сгенерируется автоматически. Однако, неплохо бы знать теориию,- бекграунд этой операции. За создание связи на основе внешнего ключа используется запрос с синтаксисом*

например, для нашего случая мы должны
1) создать столбец с внешним ключом для таблицы Employee, назовем его departmentId.
2) объявить departmentId внешним ключом, связанным с таблицей Department, столбцом id, через запрос

Теперь при изменении таблицы Department, таблица Employee также претерпит изменения. Удалив что-нибудь в Department, мы каскадно удалим записи и в Employee

(следует упомянуть, что для создания ограничения по внешнему ключу, необходимо создать ограничение на уникальность столбцов таблицы Employee, в нашем случае запрос будет выглядеть как –

) если же попробовать создать внешний ключ, ссылающийся на не уникальный ключ, мы получим ошибку – “error: there is no unique constraint matching given keys for referenced table[…]”

Расшифруем запрос:
ALTER TABLE Employee ADD CONSTRAINT fk_department – изменить таблицу Employee , добавить ограничение fk_department, такое что
FOREIGN KEY departmentId REFERENCES Department(id) – внешний ключ departmentId связывает с таблицей Department столбцом id
ON DELETE CASCADE – удаляя записи из Department мы удаляем записи из Employee
ON UPDATE CASCADE – изменяя Department мы изменяем Employee

(внимание! SQLite БД не поддерживает добавление ограничений к уже существующим таблицам)

Полный код примера**:

Примечания

*Тема связи таблиц затрагивает тему “ссылочной целостности”. Данное понятие подразумевает, что в реляционной базе данных записи из одной таблицы не могут ссылаться на несуществующие записи из другой таблицы. Нарушение этого принципа может привести к ошибкам.
Подробнее о ссылочной целостности можно узнать тут.
**В PostgreSQL аналогом AUTO_INCREMENT является служебное слово SERIAL
строки:

эквивалентны строкам:

LEAVE A COMMENT