Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 18. Ограничения на множество допустимых значений данных

Ограничения для первичного ключа

(PRIMARY КЕУ)

До сих пор мы обсуждали первичный ключ в качестве логической концепции. Из­ вестно, чем он является для любой таблицы и как он должен использоваться. Но что об этом "знает" SQL? Нужно использовать ограничение UNIQUE или уникальные ин­ дексы для первичного ключа, чтобы удостовериться в их уникальности. В ранних вер­ сиях SQL это было необходимо, сейчас же применяется по желанию. Однако сейчас SQL поддерживает первичные ключи непосредственно с помощью ограничения PRIMARY КЕУ. Это ограничение может быть доступно или недоступно в конкретной

системе.

Ограничение PRIMARY КЕУ может быть применено к таблице или к множеству строк. По функциональным возможностям оно сходно с ограничением UNIQUE, за исключением того, что только один первичный ключ (состоящий из любого количест­ ва столбцов) может быть определен для данной таблицы. Следовательно, существует различие между первичным ключом и уникальными столбцами в способах их исполь­ зования с внешними ключами. Синтаксис и определение уникальности зависят от ог­ раничения уникальности (UNIQUE).

В первичном ключе недопустимо применение NULL-значений. Это означает, что, подобно полям с ограничениями UNIQUE, любое поле, используемое в ограничении PRIMARY КЕУ, должно быть объявлено NOT NULL. Вот усовершенствованная вер­ сия определения таблицы Salespeop\e:

CREATE TABLE Salespeople

(snum integeг NOT NULL PRIMARY КЕУ,

sname char(10) NOT NULL UNIQUE,

city char(10), comm decimal);

Ясно, что поля с атрибутом UNIQUE можно отнести к той же самой таблице. Луч­ ший способ ввести ограничение PRIMARY КЕУ для полей - это ввести уникальный идентификатор строк и сохранить ограничение UNIQUE для полей, которые должны быть уникальными, исходя из логики данных (например, номера телефона или поле snaшe вместо использования идентификации строк).

Первичные ключи, состоящие более чем из одного поля. Ограничение PRIMARY КЕУ может относиться к множеству полей, дающих уникальную комби­ нацию значений. Предположим, первичным ключом является имя (name), а фамилия (first пате) и имя (\ast паше) хранятся в двух различных полях (таким образом можно организовать данные как единое целое). Ни имя, ни фамилия не являются уникальны­ ми (их уникальности практически невозможно добиться) Можно применить ограни­ чение PRIMARУ КЕУ в качестве ограничения таблицы к паре столбцов:

CREATE TABLE Namefield

(firstname сhаг(10) NOT NULL,

190

Ограничепия в таблицах

city char(10),

PRIMARY КЕУ (firstname, lastname));

Единственная проблема, возникающая при использовании этого подхода, состоит в

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

Mary Smith и М. Smith, вы рискуете их перепутать. Проще ввести числовое поле, кото­

рое позволит различать строки, и это поле определить как PRIMARY КЕУ, а ограниче­

ние UNIQUE задать для двух имен полей.

Выбор значений поля

Можно определить любое количество ограничений, которым должны удовлетво­ рять данные, вводимые в таблицы, например: попадают ли данные в заданный диапа­ зон допустимых значений, и имеют ли они корректный формат. Для этих целей SQL предоставляет ограничение СНЕСК, позволяющее определить условие, которому

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

заданному для поля значению. Любая попытка обновить или заменить значения поля

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

Рассмотрим таблицу Salespeople. Комиссионные (столбец comm) представлены как имеющие тип decimal, следовате.1ьно, это значение можно непосредственно умножить на то, которое хранится в столбце amount, чтобы получить правильное выражение в долларах. Некто, привыкший думать о комиссионных в процентах, может не обратить на это внимания. Если он вводит значение 14 вместо .14 в качестве значения поля, со­

держащего величину комиссионных, то оно воспримется как 14.0, т.е. правильное де­

сятичное значение. Для защиты от ошибок такого рода можно воспользоваться ограничением на столбец СНЕСК, чтобы убедиться, что значения поля comm не пре­

вышают 1.

CREATE TABLE Salespeople

(snum integer NOT NULL UNIQUE,

sname char(10) NOT NULL UNIQUE,

city char(10),

Использование СНЕСК для предотвращения ввода ошибочных значений. Мож­ но использовать ограничение СНЕСК для обеспечения ввода в поле специфичных зна­ чений и таким образом избежать ошибок. Предположим, офисы по продаже есть только в городах London, Barcelona, San Jose и New York. Поскольку известно, что ка­ ждый из продавцов работает в одном из этих офисов, не имеет смысла разрешать ввод других значений в этот столбец (столбец city) таблицы Salespeople. Использование ог­ раничения на множество допустимых значений с помощью явного перечисления всех

191

Глава 18. Ограничения на множество допустимых значений данных

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

Можно ввести это ограничение, если вы совершенно уверены в том, что кампания

не имеет и в ближайшем будущем не предполагает иметь офисы в других городах. Из­ менение описания созданной таблицы - полезная, но рискованная операция, так как она не является частью стандарта ANSI. Большинство программ обработки баз дан­ ных поддерживает команду ALTER TABLE, разрешающую изменять описание табли­ цы, даже если эта таблица используется. Однако, изменение или удаление

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

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

Перед вами определение таблицы Orders:

CREATE TABLE Orders

Тип DATE широко поддерживается, но не является частью стандарта ANSI. Что де­ лать, если используется база данных, которая в соответствии со стандартом ANSI не рас­ познает тип данных DATE? Если объявить, что тип данных odate должен быть числовым, то нельзя использовагь ни слэш ("/"), ни дефис ("-") в качестве разделителей. Поскольку

символы, выводимые на экран дисплея или на печатающее устройство, являются симво­

лами из набора ASCII, можно объявить столбец odate имеющим символьный (СНАR) тип. При этом возникает единственная проблема: необходимо использовать одиночные кавычки всякий раз, когда есть ссылка в запросе на значение поля odate. Простого реше­ ния этой проблемы не существует, и именно по этой причине тип DATE стал так популя­ рен. Для иллюстрации можно предположить, что объявлен столбец odate типа СНАR. Можно определить формат для столбца odate в ограничении СНЕСК:

192

Ограиичения в таблицах

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

введенные символы цифровыми и лежат ли они в допустимых границах.

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

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

Ясно, что два различных поля используются для проверки истинности предиката.

Однако следует помнить, что здесь идет речь о двух различных полях из одной и той

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

чение СНЕСК для проверки того, что все комиссионные в пределах данного города одинаковы. Для выполнения этой проверки SQL должен обрабатывать несколько строк таблицы, даже если строка обновляется или вставляется, сравнивая комиссион­ ные для записи с таким же номером. SQL не предназначен для выполнения подобной

операции.

В данном случае можно использовать тщательно разработанное ограничение СНЕСК, если точно известны комиссионные для каждого из возможных городов. На­ пример, можно определить ограничение таким образом:

СНЕСК ( (comm=. 15 AND city="London")

Это лишь теоретическая идея. Вместо того, чтобы накладывать подобные ограни­ чения, следует принять во внимание возможность определения представ"1ения (view) с предложением WIТH СНЕСК OPТION, который имеет все эти ограничения в сво­ ем предикате. Пользователи могут осуществлять доступ к представлению, а не к таб­ лице. Внесение изменений в ограничения не является трудным. WITH СНЕСК OPТION для представлений - это хорошая альтернатива для ограничений СНЕСК

(см. главу 21).

193

Глава 18. Ограничения на м11ожество допустимых значеиий да1111ых

Присвоение значений "по умолчанию"

Если строка вставляется в таблицу и не предоставляются значения для каждого поля, SQL должен иметь значения по умолчанию для заполнения ими значений полей, не заданных явно в команде; в противном случае команда вставки должна быть от­ вергнута. Наиболее распространенным значением по умолчанию является значение NULL. Это значение является значением по умолчанию для любого столбца, если для него не указано ограничение NOT NULL, либо не указано значение, присвоенное по

умолчанию.

Назначение значений по умолчанию (DEFAULT) определяется, как и ограничения для столбца, командой CREATE TABLE, хотя значения DEFAULT не ограничивают множества значений, которые могут быть введены, а только определяют, что получает­

ся, если не указано никакое значение. Предположим, офис компании как и большинст­

во ее продавцов (salespeople) базируются в Нью-Йорке (New York). Можно принять

значение New York в качестве значения, присваиваемого по умолчанию, для таблицы Salespeople, чтобы сократить объем вводимых данных при добавлении строк в табли­

цу:

Ввод в таблицу значения New York становится элементарным всякий раз, когда на­ значается новый продавец; пропуск значений поля может стать автоматическим, даже если значение отлично от New York. Значение по умолчанию может быть желатель­ ным, если, например, номер, определяющий конкретный офис в таблице Orders, явля­ ется длинным. Длинные числовые значения могут быть причиной ошибки. Если большинство заказов (все заказы) адресовано (адресованы) в этот конкретный офис,

то предпочтительно определить его как значение, принимаемое по умолчанию.

Помимо NULL-значений, есть и другой способ использования значений по умол­ чанию. Поскольку NULL-значения представляют собой (по сути) отрицание любого значения, отличного от IS NULL, они имеют тенденцию исключения множества пре­ дикатов. Иногда требуется просмотреть значения пустых полей без выделения их каким-либо специальным образом. Можно определить специальное значение по умол­ чанию, например, пробел или нуль, которые реально меньше значения, назначаемого по умолчанию. Разница между ними и NULL-значением состоит в том, что SQL вос­ принимает их как и любое другое значение.

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

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

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

NULL:

194