Нетривиальное использование таблиц
Ограничения таблиц
В ограничениях таблиц, в отличие от ограничений полей, могут участвовать сразу несколько полей таблицы.
Синтаксис ограничения таблицы:
[ CONSTRAINT ограничение ] { UNIQUE (поле [….]) | PRIMARY KEY (поле [….]) | CHECK (условие) ]
FOREIGN KEY (поле […. ])
REFERENCES таблица [ (поле [….])]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE операция ]
[ ON UPDATE операция ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED INITIALLY IMMEDIATE ]
Секция CONSTRAINT ограничение определяет необязательное имя. Ограничениям рекомендуется присваивать содержательные имена вместо автоматически сгенерированных имен, не несущих никакой полезной информации. В будущем имя также может пригодиться и для удаления ограничения (например, в секции DROP CONSTRAINT команды ALTER TABLE). Другие секции относятся к четырем разновидностям ограничений таблиц.
- PRIMARY KEY (поле [….]). Ограничение таблицы PRIMARY KEY имеет много общего с аналогичным ограничением поля. В ограничении таблицы PRIMARY KEY могут перечисляться несколько полей, разделенных запятыми. Для перечисленных полей автоматически строится индекс. Как и в случае с ограничением поля, комбинация значений всех полей должна быть уникальной и не может содержать NULL.
- UNIQUE (поле [….]). Ограничение означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Допускается многократное вхождение псевдозначения NULL, поскольку оно формально не совпадает ни с одним значением.
- CHECK (условие). Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). Используется по аналогии с ограничениями полей, но в секции CHECK может содержать ссылки на несколько полей.
- FOREIGN KEY (поле […. ]) REFERENCES таблица [ (поле […. ]) ]. В качестве прототипа для секции REFERENCES можно перечислить несколько полей. Синтаксис части, следующей за секцией FOREIGN KEY, идентичен синтаксису ограничения REFERENCES для полей.
В листинге 7.8 в базе данных booktown создается таблица editions с тремя ограничениями. Развернутое описание приводится ниже.
Листинг 7.8. Использование ограничений таблицы.
booktown=# CREATE TABLE editions booktown-# (isbn text, booktown(# bookjid integer, booktown(# edition integer, booktown(# publisherjd integer, booktownCl publication date. booktown(# type char, booktown(# CONSTRAINT pkey PRIMARY KEY (isbn), booktown(# CONSTRAINT integrity CHECK (bookjd IS NOT NULL booktown(# AND edition IS NOT NULL). booktown(# CONSTRAINT book_exists FOREIGN KEY (bookjd) booktown(# REFERENCES books (id) booktown(# ON DELETE CASCADE booktown(# ON UPDATE CASCADE); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pkey1 for table 'editions' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE
Первое ограничение, pkey, относится к типу PRIMARY KEY и устанавливается для таблицы по полю isbn. Оно практически не отличается от ограничения PRIMARY KEY для поля, поскольку список в данном примере состоит всего из одного поля.
Ограничение integrity гарантирует, что поля book id и edition не содержат псевдозначения NULL.
Наконец, ограничение book_exists при помощи конструкций FOREIGN KEY и REFERENCES гарантирует, что значение поля book_id встречается в поле id таблицы books. Более того, поскольку в секциях ON DELETE и ON ACTION встречается ключевое слово CASCADE, любые модификации поля ids таблице books приведут к каскадным изменениям записей в таблице editions, а при удалении записей из таблицы books будут удалены соответствующие записи таблицы editions.
Для этих ограничений в базе данных автоматически строится индекс editions_pkey по полю isbn, а также создается триггер. Индекс обеспечивает выполнение ограничения PRIMARY KEY, а триггер относится к ограничению FOREIGN KEY.