Внешний ключ postgresql что это
Перейти к содержимому

Внешний ключ postgresql что это

  • автор:

Внешний ключ postgresql что это

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

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

5.3.1. Ограничения-проверки

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

CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );

Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK , за которым идёт выражение в скобках. Это выражение должно включать столбец, для которого задаётся ограничение, иначе оно не имеет большого смысла.

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

CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) );

То есть, чтобы создать именованное ограничение, напишите ключевое слово CONSTRAINT , а за ним идентификатор и собственно определение ограничения. (Если вы не определите имя ограничения таким образом, система выберет для него имя за вас.)

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

CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );

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

Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя Postgres Pro этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:

CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price) );
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) );

Ограничениям таблицы можно присваивать имена так же, как и ограничениям столбцов:

CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price) );

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

Примечание

Postgres Pro не поддерживает ограничения CHECK , которые обращаются к данным, не относящимся к новой или изменённой строке. Хотя ограничение CHECK , нарушающее это правило, может работать в простых случаях, в общем случае нельзя гарантировать, что база данных не придёт в состояние, когда условие ограничения окажется ложным (вследствие последующих изменений других участвующих в его вычислении строк). В результате восстановление выгруженных данных может оказаться невозможным. Во время восстановления возможен сбой, даже если полное состояние базы данных согласуется с условием ограничения, по причине того, что строки загружаются не в том порядке, в котором это условие будет соблюдаться. Поэтому для определения ограничений, затрагивающих другие строки и другие таблицы, используйте ограничения UNIQUE , EXCLUDE или FOREIGN KEY , если это возможно.

Если вам не нужна постоянно поддерживаемая гарантия целостности, а достаточно разовой проверки добавляемой строки по отношению к другим строкам, вы можете реализовать эту проверку в собственном триггере. (Этот подход исключает вышеописанные проблемы при восстановлении, так как в выгрузке pg_dump триггеры воссоздаются после перезагрузки данных, и поэтому эта проверка не будет действовать в процессе восстановления.)

Примечание

В Postgres Pro предполагается, что условия ограничений CHECK являются постоянными, то есть при одинаковых данных в строке они всегда выдают одинаковый результат. Именно этим предположением оправдывается то, что ограничения CHECK проверяются только при добавлении или изменении строк, а не при каждом обращении к ним. (Приведённое выше предупреждение о недопустимости обращений к другим таблицам является частным следствием этого предположения.)

Однако это предположение может нарушаться, как часто бывает, когда в выражении CHECK используется пользовательская функция, поведение которой впоследствии меняется. Postgres Pro не запрещает этого, и если строки в таблице перестанут удовлетворять ограничению CHECK , это останется незамеченным. В итоге при попытке загрузить выгруженные позже данные могут возникнуть проблемы. Поэтому подобные изменения рекомендуется осуществлять следующим образом: удалить ограничение (используя ALTER TABLE ), изменить определение функции, а затем пересоздать ограничение той же командой, которая при этом перепроверит все строки таблицы.

5.3.2. Ограничения NOT NULL

Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:

CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );

Ограничение NOT NULL всегда записывается как ограничение столбца и функционально эквивалентно ограничению CHECK ( имя_столбца IS NOT NULL) , но в Postgres Pro явное ограничение NOT NULL работает более эффективно. Хотя у такой записи есть недостаток — назначить имя таким ограничениям нельзя.

Естественно, для столбца можно определить больше одного ограничения. Для этого их нужно просто указать одно за другим:

CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) );

Порядок здесь не имеет значения, он не обязательно соответствует порядку проверки ограничений.

Для ограничения NOT NULL есть и обратное: ограничение NULL . Оно не означает, что столбец должен иметь только значение NULL, что конечно было бы бессмысленно. Суть же его в простом указании, что столбец может иметь значение NULL (это поведение по умолчанию). Ограничение NULL отсутствует в стандарте SQL и использовать его в переносимых приложениях не следует. (Оно было добавлено в Postgres Pro только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:

CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );

и затем вставить ключевое слово NOT , где потребуется.

Подсказка

При проектировании баз данных чаще всего большинство столбцов должны быть помечены как NOT NULL.

5.3.3. Ограничения уникальности

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

CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );

в виде ограничения столбца и так:

CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) );

