5.8. Ограничители (задание целостности на уровне схемы)

Целостность и ограничители

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

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

Выше мы рассмотрели как с помощью триггера можно поддерживать целостность базы данных, а именно как устранять "висячие" ссылки. Другим примером ограничителя, с которым мы уже знакомы, является описатель NOT NULL (смотри оператор создания таблицы CREATE TABLE), который говорит серверу о том, что данное поле всегда должно иметь определенное значение.

В SQL существуют специальные механизмы для поддержания целостности. Эти механизмы охватывают наиболее часто встречаемые случаи и называются ограничителями. В отличии от триггеров, ограничители носят не программный, а описательный характер. То есть, при задании ограничителя надо указывать ЧТО надо проверять, а не КАК надо проверять.

Ограничители, по сути, определяют возможные значения для данного поля данной таблицы. Ограничители являются частью схемы, то есть они проверяются помимо воли программиста или пользователя. Другими словами, пока явно ограничители не будут убраны, состояние базы данных будет удовлетворять заданным ограничениям. В SQL (напомним, что речь идет о варианте SQL фирмы Informix версии 5) присутствуют следующие типы ограничителей - значения по умолчанию, ограничители на значение, ограничители на уникальность и ссылочные ограничители.

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

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

Значения по умолчанию для полей

При вставке новых рядов в таблицу часто указываются значения не для всех полей, а только для некоторых. Поля, значения для которых не указаны, приобретают значение NULL (не определено).Но иногда разработчикам хочется, что бы значение по умолчанию было каким-то конкретным. Например, при вставке информации о каком-то товаре поле "количество заказов на этот товар" разумно обнулять.

Значения по умолчанию для того или иного пля задаются при создании таблицы (оператор CREATE TABLE), либо при модификации структуры таблицы (оператор ALTER TABLE). При описании поля после типа данных надо указать ключевое слово DEFAULT и значение по умолчанию. В качестве значения по умолчанию может стоять константа (в том числе NULL) или функция-псевдополе (CURRENT, TODAY, USER). Пример:

{ таблица с заказами } CREATE TABLE orders ( order_id SERIAL, { уникальный ключ для заказа } item INTEGER NOT NULL, { ссылка на товар } when DATE DEFAULT TODAY, { дата заказа } quantity INTEGER DEFAULT 0, { количество } tot_price MONEY(20,2) DEFAULT "0.0" { общая стоимость } )

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

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

В SQL есть средства более тонкого описания множества допустимых значений поля (кстати, это множество в теории называют доменом). Более точно задать домен для того или иного поля можно с помощью ограничителя на значение (check constraint). Этот ограничитель указывается при создании таблицы (оператор CREATE TABLE). После типа поля или значения по умолчанию надо указать ключевое слово CHECK и логическое выражение в скобках:

CREATE TABLE <имя таблицы> ( ..... <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] CHECK (<логическое выражение>) .....)

Это логическое выражение и будет опредеять допустимость значения. Условие проверяется перед изменением данных в поле операторами UPDATE или INSERT. Если значение логического выражение ложь или NULL, то сервер базы данных возвращает ошибку. Если вычисленное логическое выражение имеет значение истина, то новое значение считается допустимым и операция модификации завершается успешно. Например, можно описание таблицы orders расширить следующим образом:

CREATE TABLE orders ( ..... item INTEGER NOT NULL CHECK (EXISTS (SELECT items.item_id FROM items WHERE items.item_id = item) ), ..... quantity INTEGER DEFAULT 0 CHECK (quantity > 0), tot_price MONEY(20,2) DEFAULT "0.0" CHECK (quantity > 0) )

В ограничителе на значение поля можно использовать сколь угодно сложное логическое выражение. Требования к этому выражению такие же, как и к условию в разделе WHERE оператора SELECT. То есть можно использовать логические операции IN, MATCHES, NOT, OR, AND, EXISTS и т.д. Не допускается, однако, использование вложенных подзапросов, агрегатных функций, псевдофункций-полей (CURRENT, TODAY, USER) и вызовов хранимых процедур. Нельзя, также, использовать логическое выражение, которое зависит от других полей в этой таблице. Например, если мы напишем

CREATE TABLE .... price MONEY, quantity INTEGER, tot_price MONEY CHECK (tot_price = price*quantity) .....

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

Уникальные поля

Для задания уникального ключа в таблице обычно используется тип SERIAL. Этот тип по множеству значений и способу внутреннего представления идентичен типу INTEGER. Но гарантировать уникальность значений поля типа SERIAL можно только в том случае, когда сервер сам генерирует новое значение для типа SERIAL, то есть оператор INSERT с явным указанием нового значения SERIAL не выполняется. Но иногда задача требует, что бы гарантированно поля любого типа, в том числе и SERIAL, использовались в качестве первичного ключа. Или, что тоже часто встречается, предметная область накладывает требование, что бы набор некоторых полей был уникальным, то есть был бы тоже первичным ключом (ключ, состоящий из нескольких полей, называется составным). Для проверки поля или группы полей на то, что они являются первичным ключом используется ограничитель на уникальность. Ограничитель на уникальность группы полей будет рассмотрен в параграфе 4.5. Здесь мы рассмотрим ограничитель на уникальность для отдельного поля.

Для того, чтобы сервер автоматически проверял и поддерживал уникальность для некоторого поля, надо для данного поля ввести ограничитель на уникальность. Для обозначения этого используется ключевое слово UNIQUE, которое ставится после типа поля или после значения по умолчанию, если таковое есть:

CREATE TABLE <имя таблицы> ( ..... <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] UNIQUE .....)

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

CREATE TABLE trademarks ( name CHAR(64) UNIQUE, { название торговой марки } company INTEGER, { какой компании принадлежит } .... )

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

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

Наличие в таблице уникальных полей, диктуемых логикой предметной области, не мешает нам завести искусственный первичный ключ типа SERIAL и использовать именно его для ссылок. Более того, в целях повышения эффективности, стоит поступать именно так. Тогда структура таблицы с торговыми марками будет выглядеть так:

CREATE TABLE trademarks ( trademark_id SERIAL UNIQUE, { первичный ключ } name CHAR(64) UNIQUE, { название торговой марки } company INTEGER, { какой компании принадлежит } .... )

Ограничитель на уникальность не может использоваться совместо с ограничителем на значение.

Ссылочная целостность

Различные таблицы в базе данных тем или иным способом связаны друг с другом. Логика этой связи определяется предметной областью. Например, человек связан с компанией тем, что работает в ней. Или товар связан с компанией тем, что поставляется этой компанией. Эти связи (отношения) могут быть типа один-к-одному, один-ко-многим, многие-ко-многим. Например, одной компании может соответсвовать несколько товаров, которые она поставляет (теоретически, в том числе и ноль). Это соотношение один-к-многим. Примером отношения один-к-одному является связь таблиц "паспорт" и "человек" (конечно, если не рассматриать криминальные случаи).

Для реализации таких отношений между таблицами используются ссылки. То есть в одной из двух связанных таблиц заводится поле (или поля), которое представляет собой значение первичного ключа в другой таблице. Именно таким образом связаны таблицы companies, persons, items, и orders. Здесь главной таблицей является таблица с информацией о фирмах (companies). Все остальные таблицы - люди (persons) и товары (items) в некотором смысле являются подчиненными таблице companies. То есть ни человек, ни товар не могут быть определены, если нет информации о соответсвующей фирме. Таблица orders, хотя и связана с таблицей items соотношением один-к-одному, тем не менее должна рассматриваться как пдчиненная последней, так как поставляемые товары могут существовать без конкретных заказов (мы этот товар еще ни разу не покупали), а наоборот - нет. В соответствии с этим, в подчиненной таблице хранится ссылка на главную, а не наоборот.

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

+-------------+ +-------------+ +----------+ | companies | ++------------+| ++---------+| +-------------+ ++------------+|| ++---------+|| +->| company_id |<--+ | persons ||| | items ||| | | name | | +-------------+|| +----------+|| | | address | | | person_id ||| | item_id ||| | | phone | +--+- company ||| | company -+----+ +-------------+ | lname ||| | name |++ | fname |++ | price ++ | sname ++ +----------+ +-------------+

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

CREATE TABLE <имя таблицы> ( ..... <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] PRIMARY KEY, ....., <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] REFERENCES <имя таблицы> (<имя поля>) .....)

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

CREATE TABLE companies ( company_id SERIAL PRIMARY KEY, ....)

CREATE TABLE items ( item_id SERIAL PRIMARY KEY, company INTEGER REFERENCES companies(company_id), ....)

CREATE TABLE persons ( person_id SERIAL PRIMARY KEY, company INTEGER REFERENCES companies(company_id), ....)

CREATE TABLE orders ( ..... item INTEGER REFERENCES items(item_id), ....)

Ссылочный ограничитель не может использоваться совместо с ограничителем на значение или с ограничителем на уникальность.

Ограничители на уровне таблицы

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

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

CREATE TABLE .... price MONEY, quantity INTEGER, tot_price MONEY, ...., CHECK (tot_price = price*quantity) )

Ограничитель на уникальность может описывать составной ключ. Для этого после слова UNIQUE в скобках перечисляются поля из составного первичного ключа:

.... UNIQUE (<имя поля>, <имя поля>, ...)

Ссылочный ограничитель для таблицы, так же как и для одного поля, состоит из описания первичного ключа и ссылки на первичный ключ. Первичный ключ задается с помощью ключевых слов PRIMARY KEY, после которых в скобках через запятую идут составляющие его поля. Ссылка на первичный ключ начинается со слов FOREIGN KEY, после которых, также в скобках через запятую, перечислены составляющие ссылку поля. Затем после слова REFERENCES надо указать имя таблицы и в скобках поля первичного ключа для главной таблицы:

.... PRIMARY KEY (<имя поля>, <имя поля>, ...) .... FOREIGN KEY (<имя поля>, <имя поля> REFERENCES (<имя поля>, <имя поля>, ...)

Очевидно, должно быть соответствие между порядком и количеством полей после слов FOREIGN KEY и слова REFERENCES. Пример:

CREATE TABLE accounts ( acc_num INTEGER, acc_type INTEGER, ..., PRIMARY KEY (acc_num, acc_type))

CREATE TABLE sub_accounts ( ..., ref_num INTEGER NOT NULL, ref_type INTEGER NOT NULL, ..., FOREIGN KEY (ref_num, ref_type) REFERENCES accounts (acc_num, acc_type) )