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

Sysadminium

База знаний системного администратора

Первичный и внешний ключ SQL

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

Теория

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

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

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

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

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

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

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

Таблица pupils

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

Таблица evaluations

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

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

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

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

Практика

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Внешний ключ Postgresql

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

Синтаксис

[ CONSTRAINT name ] FOREIGN KEY ( columns )
REFERENCES parent_table ( columns )
[ ON DELETE action ]
[ ON UPDATE action ]

Справочная таблица — это родительская таблица. А столбец для внешнего ключа — это первичный ключ родителя.

Введение внешнего ключа в операторе CREATE

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

В таблице «конструктор» в качестве первичного ключа устанавливается designer_id. Теперь создайте вторую таблицу.

В этой таблице category_id установлен как первичный ключ. Поскольку обе эти таблицы должны соединяться с ограничением внешнего ключа. Мы делаем «designer_id» внешним ключом в этой таблице. Справочная таблица упоминается в таблице, чтобы запрос можно было легко выполнить, выполнив поиск в таблице.

CONSTRAINT fk_designer
FOREIGN KEY ( designer_id )
REFERENCES designer ( designer_id ) ) ;

Таблица конструктора — это таблица PARENT, а таблица категорий — таблица CHILD. Каждый дизайнер работает с нулем или более категориями платьев, и один или несколько дизайнеров рассматривают каждую категорию одежды.

ПРИМЕЧАНИЕ. Чтобы сделать внешний ключ идентификатора одной таблицы во второй таблице, важно сделать этот конкретный идентификатор ПЕРВИЧНЫМ КЛЮЧОМ в своей таблице. В противном случае он не будет формировать внешний ключ в другой таблице. Произойдет ошибка при создании внешнего ключа.

БЕЗДЕЙСТВИЕ

После создания таблицы мы используем для ввода значений в нее командой «вставить».

Источник

SQL миграции в Postgres. Часть 1

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

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

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

Содержание

Суть проблемы

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

При такой схеме обновления приложения зачастую происходят с простоем (downtime). В это же время можно обновлять БД. В такой ситуации главный критерий – это время, то есть нужно выполнить миграцию как можно быстрее, чтобы минимизировать время недоступности сервиса.

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

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

Именно про такую конфигурацию мы поговорим в этой статье и рассмотрим самые популярные миграции, которые пишут разработчики – от простых к более сложным.

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

Добавление столбца

Наверное, любой человек, который работает с БД, писал подобную миграцию. Если говорить про PostgreSQL, то такая миграция весьма дешевая и безопасная. Сама команда, хоть и захватывает блокировку самого высокого уровня (AccessExclusive), выполняется очень быстро, поскольку «под капотом» происходит лишь добавление метаинформации о новом столбце без перезаписи данных самой таблицы. В большинстве случаев это происходит незаметно. Но проблемы могут возникнуть, если в момент миграции есть долгие транзакции, работающие с этой таблицей. Чтобы понять суть проблемы рассмотрим на небольшом примере, как упрощенно работают блокировки в PostgreSQL. Этот аспект будет очень важен при рассмотрении большинства других миграций в том числе.

Предположим, у нас есть большая таблица, и мы делаем из нее SELECT всех данных. В зависимости от размера БД и самой таблицы он может длиться несколько секунд или даже минут.

На время выполнения транзакции захватывается самая слабая блокировка AccessShare, которая защищает от изменений структуры таблицы.

В этот момент приходит другая транзакция, которая как раз таки пытается сделать запрос ALTER TABLE к этой таблице. Команда ALTER TABLE, как уже было сказано ранее, захватывает блокировку AccessExclusive, которая не совместима вообще ни с какой другой. Она встает в очередь.

Эта очередь блокировок «разгребается» в строгом порядке, т.е. даже если после ALTER TABLE приходят другие запросы (например, также SELECTы), которые сами по себе не конфликтуют с первым запросом, они все встают в очередь за ALTER TABLE. В итоге приложение «встало» и ждет, пока ALTER TABLE выполнится.

Что делать в такой ситуации? Можно ограничить время захвата блокировки с помощью команды SET lock_timeout. Выполняем эту команду перед ALTER TABLE (ключевое слово LOCAL означает, что настройка действует только в пределах текущей транзакции, иначе – в пределах текущей сессии):

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

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

Добавление столбца со значением по умолчанию

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

Если версия PostgreSQL 11 или свежее, эта операция обходится весьма дешево. Дело в том, что в 11й версии была сделана оптимизация, благодаря которой вместо перезаписи таблицы значение по умолчанию сохраняется в специальную таблицу pg_attribute, и в дальнейшем при выполнении SELECT все пустые значения этого столбца будут «на лету» заменяться на это значение. При этом впоследствии, когда будет происходить перезапись строк в таблице из-за других модификаций, значение будет записываться в эти строки.

Более того, с 11й версии также можно сразу создавать новый столбец и помечать его как NOT NULL:

Как быть, если PostgreSQL старше, чем 11?

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

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

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

Но такой UPDATE «в лоб» делать на самом деле нельзя, поскольку при обновлении большой таблицы мы надолго заблокируем всю таблицу. Во второй статье (здесь в будущем будет ссылка) мы рассмотрим, какие существуют стратегии для обновления больших таблиц в PostgreSQL, а пока будем предполагать, что мы каким-то образом обновили данные, и теперь и старые данные, и новые будут с необходимым значением по умолчанию.

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

Здесь логика такая же, как и при добавлении столбца: данные таблицы не модифицируются, происходит только изменение метаинформации. В данном случае столбец помечается как удаленный и недоступный при запросах. Это объясняет тот факт, что при удалении столбца в PostgreSQL физически место не освобождается (если не выполнять VACUUM FULL), то есть данные старых записей по-прежнему остаются в таблице, но недоступны при обращении. Освобождение происходит постепенно при перезаписи строк в таблице.

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

Также нужно внимательно посмотреть на запросы «SELECT *» – фреймворки могут «мапить» все столбцы в структуру в коде (и наоборот) и, соответственно, снова можно столкнуться с проблемой обращения к несуществующему столбцу.

После вывода изменений на все сервера приложений можно удалить столбец.

Создание индекса

Те, кто работает с PostgreSQL, наверное, знают, что такая команда блокирует всю таблицу. Но еще с очень старой версии 8.2 существует ключевое слово CONCURRENTLY, которое позволяет создавать индекс в неблокирующем режиме.

Команда работает медленнее, но не мешает параллельным запросам.

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

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

REINDEX
Стоит отдельно сказать про команду REINDEX, которая как раз предназначена для пересоздания индекса. До 12й версии она работает только в блокирующем режиме, что не дает возможности ее использовать. В 12й версии PostgreSQL появилась поддержка CONCURRENTLY, и теперь и ей можно пользоваться.

Подробнее о механике работы REINDEX можно почитать в официальной документации.

Создание индекса для партиционированной таблицы

А теперь рассмотрим создание индексов для партиционированных таблиц. В PostgreSQL существует 2 вида партиционирования: через наследование и декларативное, появившееся в 10й версии. Рассмотрим оба на простом примере.

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

При партиционировании через наследование мы будем иметь примерно следующую схему.

Дочерние партиции для 2020 и 2021 годов:

Индексы по полю партиционирования для каждой из партиций:

Создание триггера/правила для вставки данных в таблицу оставим за рамками.

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

Теперь рассмотрим декларативное партиционирование.

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

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

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

К сожалению, CREATE INDEX не поддерживает ключевое слово CONCURRENTLY для партиционированных таблиц. Чтобы обойти ограничение и провести миграцию без блокировок можно сделать следующее.

Ограничения

Теперь пройдемся по ограничениям: NOT NULL, внешние, уникальные и первичные ключи.

Создание ограничения NOT NULL

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

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

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

Команда итерируется по строкам таблицы и проверяет, что все записи не not null. Но в отличие от обычного NOT NULL ограничения, блокировка, захватываемая в этой команде, не такая строгая (ShareUpdateExclusive) – она не блокирует операции insert, update и delete.

Создание внешнего ключа

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

К счастью, внешние ключи в PostgreSQL также поддерживают NOT VALID, а это значит мы можем использовать тот же подход, что был рассмотрен ранее с CHECK. Создаем невалидный внешний ключ:

затем обновляем данные и проводим валидацию:

Создание ограничения уникальности

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

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

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

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

Создание первичного ключа

Первичный ключ помимо уникальности накладывает ограничение not null. Если столбец изначально имел такое ограничение, то «превратить» его в первичный ключ не составит труда – так же создаем уникальный индекс CONCURRENTLY, а затем первичный ключ:

Важно отметить, что столбец должен иметь «честное» ограничение NOT NULL – рассмотренный ранее подход с помощью CHECK не сработает.

Если же ограничения нет, то до 11-й версии PostgreSQL ничего не поделать – без блокировки первичный ключ никак не создать.

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

Создаем новый столбец, который по умолчанию not null и имеет значение по умолчанию:

Настраиваем синхронизацию данных старого и нового столбцов с помощью триггера:

Далее необходимо обновить данные строк, которые не затронулись триггером:

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

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

После смены столбцов остается создать индекс и «прибраться за собой» – удалить триггер, функцию и старый столбец.

Краткая шпаргалка с миграциями

В следующей статье рассмотрим подходы по обновлению больших таблиц.
Всем легких миграций!

Источник