в виде ограничения таблицы.

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

CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );

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

Вы можете назначить уникальному ограничению имя обычным образом:

CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric );

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

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

5.3.4. Первичные ключи

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

CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric );
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );

Первичные ключи могут включать несколько столбцов; синтаксис похож на запись ограничений уникальности:

CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );

При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL .

Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В Postgres Pro такого жёсткого требования нет, но обычно лучше ему следовать.

Первичные ключи полезны и для документирования, и для клиентских приложений. Например, графическому приложению с возможностями редактирования содержимого таблицы, вероятно, потребуется знать первичный ключ таблицы, чтобы однозначно идентифицировать её строки. Первичные ключи находят и другое применение в СУБД; в частности, первичный ключ в таблице определяет целевые столбцы по умолчанию для сторонних ключей, ссылающихся на эту таблицу.

5.3.5. Внешние ключи

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

Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );

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

CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer );

С таким ограничением создать заказ со значением product_no , отсутствующим в таблице products (и не равным NULL), будет невозможно.

В такой схеме таблицу orders называют подчинённой таблицей, а products — главной. Соответственно, столбцы называют так же подчинённым и главным (или ссылающимся и целевым).

Предыдущую команду можно сократить так:

CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products, quantity integer );

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

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

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

CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );

Естественно, число и типы столбцов в ограничении должны соответствовать числу и типам целевых столбцов.

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

CREATE TABLE tree ( node_id integer PRIMARY KEY, parent_id integer REFERENCES tree, name text, . );

Для узла верхнего уровня parent_id будет равен NULL, но записи с отличным от NULL parent_id будут ссылаться только на существующие строки таблицы.

Таблица может содержать несколько ограничений внешнего ключа. Это полезно для связи таблиц в отношении многие-ко-многим. Скажем, у вас есть таблицы продуктов и заказов, но вы хотите, чтобы один заказ мог содержать несколько продуктов (что невозможно в предыдущей схеме). Для этого вы можете использовать такую схему:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );

Заметьте, что в последней таблице первичный ключ покрывает внешние ключи.

Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к одному продукту. Но что делать, если после создания заказов с определённым продуктом мы захотим удалить его? SQL справится с этой ситуацией. Интуиция подсказывает следующие варианты поведения:

Запретить удаление продукта

Удалить также связанные заказы

Для иллюстрации давайте реализуем следующее поведение в вышеприведённом примере: при попытке удаления продукта, на который ссылаются заказы (через таблицу order_items ), мы запрещаем эту операцию. Если же кто-то попытается удалить заказ, то удалится и его содержимое:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );

Ограничивающие и каскадные удаления — два наиболее распространённых варианта. RESTRICT предотвращает удаление связанной строки. NO ACTION означает, что если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка (это поведение по умолчанию). (Главным отличием этих двух вариантов является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT — нет.) CASCADE указывает, что при удалении связанных строк зависимые от них будут так же автоматически удалены. Есть ещё два варианта: SET NULL и SET DEFAULT . При удалении связанных строк они назначают зависимым столбцам в подчинённой таблице значения NULL или значения по умолчанию, соответственно. Заметьте, что это не будет основанием для нарушения ограничений. Например, если в качестве действия задано SET DEFAULT , но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция закончится ошибкой.

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

Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если один из связанных столбцов содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL , строка будет удовлетворять ограничению, только если все связанные столбцы равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL ). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные столбцы как NOT NULL .

Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных столбцов всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно. Так как команды DELETE для строк главной таблицы или UPDATE для зависимых столбцов потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых столбцов. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным столбцам.

Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE .

5.3.6. Ограничения-исключения

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

CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );

При добавлении ограничения-исключения будет автоматически создан индекс того типа, который указан в объявлении ограничения.

Пред. Начало След.
5.2. Значения по умолчанию Наверх 5.4. Системные столбцы

PostgreSQL Antipatterns: когда мешает внешний ключ

Внешние ключи (foreign keys) — мощный и удобный механизм контроля логической целостности данных в базе. Но он бывает не только лишь полезен, и может неплохо пригрузить вашу БД.

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

Традиционно, начнем с самой простой ситуации — пара табличек, одна на другую ссылается:

