Определение правил и умолчаний для данных

Определение правил и умолчаний для данных

 

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

В этой главе рассматриваются следующие вопросы:

 

 

 

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

В системах управления реляционными базами данных каждый элемент базы, например, определенный столбец в определенной строке, должен иметь какое-либо значение, даже если это неопределенное значение NULL. Как уже говорилось в главе 7 “ Создание баз данных и таблиц”, в некоторые столбцы нельзя записывать неопределенное значение. В такие столбцы пользователь должен ввести определенное значение, либо по умолчанию это должен сделать  SQL Сервер.

Значения по умолчанию, определенные пользователем, позволяют SQL Серверу автоматически записывать в поле некоторое значение, если для этого поля явно не было указано никакого значения, независимо от того допускает ли это поле неопределенные значения или нет. Например, можно определить по умолчанию значение “???” или значение “заполнить позже”.

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

Каждый раз, когда пользователь записывает в столбец таблицы данные с помощью операторов insert и update, SQL сервер проверяет, соответствуют ли они правилу, которое связано с этим столбцом. Данные, которые были записаны в табличный столбец до введения и присоединения к нему правила, не проверяются на соответствие этому правилу.

 

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

 

В данной главе объясняется, как создавать правила и умолчания с помощью команд create default (создать умолчание) и create rule (создать правило), а также как связывать правила и умолчания к столбцу или к типу данных пользователя с помощью системных процедур sp_bindefault (присоединить умолчание), sp_bindrule (присоединить правило), sp_unbindefault (отсоединить умолчание), sp_unbindrule (отсоединить правило).

 

 

Вместо создания правил и умолчаний, можно  сразу в операторе создания таблицы create table указать конструкцию default (умолчание) и опцию check (проверка), которые выполняют аналогичную роль. Однако, эти  правила и умолчания будут действовать только для одной таблицы и не могут быть связаны со столбцами других таблиц или типами данных пользователя. Подробная информация об условиях сохранения целостности данных в отдельной таблице содержится в главе 7 “Создание баз данных и таблиц”.

 

 

Умолчания могут создаваться и удаляться в любой момент, до или после введения данных в таблицу. Значения по умолчанию создаются командой create default (создать умолчание), а удаляются с командой drop default (удалить умолчание).

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

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

 

 

 

Синтаксис команды create default выглядит следующим образом:

 

create default [имя_владельца.]название_умолчания

       as константное_выражение

 

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

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

Здесь будет показано, как создать значение по умолчанию “Oakland”, которое использовалось в столбце city таблицы friends_etc (создание этой таблицы рассматривалось в главе 7 “Создание баз данных и таблиц”) и которое возможно будет полезно для других столбцов или типов данных. Во время работы с этим примером можно использовать название любого другого города, которое имеет смысл для того контингента людей, сведения о которых планируется хранить в таблице. Для создания этого умолчания нужно выполнить команду:

 

create default citydflt

as “Oakland”

 

После слова as можно задать любую константу. Символьные константы и даты должны быть заключены в кавычки; для денежных, целых и числовых констант с плавающей точкой кавычки не обязательны.  Двоичным данным должен предшествовать символ 0х, а денежным данным - символ доллара ($). Значение по умолчанию должно соответствовать типу данных, установленному для столбца. Например, нельзя использовать в качестве значения по умолчанию строку “none” для столбца числового типа, а число 0 (ноль) можно.  

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

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

 

 

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

 

create default phonedflt as “UNKNOWN”

 

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

 

sp_bindefault phonedflt, “authors.phone”

 

Это значение по умолчанию будет записываться в таблицу authors (авторы) только в том случае, если для столбца phone (телефон) не указано никакого значения. Таким образом, отсутствие данных в столбце не обязательно приводит к появлению в нем неопределенного значения.

 

Замечание: Чтобы в столбец по умолчанию записалось определенное значение необходимо выполнить команду insert или update со списком столбцов, в котором нет этого столбца.

 

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

Ниже показано, как присоединить умолчание citydflt к столбцу city таблицы friends_etc:

 

sp_bindefault citydflt, “friends_etc.city”

 

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

 

                             Замечание: Умолчания нельзя присоединить к системным типам данным, поскольку неясно какие значения потребуются различным пользователям. Также не допускается присоединение умолчаний к столбцам типа timestamp (моменты времени), поскольку SQL Сервер автоматически записывает значения в эти столбцы. Можно связывать умолчания со столбцами типа IDENTITY (счетчик) или с типами данных, определенными пользователем и имеющими свойство IDENTITY, но такие умолчания игнорируются. Каждый раз когда  вводится новая строка без указания значения для счетчика, SQL Сервер записывает в этот столбец значение, которое на единицу больше предыдущего.

 

Если создать специальный тип данных для всех столбцов city каждой таблицы базы данных, то можно присоединить умолчание citydflt   к этому типу данных и быть уверенным в том, что значение по умолчанию “Oakland” будет заменять значения в тех столбцах, где это необходимо. Например, если пользовательский тип данных называется citytype, то присоединить к этому типу данных умолчание citydflt можно следующим образом:

 

