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

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

Для связи между таблицами применяются внешние ключи. Внешний ключ устанавливается для столбца из зависимой, подчиненной таблицы (referencing table), и указывает на один из столбцов из главной таблицы (referenced table). Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.

Общий синтаксис установки внешнего ключа на уровне столбца:

REFERENCES главная_таблица (столбец_главной_таблицы) [ON DELETE ] [ON UPDATE ]

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

FOREIGN KEY (стобец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE ] [ON UPDATE ]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, Age INTEGER, FirstName VARCHAR(20) NOT NULL ); CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, CustomerId INTEGER REFERENCES Customers (Id), Quantity INTEGER );

Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:

CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, Age INTEGER, FirstName VARCHAR(20) NOT NULL ); CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, CustomerId INTEGER, Quantity INTEGER, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. Для установки подобного действия можно использовать следующие опции:

• CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
• RESTRICT : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
• NO ACTION : действие по умолчанию, предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. И генерирует ошибку. В отличие от RESTRICT выполняет отложенную проверку на связанность между таблицами.
• SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
• SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.

Каскадное удаление

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

CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, CustomerId INTEGER, Quantity INTEGER, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE );

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, CustomerId INTEGER, Quantity INTEGER, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

Установка значения по умолчанию

CREATE TABLE Orders ( Id SERIAL PRIMARY KEY, CustomerId INTEGER DEFAULT 1, Quantity INTEGER, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT );

Если для столца значение по умолчанию не задано через параметр DEFAULT, то в качестве такового используется значение NULL (если столбец допускает NULL).

Связывание таблиц в Postgresql

Помогите разобраться в связывании таблиц в Postgresql (один ко многим). Есть несколько помещений, в каждом из которых установлен электросчетчик. Необходимо таблицу с помещениями связать с таблицей, где находятся переданные показания всех счетчиков (передаются ежемесячно на протяжении длительного времени). Связывыю внешним ключом meter из таблицы places с полем meter_number в таблице meters . а) таблица с помещениями:

CREATE TABLE places ( id integer PRIMARY KEY, name text, meter integer NOT NULL ); 

б) таблица с показаниями счетчиков:

CREATE TABLE meters ( id integer PRIMARY KEY, meter_number integer NOT NULL, data integer NOT NULL, UNIQUE (id, meter_number) ); 

Внешний ключ будет ссылаться на поле meter_number , но это поле не уникально (т.к. в таблице meters показания по каждому счётчику за большой интервал времени), поэтому делаю уникальность для группы столбцов ( id и meter_number ). в) делаю связь между таблицами:

ALTER TABLE places ADD CONSTRAINT meters FOREIGN KEY (meter) REFERENCES meters (meter_number) ON UPDATE CASCADE ON DELETE CASCADE; 

Получаю ошибку: «ERROR: there is no unique constraint matching given keys for referenced table «meters» SQL-состояние: 42830» Подскажите, что делаю не так? Может вообще нужно было делать таблицу с показаниями счётчиков в другом виде (один счётчик – одна таблица)? Но вроде и так должно работать, просто у меня пока не получается.

• postgresql
• связывание-данных

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

До этого все наши запросы обращались только к одной таблице. Однако запросы могут также обращаться сразу к нескольким таблицам или обращаться к той же таблице так, что одновременно будут обрабатываться разные наборы её строк. Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, для которых эти значения совпадают.

Примечание

Это не совсем точная модель. Обычно соединения выполняются эффективнее (сравниваются не все возможные пары строк), но это скрыто от пользователя.

Это можно сделать с помощью следующего запроса:

SELECT * FROM weather, cities WHERE city = name;

city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (2 rows)

Обратите внимание на две особенности полученных данных:

В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице cities нет строки для данного города, а при соединении все строки таблицы weather , для которых не нашлось соответствие, опускаются. Вскоре мы увидим, как это можно исправить.

Название города оказалось в двух столбцах. Это правильно и объясняется тем, что столбцы таблиц weather и cities были объединены. Хотя на практике это нежелательно, поэтому лучше перечислить нужные столбцы явно, а не использовать * :

SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

Упражнение: Попробуйте определить, что будет делать этот запрос без предложения WHERE .

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

SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city;

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

Запросы соединения, которые вы видели до этого, можно также записать в другом виде:

SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);

Эта запись не так распространена, как первый вариант, но мы показываем её, чтобы вам было проще понять следующие темы.

Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой её строки нашёл соответствующую строку в таблице cities . Если же такая строка не будет найдена, мы хотим, чтобы вместо значений столбцов из таблицы cities были подставлены « пустые значения » . Запросы такого типа называются внешними соединениями. (Соединения, которые мы видели до этого, называются внутренними.) Эта команда будет выглядеть так:

SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- Hayward | 37| 54| | 1994-11-29| | San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (3 rows)

Этот запрос называется левым внешним соединением, потому что из таблицы в левой части оператора будут выбраны все строки, а из таблицы справа только те, которые удалось сопоставить каким-нибудь строкам из левой. При выводе строк левой таблицы, для которых не удалось найти соответствия в правой, вместо столбцов правой таблицы подставляются пустые значения (NULL).