CREATE TABLE tblpk( k integer PRIMARY KEY ); CREATE TABLE tblfk( k integer REFERENCES tblpk -- эквивалентно tblpk(k), поскольку k - PK ON DELETE CASCADE -- "ничейные" записи нам не нужны , v integer );

Наполним их некоторыми данными:

INSERT INTO tblpk(k) SELECT generate_series(1, 1e3); -- [1..1000] INSERT INTO tblfk(k, v) SELECT (random() * (1e3 - 1))::integer + 1 -- random = [0..1] , (random() * 1e6)::integer FROM generate_series(1, 1e6);

Медленный SELECT

А теперь попробуем самым примитивным запросом, через JOIN , для каждой записи tblpk из первого десятка найти максимальное значение tblfk.v :

SELECT k , max(v) FROM tblpk JOIN tblfk USING(k) WHERE k 

И. Parallel Seq Scan по миллиону записей tblfk - это совсем не то, чего бы хотелось:

Вычитываем миллион записей

К счастью, теперь, в продолжение темы про подсказки об упущенных индексах, наш сервис визуализации explain.tensor.ru научился различать не только условия на самом узле чтения ( Seq Scan ), но и стоящем выше него Hash Join .

В нашем примере каждый из 3 параллельных воркеров "свои" 333333 записи tblfk превращал в Hash Join в 3164 результирующую запись:

-> Hash Join (actual time=1.635..57.796 rows=3164 loops=3) Hash Cond: (tblfk.k = tblpk.k) Buffers: shared hit=8867 -> Parallel Seq Scan on tblfk (actual time=1.506..24.588 rows=333333 loops=3) Buffers: shared hit=8850

А если условие фильтрации для tblfk у нас получается известно ( tblfk.k = tblpk.k ), то нам ничто не мешает порекомендовать создать подходящий индекс:

Hash Join + Seq Scan = index

Нам рекомендовано создать индекс:

CREATE INDEX CONCURRENTLY "~tblfk-67ed26fd" ON tblfk(k);

Запомним это, но пока не будем его накатывать.

А заодно запомним, что PostgreSQL не создает автоматически индексы для внешних ключей.

UPDATE/DELETE "тупит". на триггере?

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

Но вот нам захотелось удалить (или даже обновить, если речь идет о старых версиях PostgreSQL) запись из основной таблицы:

DELETE FROM tblpk WHERE k = 1000;

Удаление по foreign key

Оу. подсказка сразу акцентирует наше внимание, что 99.9% всего времени ушло вовсе не на выполнение запроса, а на Trigger for constraint tblfk_k_fkey .

Помните ON DELETE CASCADE в начале? Вот это он и есть - отработка внешнего ключа через триггер.

Давайте включим auto_explain и пристально посмотрим в лог сервера на аналогичном запросе:

LOAD 'auto_explain'; SET auto_explain.log_analyze = 'on'; SET auto_explain.log_buffers = 'on'; SET auto_explain.log_min_duration = 0; SET auto_explain.log_nested_statements = 'on'; SET auto_explain.log_timing = 'on'; SET auto_explain.log_triggers = 'on'; DELETE FROM tblpk WHERE k = 999;
2022-05-11 15:02:44.196 MSK [17696] LOG: duration: 264.759 ms plan: Query Text: DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k" Delete on tblfk (cost=0.00..16925.00 rows=996 width=6) (actual time=264.757..264.757 rows=0 loops=1) Buffers: shared hit=6252 dirtied=1279 -> Seq Scan on tblfk (cost=0.00..16925.00 rows=996 width=6) (actual time=0.181..143.802 rows=1016 loops=1) Filter: (999 = k) Rows Removed by Filter: 998497 Buffers: shared hit=4425 dirtied=467 2022-05-11 15:02:44.196 MSK [17696] CONTEXT: SQL statement "DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k""

Собственно, вот он и есть - виновник наших тормозов - " Seq Scan на миллион":

При удалении читаем миллион записей

Логично, что к нему рекомендовано создание того же индекса - таки создадим же его:

CREATE INDEX CONCURRENTLY "~tblfk-67ed26fd" ON tblfk(k);

Ну, как там наше удаление теперь?

DELETE FROM tblpk WHERE k = 1;

Удаление по индексированному FK

А вот теперь стало все отлично - 7ms вместо 95ms, поскольку удаление из tblfk теперь пользуется нашим индексом:

2022-05-11 15:13:16.566 MSK [17696] LOG: duration: 3.166 ms plan: Query Text: DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k" Delete on tblfk (cost=12.14..2399.04 rows=996 width=6) (actual time=3.151..3.151 rows=0 loops=1) Buffers: shared hit=1550 dirtied=291 -> Bitmap Heap Scan on tblfk (cost=12.14..2399.04 rows=996 width=6) (actual time=0.149..0.760 rows=531 loops=1) Recheck Cond: (1 = k) Heap Blocks: exact=508 Buffers: shared hit=511 -> Bitmap Index Scan on "~tblfk-67ed26fd" (cost=0.00..11.89 rows=996 width=0) (actual time=0.092..0.092 rows=531 loops=1) Index Cond: (k = 1) Buffers: shared hit=3 2022-05-11 15:13:16.566 MSK [17696] CONTEXT: SQL statement "DELETE FROM ONLY "public"."tblfk" WHERE $1 OPERATOR(pg_catalog.=) "k""

INSERT совсем небыстр

Теперь-то у нас все хорошо? С индексом SELECT работает по нашим таблицам теперь быстро, UPDATE/DELETE - тоже, а как там поживает INSERT ?

Восстановим удаленные нами записи в основной таблице:

INSERT INTO tblpk VALUES(1),(998),(999),(1000);

И докинем еще тысячу записей в дополнительную:

INSERT INTO tblfk(k, v) SELECT (random() * (1e3 - 1))::integer + 1 , (random() * 1e6)::integer FROM generate_series(1, 1e3);

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

2022-05-11 15:23:00.005 MSK [17696] LOG: duration: 0.296 ms plan: Query Text: SELECT 1 FROM ONLY "public"."tblpk" x WHERE "k" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x LockRows (cost=0.28..8.30 rows=1 width=10) (actual time=0.292..0.292 rows=1 loops=1) Buffers: shared hit=5 dirtied=1 -> Index Scan using tblpk_pkey on tblpk x (cost=0.28..8.29 rows=1 width=10) (actual time=0.017..0.017 rows=1 loops=1) Index Cond: (k = 361) Buffers: shared hit=3 2022-05-11 15:23:00.005 MSK [17696] CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."tblpk" x WHERE "k" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

То есть при вставке каждой нашей записи сервер идет в основную таблицу, находит запись с искомым ключом (по индексу, конечно) и вешает на нее FOR KEY SHARE -блокировку, чтобы никто в ней не успел сменить значение первичного ключа, пока мы тут вставляем остальное.

Неудивительно, что в плане запроса мы увидим все тот же несчастный триггер, обслуживающий foreign key, который и занял 90% всего времени:

Вставка с проверкой и блокировкой foreign key

Отсюда вывод: если вам необходимо вставлять много и быстро в PostgreSQL, то это вполне реально, но внешними ключами придется пожертвовать.

Подробнее о способах оптимизации записи в PostgreSQL можно почитать в статье "Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB" или расшифровке моего доклада "Массовая оптимизация запросов PostgreSQL".

Sysadminium

Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Оглавление скрыть

Теория

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

  • Все записи относящиеся к первичному ключу должны быть уникальны. Это означает, что если первичный ключ состоит из одного поля, то все записи в нём должны быть уникальными. А если первичный ключ состоит из нескольких полей, то комбинация этих записей должна быть уникальна, но в отдельных полях допускаются повторения.
  • Записи в полях относящихся к первичному ключу не могут быть пустыми. Это ограничение в PostgreSQL называется not null.
  • В каждой таблице может присутствовать только один первичный ключ.

К первичному ключу предъявляют следующее требование:

  • Первичный ключ должен быть минимально достаточным. То есть в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности. Это не обязательное требование но желательно его соблюдать.

Первичный ключ может быть:

  • естественным — существует в реальном мире, например ФИО, или номер и серия паспорта;
  • суррогатным — не существует в реальном мире, например какой-то порядковый номер, который существует только в базе данных.

Я сам не имею большого опыта работы с SQL, но в книгах пишут что лучше использовать естественный первичный ключ. Почему именно так, я пока ответить не смогу.