sp_bindefault citydflt, citytype

 

Параметр futureonly (только в будущем) может быть использован при связывании умолчания с пользовательским типом данных. Этот параметр предотвращает изменение существующих значений в столбцах этого типа, записанных по умолчанию, на новое значение. Параметр futureonly никогда не используется при связывании умолчания со столбцом таблицы. В следующем примере показано, как создать и присоединить новое значение по умолчанию “Berkeley” к типу данных citytype таким образом, чтобы оно действовало только для вновь создаваемых столбцов. Предыдущее значение по умолчанию “Oakland” будет по-прежнему находиться в уже существующих столбцах типа citytype.

 

create default newcitydflt as “Berkeley”

sp_bindefault newcitydflt, citytype, futureonly

 

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

 

create default zipdflt as “94609”

sp_bindefault zipdflt, “friends_etc.postalcode”

 

Полный синтаксис вызова системной процедуры sp_bindefault имеет следующий вид:

 

sp_bindefault название_умолчания, название_объекта  [, futureonly]

 

Название_умолчания это название умолчания, указанное при его создании процедурой create default. Название_объекта это название столбца таблицы или пользовательского типа данных, с которым связывается данное умолчание. Если это название не имеет вида таблица.столбец, то оно воспринимается как название типа данных.

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

 

 

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

 

Таблица 12-1: Предшествование умолчаний

 

	Прежнее умолчание связано с:
Новое умолчание связано с:	Пользовательским типом	Табличным столбцом
Пользовательским типом	Новое значение заменяет прежнее.	Нет изменений. Действует прежнее.
Табличным столбцом	Новое значение заменяет прежнее.	Новое значение заменяет прежнее.

 

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

Например, предположим, что пользователь создал таблицу foes со столбцом city, который имеет пользовательский тип citytype. Сначала тип citytype не имел значений по умолчанию. После создания умолчания citydflt,  оно автоматически связывается со столбцом foes.city. Затем пользователь связывает другое умолчание newcitydflt с типом citytype. Хотя foes.city является столбцом типа citytype, новое умолчание не будет с ним связано, поскольку значение по умолчание для этого столбца было предварительно изменено.

 

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

 

 

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

В следующем примере показано, как отсоединить значение по умолчанию от столбца city таблицы friends_etc:

 

execute sp_unbindefault “friends_etc.city”

 

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

Для отсоединения умолчания от пользовательского типа данных citytype нужно выполнить следующую команду:

 

sp_unbindefault citytype

 

Полный синтаксис вызова системной процедуры sp_unbindefault имееи следующий вид:

 

sp_unbindefault название_объекта [, futureonly]

 

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

 

 

Ниже приведен пример, илюстрирующий описанные выше условия:

 

 

 

Для удаления умолчаний из базы данных используется команда drop default (удалить умолчание). Перед удалением умолчание должно быть отсоединено от всех столбцов и пользовательских типов данных. Если попытаться удалить умолчание, которое не отсоединено, то SQL Сервер выдаст сообщение об ошибке и команда drop default не будет выполнена. Однако для того, чтобы присоединить новое умолчание, не обязательно удалять прежнее умолчание из базы. Достаточно просто присоединить новое умолчание на его место.

В следующем примере показано, как удалить умолчание citydflt из базы:

 

drop default citydflt

 

Полный синтаксис команды drop default выглядит следующим образом:

 

drop default [владелец.]название _умолчания

       [, [владелец.]название_умолчания]...

 

Умолчание может быть удалено только его владельцем.

 

 

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

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

Следующая таблица показывает соотношение между существованием умолчания и определением столбца с опциями NULL или NOT NULL. Результаты указаны в таблице:

 

Таблица 12-2: Умолчания и неопределенные значения

 

Определение столбца	Нет данных и умолчания	Нет данных, но есть умолчание	Введено NULL и нет умолчания	Введено NULL и есть умолчание
NULL	Null	Значение по умолчанию	Null	Null
NOT NULL	Ошибка	Значение по умолчанию	Ошибка	Ошибка

 

 

Правила создаются с помощью команды create rule (создать правило), а затем присоединяются к столбцу или пользовательскому типу данных с помощью системной процедуры sp_binderule (присоединить правило). Отсоединение правила от столбца или пользовательских типа данных проводится с помощью системной процедуры sp_unbindrule (отсоединить правило) или путем присоединения нового правила к столбцу или пользовательскому типу.

 

 

Синтаксис команды create rule имеет следующий вид:

 

create rule [владелец. ]название_правила

       as условное_выражение

 

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

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

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

 

create rule pub_idrule

as @pub_id in (“1389”, “0736”, “0877”, “1622”, “1756”)

or @pub_id like “99[0-9] [0-9]”

 

Конструкция as содержит название аргумента правила, которому предшествует символ “@”, и непосредственно определение правила.  Аргумент ссылается на значение в столбце, которое может изменяться операторами update и insert.

