5.11. Наследование #
PostgreSQL реализует наследование таблиц, что может быть полезно для проектировщиков баз данных. (Стандарт SQL:1999 и более поздние версии определяют возможность наследования типов, но это во многом отличается от того, что описано здесь.)
Давайте начнём со следующего примера: предположим, что мы создаём модель данных для городов. В каждом штате есть множество городов, но лишь одна столица. Мы хотим иметь возможность быстро получать город-столицу для любого штата. Это можно сделать, создав две таблицы: одну для столиц штатов, а другую для городов, не являющихся столицами. Однако что делать, если нам нужно получить информацию о любом городе, будь то столица штата или нет? В решении этой проблемы может помочь наследование. Мы определим таблицу capitals
как наследника cities
:
CREATE TABLE cities ( name text, population float, elevation int -- в футах ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities);
В этом случае таблица capitals
наследует все столбцы своей родительской таблицы, cities
. Столицы штатов также имеют дополнительный столбец state
, в котором будет указан штат.
В PostgreSQL таблица может наследоваться от нуля или нескольких других таблиц, а запросы могут выбирать все строки родительской таблицы или все строки родительской и всех дочерних таблиц. По умолчанию принят последний вариант. Например, следующий запрос найдёт названия всех городов, включая столицы штатов, расположенных выше 500 футов:
SELECT name, elevation FROM cities WHERE elevation > 500;
Для данных из введения (см. Раздел 2.1) он выдаст:
name | elevation -----------+----------- Las Vegas | 2174 Mariposa | 1953 Madison | 845
А следующий запрос находит все города, которые не являются столицами штатов, но также находятся на высоте выше 500 футов:
SELECT name, elevation FROM ONLY cities WHERE elevation > 500; name | elevation -----------+----------- Las Vegas | 2174 Mariposa | 1953
Здесь ключевое слово ONLY
указывает, что запрос должен применяться только к таблице cities
, но не к таблицам, расположенным ниже cities
в иерархии наследования. Многие операторы, которые мы уже обсудили, — SELECT
, UPDATE
и DELETE
— поддерживают ключевое слово ONLY
.
Вы также можете добавить после имени таблицы *
, чтобы явно указать, что должны включаться и дочерние таблицы:
SELECT name, elevation FROM cities* WHERE elevation > 500;
Указывать *
не обязательно, так как теперь это поведение всегда подразумевается по умолчанию. Однако такая запись всё ещё поддерживается для совместимости со старыми версиями, где поведение по умолчанию могло быть изменено.
В некоторых ситуациях бывает необходимо узнать, из какой таблицы выбрана конкретная строка. Для этого вы можете воспользоваться системным столбцом tableoid
, присутствующим в каждой таблице:
SELECT c.tableoid, c.name, c.elevation FROM cities c WHERE c.elevation > 500;
этот запрос выдаст:
tableoid | name | elevation ----------+-----------+----------- 139793 | Las Vegas | 2174 139793 | Mariposa | 1953 139798 | Madison | 845
(Если вы попытаетесь выполнить его у себя, скорее всего вы получите другие значения OID.) Собственно имена таблиц вы можете получить, обратившись к pg_class
:
SELECT p.relname, c.name, c.elevation FROM cities c, pg_class p WHERE c.elevation > 500 AND c.tableoid = p.oid;
в результате вы получите:
relname | name | elevation ----------+-----------+----------- cities | Las Vegas | 2174 cities | Mariposa | 1953 capitals | Madison | 845
Тот же эффект можно получить другим способом, используя альтернативный тип regclass
; при этом OID таблицы выводится в символьном виде:
SELECT c.tableoid::regclass, c.name, c.elevation FROM cities c WHERE c.elevation > 500;
Механизм наследования не способен автоматически распределять данные команд INSERT
или COPY
по таблицам в иерархии наследования. Поэтому в нашем примере этот оператор INSERT
не выполнится:
INSERT INTO cities (name, population, elevation, state) VALUES ('Albany', NULL, NULL, 'NY');
Мы могли надеяться на то, что данные каким-то образом попадут в таблицу capitals
, но этого не происходит: INSERT
всегда вставляет данные непосредственно в указанную таблицу. В некоторых случаях добавляемые данные можно перенаправлять, используя правила (см. Главу 39). Однако в нашем случае это не поможет, так как таблица cities
не содержит столбца state
и команда будет отвергнута до применения правила.
Дочерние таблицы автоматически наследуют от родительской таблицы ограничения-проверки и ограничения NOT NULL (если только для них не задано явно NO INHERIT
). Все остальные ограничения (уникальности, первичный ключ и внешние ключи) не наследуются.
Таблица может наследоваться от нескольких родительских таблиц, в этом случае она будет объединять в себе все столбцы этих таблиц, а также столбцы, описанные непосредственно в её определении. Если в определениях родительских и дочерней таблиц встретятся столбцы с одним именем, эти столбцы будут «объединены», так что в дочерней таблице окажется только один столбец. Чтобы такое объединение было возможно, столбцы должны иметь одинаковый тип данных, в противном случае произойдёт ошибка. Наследуемые ограничения-проверки и ограничения NOT NULL объединяются подобным образом. Так, например, объединяемый столбец получит свойство NOT NULL, если какое-либо из порождающих его определений имеет свойство NOT NULL. Ограничения-проверки объединяются, если они имеют одинаковые имена; но если их условия различаются, происходит ошибка.
Отношение наследования между таблицами обычно устанавливается при создании дочерней таблицы с использованием предложения INHERITS
оператора CREATE TABLE
. Другой способ добавить такое отношение для таблицы, определённой подходящим образом, — использовать INHERIT
с оператором ALTER TABLE
. Для этого будущая дочерняя таблица должна уже включать те же столбцы (с совпадающими именами и типами), что и родительская таблица. Также она должна включать аналогичные ограничения-проверки (с теми же именами и выражениями). Удалить отношение наследования можно с помощью указания NO INHERIT
оператора ALTER TABLE
. Динамическое добавление и удаление отношений наследования может быть полезно при реализации секционирования таблиц (см. Раздел 5.12).
Для создания таблицы, которая затем может стать наследником другой, удобно воспользоваться предложением LIKE
оператора CREATE TABLE
. Такая команда создаст новую таблицу с теми же столбцами, что имеются в исходной. Если в исходной таблицы определены ограничения CHECK
, для создания полностью совместимой таблицы их тоже нужно скопировать, и это можно сделать, добавив к предложению LIKE
параметр INCLUDING CONSTRAINTS
.
Родительскую таблицу нельзя удалить, пока существуют унаследованные от неё. При этом в дочерних таблицах нельзя удалять или модифицировать столбцы или ограничения-проверки, унаследованные от родительских таблиц. Если вы хотите удалить таблицу вместе со всеми её потомками, это легко сделать, добавив в команду удаления родительской таблицы параметр CASCADE
(см. Раздел 5.15).
При изменениях определений и ограничений столбцов команда ALTER TABLE
распространяет эти изменения вниз в иерархии наследования. Однако удалить столбцы, унаследованные дочерними таблицами, можно только с помощью параметра CASCADE
. При создании отношений наследования команда ALTER TABLE
следует тем же правилам объединения дублирующихся столбцов, что и CREATE TABLE
.
В запросах с наследуемыми таблицами проверка прав доступа выполняется только в родительской таблице. Так, например, наличие разрешения UPDATE
для таблицы cities
подразумевает право на изменение строк также в таблице capitals
, когда к ним происходит обращение через таблицу cities
. Это сохраняет видимость того, что эти данные (также) находятся в родительской таблице. Но изменить таблицу capitals
напрямую без дополнительного разрешения нельзя. Подобным образом, политики защиты на уровне строк (см. Раздел 5.9) для родительской таблицы применяются к строкам, получаемым из дочерних таблиц при выполнении запроса с наследованием. Политики же дочерних таблиц, если они определены, действуют только когда такие таблицы явно задействуются в запросе; в этом случае все политики, связанные с родительскими таблицами, игнорируются.
Сторонние таблицы (см. Раздел 5.13) могут также входить в иерархию наследования как родительские или дочерние таблицы, так же, как и обычные. Если в иерархию наследования входит сторонняя таблица, все операции, не поддерживаемые ей, не будут поддерживаться иерархией в целом.
5.11.1. Ограничения #
Заметьте, что не все SQL-команды могут работать с иерархиями наследования. Команды, выполняющие выборку данных, изменение данных или модификацию схемы (например SELECT
, UPDATE
, DELETE
, большинство вариантов ALTER TABLE
, но не INSERT
и ALTER TABLE ... RENAME
), обычно по умолчанию обрабатывают данные дочерних таблиц и могут исключать их, если поддерживают указание ONLY
. Команды для обслуживания и настройки базы данных (например REINDEX
и VACUUM
) обычно работают только с отдельными физическими таблицами и не поддерживают рекурсивную обработку отношений наследования. Соответствующее поведение каждой команды описано в её справке (Команды SQL).
Возможности наследования серьёзно ограничены тем, что индексы (включая ограничения уникальности) и ограничения внешних ключей относятся только к отдельным таблицам, но не к их потомкам. Это касается обеих сторон ограничений внешних ключей. Таким образом, применительно к нашему примеру:
Если мы объявим
cities
.name
с ограничениемUNIQUE
илиPRIMARY KEY
, это не помешает добавить в таблицуcapitals
строки с названиями городов, уже существующими в таблицеcities
. И эти дублирующиеся строки по умолчанию будут выводиться в результате запросов кcities
. На деле таблицаcapitals
по умолчанию вообще не будет содержать ограничение уникальности, так что в ней могут оказаться несколько строк с одним названием. Хотя вы можете добавить вcapitals
соответствующее ограничение, но это не предотвратит дублирование при объединении сcities
.Подобным образом, если мы укажем, что
cities
.name
ссылается (REFERENCES
) на какую-то другую таблицу, это ограничение не будет автоматически распространено наcapitals
. В этом случае решением может стать явное добавление такого же ограниченияREFERENCES
в таблицуcapitals
.Если вы сделаете, чтобы столбец другой таблицы ссылался на
cities(name)
, в этом столбце можно будет указывать только названия городов, но не столиц. В этом случае хорошего решения нет.
Некоторая функциональность, не реализованная для иерархий наследования, реализована для декларативного секционирования. Поэтому обязательно взвесьте все за и против, прежде чем применять в своих приложениях секционирование с использованием устаревшего наследования.