Упражнение: Существуют также правые внешние соединения и полные внешние соединения. Попробуйте выяснить, что они собой представляют.

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

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi >W2.temp_hi; city | low | high | city | low | high ---------------+-----+------+---------------+-----+------ San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)

Здесь мы ввели новые обозначения таблицы weather: W1 и W2 , чтобы можно было различить левую и правую стороны соединения. Вы можете использовать подобные псевдонимы и в других запросах для сокращения:

SELECT * FROM weather w, cities c WHERE w.city = c.name;

Вы будете встречать сокращения такого рода довольно часто.

Пред.	Наверх	След.
2.5. Выполнение запроса	Начало	2.7. Агрегатные функции

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

Начнем с создания таблицы классов ( classrooms ). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса ( query tool ) и запустите ( run или F5 ).

DROP TABLE IF EXISTS classrooms CASCADE; CREATE TABLE classrooms ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, teacher VARCHAR(100) ); 

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms , если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение ( constraint ) в конце строки добавлен оператор CASCADE . CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом ( primary key ), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY , этот приём является альтернативой синтаксису последовательности ( CREATE SEQUENCE ). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу ( alter table ).

Теперь давайте создадим таблицу учеников ( students ). Новая таблица будет содержать: уникальный идентификатор ( id ), имя ученика ( name ), и внешний ключ ( foreign key ), который будет указывать ( references ) на таблицу классов.

DROP TABLE IF EXISTS students CASCADE; CREATE TABLE students ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), classroom_id INT, CONSTRAINT fk_classrooms FOREIGN KEY(classroom_id) REFERENCES classrooms(id) ); 

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id , которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса ( classroom_id ), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов ( classrooms ).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников ( students ). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников ( students ), в которой указан идентификатор класса ( classroom_id ), не существующий в таблице classrooms . Например: у нас в таблице классов 10 записей ( id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с >

INSERT INTO students (name, classroom_id) VALUES ('Matt', 1); /* ERROR: insert or update on table "students" violates foreign key constraint "fk_classrooms" DETAIL: Key (classroom_id)=(1) is not present in table "classrooms". SQL state: 23503 */ 

Теперь давайте добавим немного данных в таблицу классов ( classrooms ). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

INSERT INTO classrooms (teacher) VALUES ('Mary'), ('Jonah'); SELECT * FROM classrooms; /* id | teacher -- | ------- 1 | Mary 2 | Jonah */ 

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

INSERT INTO students (name, classroom_id) VALUES ('Adam', 1), ('Betty', 1), ('Caroline', 2); SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 1 | Adam | 1 2 | Betty | 1 3 | Caroline | 2 */ 

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса ( classroom_id ) передать null . Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

-- явно определим значение NULL INSERT INTO students (name, classroom_id) VALUES ('Dina', NULL); -- неявно определим значение NULL INSERT INTO students (name) VALUES ('Evan'); SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 1 | Adam | 1 2 | Betty | 1 3 | Caroline | 2 4 | Dina | [null] 5 | Evan | [null] */ 

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий ( assignments ), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок ( grades ), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

DROP TABLE IF EXISTS assignments CASCADE; DROP TABLE IF EXISTS grades CASCADE; CREATE TABLE assignments ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, category VARCHAR(20), name VARCHAR(200), due_date DATE, weight FLOAT ); CREATE TABLE grades ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, assignment_id INT, score INT, student_id INT, CONSTRAINT fk_assignments FOREIGN KEY(assignment_id) REFERENCES assignments(id), CONSTRAINT fk_students FOREIGN KEY(student_id) REFERENCES students(id) ); 

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data ).

COPY assignments(category, name, due_date, weight) FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv' DELIMITER ',' CSV HEADER; /* COPY 5 Query returned successfully in 118 msec. */ COPY grades(assignment_id, score, student_id) FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv' DELIMITER ',' CSV HEADER; /* COPY 25 Query returned successfully in 64 msec. */ 

Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.

SELECT c.teacher, a.category, ROUND(AVG(g.score), 1) AS avg_score FROM students AS s INNER JOIN classrooms AS c ON c.id = s.classroom_id INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id GROUP BY 1, 2 ORDER BY 3 DESC; /* teacher | category | avg_score ------- | --------- | --------- Jonah | project | 100.0 Jonah | homework | 94.0 Jonah | exam | 92.5 Mary | homework | 78.3 Mary | exam | 76.0 Mary | project | 69.5 */ 

Отлично! Мы установили, настроили и наполнили базу данных.

Итак, в этой статье мы научились:

• создавать базу данных;
• создавать таблицы;
• наполнять таблицы данными;
• устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц ( JOIN ) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

• виды фильтраций в запросах;
• запросы с условиями типа if-else;
• новые виды соединений таблиц;
• функции для работы с массивами;

Материалы по теме

• �� 8 лучших GUI клиентов PostgreSQL в 2021 году
• ���� Python и MySQL: практическое введение
• ����️ Управление данными с помощью Python, SQLite и SQLAlchemy