В предыдущем примере аргумент называется @pub_id, чтобы подчеркнуть, что это правило будет связано со столбцом pub_id. Для аргумента можно использовать любое имя, но первым символом обязательно должен быть символ “@”. Использование в качестве названия аргумента названия столбца или пользовательского типа данных, к которому это правило присоединяется, позволяет легче запомнить, для чего оно предназначено.

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

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

 

create rule phonerule

as @phone like ‘415_ _ _ _ _ _ _’

 

Следующее правило устанавливает, что возраст друзей пользователя может изменяться от 1 до 120, но не равен 17:

 

create rule agerule

as @age between 1 and 120 and @age !=17  

 

 

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

Полный синтаксис процедуры sp_bindrule выглядит так:

 

sp_binderule название_правила, название_объекта [, futureonly]

 

Параметр название_правила это название того правила, которое было создано с помощью процедуры create rule. Параметр название_объекта это название столбца или пользовательского типа, к которому это правило будет присоединено. Если этот параметр не имеет вида таблица.столбец, то он воспринимается как название пользовательского типа данных.

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

 

Замечание: Не допускается присоединение правил к столбцам типа text, image, и timestamp.

 

 

Правило присоединяется к столбцу с помощью процедуры sp_binderule, которой в качестве параметров передается название правила, а также  название таблицы и столбца, заключенные в кавычки. Ниже показано, как присоединить правило pub_idrule к столбцу publishers pub_id:

 

sp_binderule pub_idrule, “publishers.pub_id”

 

Другой пример показывает, как создать правило, которое допускает  только почтовые коды, начинающиеся с цифр 946:

 

create rule postalcoderule946

as @postalcode like “946[0-9] [0-9]”

 

Связывание этого правила со столбцом postalcode таблицы friends_etc выглядит следующим образом:

 

sp_binderule postalcoderule946, “friends_etc.postalcode”

 

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

 

 

Правила нельзя связывать с системными типами данных, т.е. их можно присоединять только к пользовательским типам данных. Например, чтобы связать правило phonerule с пользовательским типу p#, омужно выполнить команду:

 

sp_binderule, “p#”

 

 

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

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

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

 

Таблица 12-3: Старшинство правил

 

Новое правило связывается с:	Прежнее правило связано с:
	Пользовательским типом	Табличным столбцом
Пользовательским типом	Заменяет прежнее правило.	Нет изменений. Действует прежнее.
Табличным столбцом	Заменяет прежнее правило.	Заменяет прежнее правило.

 

При введении данных в табличные столбцы, которые требуют особых временных ограничений, можно сделать новое правило для проверки этих данных. Например, предположим, пользователю нужно добавить данные о величине долгов в столбец debt (долг) таблицы friends_etc. Ему известно, что все долги, которые нужно зафиксировать, находятся в пределах от $5 до $200. Чтобы избежать случайных ошибок и не допустить чисел, выходящих из этого диапазона, можно сделать следующее правило. (Это правило позволяет вводить нулевое значение $0.00, которая ранее было определено как значение по умолчанию для этого столбца.)

 

create rule debtrule

as @debt = $0.00 or @debt between $5.0 and $200.00

 

Правило debtrule связывается со столбцом debt следующим образом:

 

sp_bindrule debtrule, “friends_etc.debt”

 

Замечание: После создания и присоединения правила нужно обязательно проверить его, записав в таблицу некоторые данные. Многие ошибки, связанные с созданием и присоединением правил, могут быть обнаружены только с помощью операторов insert и update.

 

 

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

Существуют два способа отсоединить правило:

 

 

Ниже показано, как отсоединить правило debtrule (или любое другое связанное правило) от столбца friends_etc.debt: 

 

sp_unbindrule “friends_etc.debt”

 

После выполнения этой команды правило debtrule по-прежнему остается в базе данных, но от столбца friends_etc.debt оно уже отсоединено.

Для отсоединения правила от пользовательского типа p# нужно выполнить следующую команду:

 

sp_unbinderule “p#”

 

Полный синтаксис вызова системной процедуры sp_unbinderule выглядит следующим образом:

 

sp_unbindrule название_объекта [, futureonly]

 

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

 

 

 

Для удаления правил из базы данных используется команда drop rule (удалить правило). Перед удалением правило должно быть отсоединено от всех столбцов и типов данных. Если попытаться удалить связанное правило, то SQL Cервер выдаст сообщение об ошибке и команда drop rule не будет выполняться. Однако для того, чтобы присоединить новое правило, не обязательно отсоединять и удалять прежнее правило. Достаточно просто присоединить новое правило на место прежнего.

Ниже показано, как удалить правило phonerule после его отсоединения:

 

drop rule phonerule

 

Полный синтаксис команды drop rule выглядит следующим образом:

 

drop rule [владелец.]название_правила

       [, [владелец.]название_правила] ...

 

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

Правило может быть удалено только ее владельцем.

 

 

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

 

sp_help authors

 

С помощью системной процедуры sp_help можно также получить информацию о правилах, связанных с пользовательскими типами данных. В следующем примере проверяется, имеет ли тип данных “p#”, связанные с ним  правило:

 

sp_help “p#”

 

Процедура sp_helptext выдает текст определения (т.е. оператора create) правила или умолчания.