Внешние ключи и создание связей между таблицами
После того, как все таблицы БД созданы, необходимо связать их между собой. Для задания связей между таблицами используются первичные и внешние ключи. Для БД «Деканат ВУЗа» связь между таблицами Students и Groups задается с помощью первичного ключа таблицы Groups и внешнего ключа таблицы Students. Первичный ключ таблицы Groups к этому времени уже должен быть создан.
Ограничение внешнего ключа говорит, что значение в колонке (или группе колонок) одной таблицы должно полностью совпадать со значениями, которые существуют в некоторых строках другой таблицы. Для примера: у таблицы Students значения в колонке GrNo должны совпадать с соответствующими значениями в колонке GrNo таблицы Groups.
Чтобы создать внешний ключ нужно выбрать в окне Браузер объектов требуемую таблицу, например Students, активизировать ее контекстное меню и в нем выбрать пункт Свойства (рисунок 2.20).
Рисунок 2.20 – Контекстное меню объекта Таблица
После этого откроется окно характеристик выбранной таблицы (рисунок 2.21).
Рисунок 2.21 – Закладка Ограничения окна свойств таблицы
В нем нужно открыть закладку Ограничения, выбрать в выпадающем списке тип ограничения вторичный (внешний) ключ и нажать кнопку Добавить. В результате выполнения этих действий появится окно Новый внешний ключ (рисунок 2.22).
Рисунок 2.22 – Окно Новый внешний ключ. Закладка Свойства
Чтобы создать новый внешний ключ необходимо выполнить следующие действия:
на закладке Свойства задать имя внешнего ключа, например, GrNoFK (FK от англ. Foreign Key – внешний ключ);
выбрать из списка таблицу, на которую ссылается внешний ключ (свойство Ссылается);
отметить свойство Совпадение полное, которое говорит о том, что значения внешнего ключа должны полностью совпадать со значениями первичного ключа;
на закладке Колонки (рисунок 2.23) выбрать колонку исходной таблицы (Локальная колонка), которая определяет внешний ключ (для приведенного примера это колонка GrNo таблицы Students), и колонку, на которую ссылается внешний ключ (колонка GrNo таблицы Groups);
Рисунок 2.23 – Закладка Колонки окна Новый внешний ключ
на закладке Действие (рисунок 2.24) выбрать действие, которое будет выполняться при изменении или удалении значения первичного ключа.
Рисунок 2.24 – Закладка Действие окна Новый внешний ключ
Возможные варианты действий:
NO ACTION – никаких дополнительных действий и ограничений;
RESTRICT – удаление/изменение значения первичного ключа запрещается, если на него ссылается какой-либо внешний ключ. Это действие установлено по умолчанию;
CASCADE – удаление строки в родительской таблице приводит к удалению всех связанных с ней строк дочерней таблицы (например, если удалить запись о группе в таблице Groups, то автоматически будут удалены все записи о ее студентах в таблице Students); изменение значения первичного ключа родительской таблицы приводит к соответствующему изменению значений внешних ключей дочерней таблицы (например, если изменить какое-либо значение в колонке GrNo таблицы Groups, то автоматически будут изменены связанные с ним значения колонки GrNo в таблице Students);
SET NULL – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение NULL всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы (например, если удалить/изменить значение в колонке GrNo таблицы Groups, то связанные с ним значения колонки GrNo в таблице Students автоматически будут установлены в NULL);
SET DEFAULT – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение по умолчанию всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы.
Чтобы увидеть результат изменений колонки, таблицы или БД необходимо выделить соответствующий объект в окне Браузер объектов и выполнить его обновление (рисунок 2.25).
Как связать таблицы в postgresql
Для связи между таблицами применяются внешние ключи. Внешний ключ устанавливается для столбца из зависимой, подчиненной таблицы (referencing table), и указывает на один из столбцов из главной таблицы (referenced table). Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне столбца:
Чтобы установить связь между таблицами, после ключевого слова REFERENCES указывается имя связанной таблицы и далее в скобках имя столбца из этой таблицы, на который будет указывать внешний ключ. После выражения REFERENCES может идти выражение ON DELETE и ON UPDATE , которые уточняют поведение при удалении или обновлении данных.
Общий синтаксис установки внешнего ключа на уровне таблицы:
Например, определим две таблицы и свяжем их посредством внешнего ключа:
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. Для установки подобного действия можно использовать следующие опции:
CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
RESTRICT : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
NO ACTION : действие по умолчанию, предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. И генерирует ошибку. В отличие от RESTRICT выполняет отложенную проверку на связанность между таблицами.
SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление
По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE :
Аналогично работает выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
Установка значения по умолчанию
Если для столца значение по умолчанию не задано через параметр DEFAULT, то в качестве такового используется значение NULL (если столбец допускает NULL).
Relationships
There are 3 types of table relationships in a relational database. The relationships can be enforced by defining the right foreign key constraints on the columns.
One-to-One and One-to-Many
A one-to-one relationship between two tables can be established via a unique foreign key constraint.
Say we have the following two tables in our database schema:
These two tables are related via
- one-to-one relationship. An article can have one author.
- one-to-many relationship. An author can have multiple articles.
Many-to-Many
A many to many relationship is typically created using a join table.
Consider the following two tables article and tag .
In practical use cases, each article can have multiple tags and each tag can be mapped to multiple articles.
Now let us create a bridge table called article_tag .
We have applied two foreign key constraints, one for article_id and one for tag_id. The table article_tag is the bridge table involved in the many-to-many relationship.
GraphQL Example
Check out the Postgres schema in GraphQL using our example running in Hasura Cloud.
PostgreSQL Foreign Key syntax
I have 2 tables as you will see in my PosgreSQL code below. The first table students has 2 columns, one for student_name and the other student_id which is the Primary Key.
In my second table called tests, this has 4 columns, one for subject_id , one for the subject_name , then one for a student with the highest score in a subject which is highestStudent_id . am trying to make highestStudent_id refer to student_id in my students table. This is the code I have below, am not sure if the syntax is correct:
is the syntax highestStudent_id SERIAL REFERENCES students correct? because i have seen another one like highestStudent_id REFERENCES students(student_id))
What would be the correct way of creating the foreign key in PostgreSQL please?
2 Answers 2
Assuming this table:
There are four different ways to define a foreign key (when dealing with a single column PK) and they all lead to the same foreign key constraint:
Inline without mentioning the target column:
Inline with mentioning the target column:
Out of line inside the create table :
As a separate alter table statement:
Which one you prefer is a matter of taste. But you should be consistent in your scripts. The last two statements are the only option if you have foreign keys referencing a PK that consists of more than one column — you can’t define the FK «inline» in that case, e.g. foreign key (a,b) references foo (x,y)
Only version 3) and 4) will give you the ability to define your own name for the FK constraint if you don’t like the system generated ones from Postgres.
The serial data type is not really a data type. It’s just a short hand notation that defines a default value for the column taken from a sequence. So any column referencing a column defined as serial must be defined using the appropriate base type integer (or bigint for bigserial columns)