Связь между таблицами

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

  • эти поля должны присутствовать и в ссылающейся таблице и в той таблице на которую он ссылается;
  • ссылается внешний ключ из одной таблицы обычно на первичный ключ другой таблицы.

Например, у вас есть таблица «Ученики» (pupils) и выглядит она следующим образом:

ФИО
full_name
Возраст
age
Класс
class
Иванов Иван Иванович 15
Сумкин Фёдор Андреевич 15
Петров Алексей Николаевич 14
Булгаков Александр Геннадьевич 14

Таблица pupils

И есть таблица «Успеваемость» (evaluations):

Предмет
item
ФИО
full_name
Оценка
evaluation
Русский язык Иванов Иван Иванович 4
Русский язык Петров Алексей Николаевич 5
Математика Булгаков Александр Геннадьевич 3
Литература Сумкин Фёдор Андреевич 5

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице «Успеваемость» не может содержаться ФИО, которого нет в таблице « Ученики«. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле «ФИО» в таблице « Ученики«. А внешним ключом будет «ФИО» в таблице «Успеваемость«. При этом, если мы удаляем запись о каком-то ученике из таблицы «Ученики«, то все его оценки тоже должны удалиться из таблицы «Успеваемость«.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

  • составной первичный ключ — например, в качестве первичного ключа взять два поля: ФИО и Класс;
  • суррогатный первичный ключ — в таблице «Ученики» добавить поле «№ Ученика» и сделать это поле первичным ключом;
  • добавить более уникальное поле — например, можно использовать уникальный номер зачетной книжки и использовать новое поле в качестве первичного ключа;

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

postgres=# CREATE DATABASE school; CREATE DATABASE postgres=# \c school You are now connected to database "school" as user "postgres". school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3), PRIMARY KEY (full_name) ); CREATE TABLE school=# \dt pupils List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | pupils | table | postgres (1 row) school=# \d pupils Table "public.pupils" Column | Type | Collation | Nullable | Default -----------+----------------------+-----------+----------+--------- full_name | text | | not null | age | integer | | | class | character varying(3) | | | Indexes: "pupils_pkey" PRIMARY KEY, btree (full_name)

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

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

  • поле full_name, к которому относится первичный ключ не может быть пустым, это видно в колонки Nullablenot null;
  • для поля full_name был создан индекс pupils_pkey с типом btree. Про типы индексов и про сами индексы расскажу в другой статье.

Индекс в свою очередь наложил ещё одно ограничение — записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

school=# CREATE TABLE evaluations (item text, full_name text, evaluation integer, FOREIGN KEY (full_name) REFERENCES pupils ON DELETE CASCADE ); CREATE TABLE school=# \d evaluations Table "public.evaluations" Column | Type | Collation | Nullable | Default ------------+---------+-----------+----------+--------- item | text | | | full_name | text | | | evaluation | integer | | | Foreign-key constraints: "evaluations_full_name_fkey" FOREIGN KEY (full_name) REFERENCES pupils(full_name) ON DELETE CASCADE

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу «pupils«:

school=# INSERT into pupils (full_name, age, class) VALUES ('Иванов Иван Иванович', 15, '9A'), ('Сумкин Фёдор Андреевич', 15, '9A'), ('Петров Алексей Николаевич', 14, '8B'), ('Булгаков Александр Геннадьевич', 14, '8B'); INSERT 0 4

Заполним таблицу «evaluations«:

school=# INSERT into evaluations (item, full_name, evaluation) VALUES ('Русский язык', 'Иванов Иван Иванович', 4), ('Русский язык', 'Петров Алексей Николаевич', 5), ('Математика', 'Булгаков Александр Геннадьевич', 3), ('Литература', 'Сумкин Фёдор Андреевич', 5); INSERT 0 4

А теперь попробуем поставить оценку не существующему ученику:

school=# INSERT into evaluations (item, full_name, evaluation) VALUES ('Русский язык', 'Угаров Виктор Михайлович', 3); ERROR: insert or update on table "evaluations" violates foreign key constraint "evaluations_full_name_fkey" DETAIL: Key (full_name)=(Угаров Виктор Михайлович) is not present in table "pupils".

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

school=# delete from pupils WHERE full_name = 'Иванов Иван Иванович'; DELETE 1

И посмотрим на строки в таблице evaluations:

school=# SELECT * FROM evaluations; item | full_name | evaluation --------------+--------------------------------+------------ Русский язык | Петров Алексей Николаевич | 5 Математика | Булгаков Александр Геннадьевич | 3 Литература | Сумкин Фёдор Андреевич | 5 (3 rows)

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

school=# INSERT into pupils (full_name, age, class) VALUES ('Петров Алексей Николаевич',15, '5B'); ERROR: duplicate key value violates unique constraint "pupils_pkey" DETAIL: Key (full_name)=(Петров Алексей Николаевич) already exists.

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

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

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

school=# DROP table evaluations; DROP TABLE school=# DROP table pupils; DROP TABLE school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3), PRIMARY KEY (full_name, class) ); CREATE TABLE school=# CREATE TABLE evaluations (item text, full_name text, class varchar(3), evaluation integer, FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE ); CREATE TABLE

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

school=# \d pupils Table "public.pupils" Column | Type | Collation | Nullable | Default -----------+----------------------+-----------+----------+--------- full_name | text | | not null | age | integer | | | class | character varying(3) | | not null | Indexes: "pupils_pkey" PRIMARY KEY, btree (full_name, class) Referenced by: TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE school=# \d evaluations Table "public.evaluations" Column | Type | Collation | Nullable | Default ------------+----------------------+-----------+----------+--------- item | text | | | full_name | text | | | class | character varying(3) | | | evaluation | integer | | | Foreign-key constraints: "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

school=# INSERT INTO pupils (full_name, age, class) VALUES ('Гришина Ольга Константиновна', 12, '5A'), ('Гришина Ольга Константиновна', 14, '7B'); INSERT 0 2 school=# SELECT * FROM pupils; full_name | age | class ------------------------------+-----+------- Гришина Ольга Константиновна | 12 | 5A Гришина Ольга Константиновна | 14 | 7B (2 rows)

И также по второй таблице:

school=# INSERT INTO evaluations (item, full_name, class, evaluation) VALUES ('Русский язык', 'Гришина Ольга Константиновна', '5A', 5), ('Русский язык', 'Гришина Ольга Константиновна', '7B', 3); INSERT 0 2 school=# SELECT * FROM evaluations; item | full_name | class | evaluation --------------+------------------------------+-------+------------ Русский язык | Гришина Ольга Константиновна | 5A | 5 Русский язык | Гришина Ольга Константиновна | 7B | 3 (2 rows)

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

school=# DROP table pupils; ERROR: cannot drop table pupils because other objects depend on it DETAIL: constraint evaluations_full_name_class_fkey on table evaluations depends on table pupils HINT: Use DROP . CASCADE to drop the dependent objects too.

Поэтому удалим наши таблицы в следующем порядке:

school=# DROP table evaluations; DROP TABLE school=# DROP table pupils; DROP TABLE

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3), PRIMARY KEY (full_name, class) ); CREATE TABLE school=# CREATE TABLE evaluations (item text, full_name text, class varchar(3), evaluation integer, FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE ); school=# DROP TABLE pupils CASCADE; NOTICE: drop cascades to constraint evaluations_full_name_class_fkey on table evaluations DROP TABLE school=# \d List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | evaluations | table | postgres (1 row) school=# \d evaluations Table "public.evaluations" Column | Type | Collation | Nullable | Default ------------+----------------------+-----------+----------+--------- item | text | | | full_name | text | | | class | character varying(3) | | | evaluation | integer | | |

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

school=# DROP table evaluations; DROP TABLE

И сделаем таблицы без ключей:

school=# CREATE TABLE pupils (full_name text, age integer, class varchar(3) ); CREATE TABLE school=# CREATE TABLE evaluations (item text, full_name text, class varchar(3), evaluation integer ); CREATE TABLE

Теперь создадим первичный ключ в таблице pupils:

school=# ALTER TABLE pupils ADD PRIMARY KEY (full_name, class); ALTER TABLE

И создадим внешний ключ в таблице evaluations:

school=# ALTER TABLE evaluations ADD FOREIGN KEY (full_name, class) REFERENCES pupils ON DELETE CASCADE; ALTER TABLE

Посмотрим что у нас получилось:

school=# \d pupils Table "public.pupils" Column | Type | Collation | Nullable | Default -----------+----------------------+-----------+----------+--------- full_name | text | | not null | age | integer | | | class | character varying(3) | | not null | Indexes: "pupils_pkey" PRIMARY KEY, btree (full_name, class) Referenced by: TABLE "evaluations" CONSTRAINT "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE school=# \d evaluations Table "public.evaluations" Column | Type | Collation | Nullable | Default ------------+----------------------+-----------+----------+--------- item | text | | | full_name | text | | | class | character varying(3) | | | evaluation | integer | | | Foreign-key constraints: "evaluations_full_name_class_fkey" FOREIGN KEY (full_name, class) REFERENCES pupils(full_name, class) ON DELETE CASCADE

Итог

В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.

Дополнительно про первичный и внешний ключ sql можете почитать тут.

PostgreSQL Foreign Key

Summary: in this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.

Introduction to PostgreSQL Foreign Key Constraint

A foreign key is a column or a group of columns in a table that reference the primary key of another table.

The table that contains the foreign key is called the referencing table or child table. And the table referenced by the foreign key is called the referenced table or parent table.

A table can have multiple foreign keys depending on its relationships with other tables.

In PostgreSQL, you define a foreign key using the foreign key constraint. The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.

A foreign key constraint indicates that values in a column or a group of columns in the child table equal the values in a column or a group of columns of the parent table.

PostgreSQL foreign key constraint syntax

The following illustrates a foreign key constraint syntax:

[CONSTRAINT fk_name] FOREIGN KEY(fk_columns) REFERENCES parent_table(parent_key_columns) [ON DELETE delete_action] [ON UPDATE update_action]Code language: CSS (css)
  • First, specify the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
  • Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords.
  • Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.
  • Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.

The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. We’ll focus on the ON DELETE action.

PostgreSQL supports the following actions:

  • SET NULL
  • SET DEFAULT
  • RESTRICT
  • NO ACTION
  • CASCADE

PostgreSQL foreign key constraint examples

The following statements create the customers and contacts tables:

DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS contacts; CREATE TABLE customers( customer_id INT GENERATED ALWAYS AS IDENTITY, customer_name VARCHAR(255) NOT NULL, PRIMARY KEY(customer_id) ); CREATE TABLE contacts( contact_id INT GENERATED ALWAYS AS IDENTITY, customer_id INT, contact_name VARCHAR(255) NOT NULL, phone VARCHAR(15), email VARCHAR(100), PRIMARY KEY(contact_id), CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id) );Code language: SQL (Structured Query Language) (sql)

In this example, the customers table is the parent table and the contacts table is the child table.

Each customer has zero or many contacts and each contact belongs to zero or one customer.

The customer_id column in the contacts table is the foreign key column that references the primary key column with the same name in the customers table.

The following foreign key constraint fk_customer in the contacts table defines the customer_id as the foreign key:

CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id)Code language: SQL (Structured Query Language) (sql)

Because the foreign key constraint does not have the ON DELETE and ON UPDATE action, they default to NO ACTION .

NO ACTION

The following inserts data into the customers and contacts tables:

INSERT INTO customers(customer_name) VALUES('BlueBird Inc'), ('Dolphin LLC'); INSERT INTO contacts(customer_id, contact_name, phone, email) VALUES(1,'John Doe','(408)-111-1234','[email protected]'), (1,'Jane Doe','(408)-111-1235','[email protected]'), (2,'David Wright','(408)-222-1234','[email protected]');Code language: SQL (Structured Query Language) (sql)

The following statement deletes the customer id 1 from the customers table:

DELETE FROM customers WHERE customer_id = 1;Code language: SQL (Structured Query Language) (sql)

Because of the ON DELETE NO ACTION , PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the contacts table:

ERROR: update or delete on table "customers" violates foreign key constraint "fk_customer" on table "contacts" DETAIL: Key (customer_id)=(1) is still referenced from table "contacts". SQL state: 23503Code language: Shell Session (shell)

The RESTRICT action is similar to the NO ACTION . The difference only arises when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode. We’ll discuss more on this in the subsequent tutorial.

SET NULL

The SET NULL automatically sets NULL to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted.

The following statements drop the sample tables and re-create them with the foreign key that uses the SET NULL action in the ON DELETE clause:

DROP TABLE IF EXISTS contacts; DROP TABLE IF EXISTS customers; CREATE TABLE customers( customer_id INT GENERATED ALWAYS AS IDENTITY, customer_name VARCHAR(255) NOT NULL, PRIMARY KEY(customer_id) ); CREATE TABLE contacts( contact_id INT GENERATED ALWAYS AS IDENTITY, customer_id INT, contact_name VARCHAR(255) NOT NULL, phone VARCHAR(15), email VARCHAR(100), PRIMARY KEY(contact_id), CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ); INSERT INTO customers(customer_name) VALUES('BlueBird Inc'), ('Dolphin LLC'); INSERT INTO contacts(customer_id, contact_name, phone, email) VALUES(1,'John Doe','(408)-111-1234','[email protected]'), (1,'Jane Doe','(408)-111-1235','[email protected]'), (2,'David Wright','(408)-222-1234','[email protected]');Code language: SQL (Structured Query Language) (sql)

The following statements insert data into the customers and contacts tables:

INSERT INTO customers(customer_name) VALUES('BlueBird Inc'), ('Dolphin LLC'); INSERT INTO contacts(customer_id, contact_name, phone, email) VALUES(1,'John Doe','(408)-111-1234','[email protected]'), (1,'Jane Doe','(408)-111-1235','[email protected]'), (2,'David Wright','(408)-222-1234','[email protected]');Code language: SQL (Structured Query Language) (sql)

To see how the SET NULL works, let’s delete the customer with id 1 from the customers table:

DELETE FROM customers WHERE customer_id = 1;Code language: SQL (Structured Query Language) (sql)

Because of the ON DELETE SET NULL action, the referencing rows in the contacts table set to NULL. The following statement displays the data in the contacts table:

SELECT * FROM contacts;

PostgreSQL Foreign Key - SET NULL

As can be seen clearly from the output, the rows that have the customer_id 1 now have the customer_id sets to NULL

CASCADE

The ON DELETE CASCADE automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted. In practice, the ON DELETE CASCADE is the most commonly used option.

The following statements recreate the sample tables. However, the delete action of the fk_customer changes to CASCADE :

DROP TABLE IF EXISTS contacts; DROP TABLE IF EXISTS customers; CREATE TABLE customers( customer_id INT GENERATED ALWAYS AS IDENTITY, customer_name VARCHAR(255) NOT NULL, PRIMARY KEY(customer_id) ); CREATE TABLE contacts( contact_id INT GENERATED ALWAYS AS IDENTITY, customer_id INT, contact_name VARCHAR(255) NOT NULL, phone VARCHAR(15), email VARCHAR(100), PRIMARY KEY(contact_id), CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); INSERT INTO customers(customer_name) VALUES('BlueBird Inc'), ('Dolphin LLC'); INSERT INTO contacts(customer_id, contact_name, phone, email) VALUES(1,'John Doe','(408)-111-1234','[email protected]'), (1,'Jane Doe','(408)-111-1235','[email protected]'), (2,'David Wright','(408)-222-1234','[email protected]');Code language: SQL (Structured Query Language) (sql)

The following statement deletes the customer id 1:

DELETE FROM customers WHERE customer_id = 1;

Because of the ON DELETE CASCADE action, all the referencing rows in the contacts table are automatically deleted:

SELECT * FROM contacts;

PostgreSQL Foreign Key - ON DELETE CASCADE

SET DEFAULT

The ON DELETE SET DEFAULT sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted.

Add a foreign key constraint to an existing table

To add a foreign key constraint to the existing table, you use the following form of the ALTER TABLE statement:

ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (fk_columns) REFERENCES parent_table (parent_key_columns);Code language: SQL (Structured Query Language) (sql)

When you add a foreign key constraint with ON DELETE CASCADE option to an existing table, you need to follow these steps:

First, drop existing foreign key constraints:

ALTER TABLE child_table DROP CONSTRAINT constraint_fkey;Code language: SQL (Structured Query Language) (sql)

First, add a new foreign key constraint with ON DELETE CASCADE action:

ALTER TABLE child_table ADD CONSTRAINT constraint_fk FOREIGN KEY (fk_columns) REFERENCES parent_table(parent_key_columns) ON DELETE CASCADE;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about PostgreSQL foreign keys and how to use the foreign key constraint to create foreign keys for a table.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *