Назад в раздел
InfoCity
В ГЛАВЕ 17, ВЫ УЗНАЛИ КАК СОЗДАЮТСЯ ТАБЛИЦЫ. Теперь более тщательно
с этого места мы покажем вам как вы можете устанавливать ограничения в
таблицах. Ограничения - это часть определений таблицы, которое ограни-
чивает значения которые вы можете вводить в столбцы. До этого места в
книге, единственым ограничением на значения которые вы могли вводить,
были тип данных и размер вводимых значений которые должны быть совмес-
тимы с теми столбцами в которые эти значения помещаются ( как и опре-
делено в команде CREATE TABLE или команде ALTER TABLE ). Ограничения
дают вам значительно большие возможности и скоро вы это увидете. Вы
также узнаете как определять значения по умолчанию в этой главе. По
умолчанию - это значение которое вставляется автоматически в любой
столбец таблицы, когда значение для этого столбца отсутствует в коман-
де INSERT для этой таблицы. NULL - это наиболее широко используемое
значение по умолчанию, но в этой главе будет показано как определять и
другие значения по умолчанию.
Когда вы создаете таблицу ( или, когда вы ее изменяете ), вы можете
помещать ограничение на значения которые могут быть введены в поля.
Если вы это сделали, SQL будет отклонять любые значения которые нару-
шают критерии которые вы определили. Имеется два основных типа ограни-
чений - ограничение столбца и ограничение таблицы. Различие между ними
в том, что ограничение столбца применяется только к индивидуальным
столбцам, в то время как ограничение таблицы применяется к группам из
одного и более столбцов.
Вы вставляете ограничение столбца в конец имени столбца после типа
данных и перед запятой. Ограничение таблицы помещаются в конец имени
таблицы после последнего имени столбца, но перед заключительной круг-
лой скобкой. Далее показан синтаксис для команды CREATE TABLE, расши-
ренной для включения в нее ограничения:
CREATE TABLE < table name >
(< column name > < column constraint >,
< column name > < data type > < column constraint > ...
< table constraint > ( < column name >
[, < column name > ])... );
( Для краткости, мы опустили аргумент размера, который иногда исполь-
зуется с типом данных. ) Поля данные в круглых скобках после ограниче-
ния таблицы - это поля к которым применено это ограничение. Ограниче-
ние столбца, естественно, применяется к столбцам, после чьих имен оно
следует. Остальная часть этой глава будет описывать различные типы ог-
раничений и их использование.
Вы можете использовать команду CREATE TABLE чтобы предохранить поле
от разрешения в нем пустых(NULL) указателей с помощью ограничения NOT
NULL. Это ограничение накладывается только для разнообразных столбцов.
Вы можете вспомнить что NULL - это специальное обозначение которое
отмечает поле как пустое. NULL может быть полезен, когда имеются слу-
чаи, когда вы хотите быть от них гарантированы. Очевидно, что первич-
ные ключи никогда не должны быть пустыми , поскольку это будет подры-
вать их функциональные возможности. Кроме того, такие поля как имена,
требуют в большинстве случаев, определенных значений. Например, вы ве-
роятно захотите иметь имя для каждого заказчика в таблице Заказчиков.
Если вы поместите ключевые слова NOT NULL сразу после типа данных (
включая размер ) столбца, любая попытка поместить значение NULL в это
поле будет отклонена. В противном случае, SQL понимает, что NULL раз-
решен.
Например, давайте улучшим наше определение таблицы Продавцов, не
позволяя помещать NULL значения в столбцы snum или sname :
CREATE TABLE Salespeople
( Snum integer NOT,
Sname char (10) NOT,
city char (10),
comm decimal);
Важно помнить, что любому столбцу с ограничением NOT NULL должно
быть установлено значение в каждом предложении INSERT воздействующем
на таблицу. При отсутствии NULL, SQL может не иметь значений для уста-
новки в эти столбцы, если конечно значение по умолчанию, описанное ра-
нее в этой главе, уже не было назначено.
Если ваша система поддерживает использование ALTER TABLE чтобы до-
бавлять новые столбцы к уже существующей таблице, вы можете вероятно
помещать ограничение столбцов, типа NOT NULL, для этих новых столбцов.
Однако, если вы предписываете новому столбцу значение NOT NULL, теку-
щая таблица должна быть пустой.
В Главе 17, мы обсудили использование уникальных индексов чтобы зас-
тавить поля иметь различные значения для каждой строки. Эта практика -
осталась с прежних времен, когда SQL поддерживал ограничение UNIQUE.
Уникальность - это свойство данных в таблице, и поэтому его более ло-
гично назвать как ограничение этих данных, а не просто как свойство
логического отличия, связывающее объект данных ( индекс ).
Несомненно, уникальные индексы - один из самых простых и наиболее
эффективных методов предписания уникальности. По этой причине, некото-
рые реализации ограничения UNIQUE используют уникальные индексы;
то-есть они создают индекс не сообщая вам об этом. Остается фактом,
что вероятность беспорядка в базе данных достаточно мала, если вы
предписываете уникальность вместе с ограничением.
Время от времени, вы хотите убедиться, что все значения введеные в
столбец отличаются друг от друга. Например, первичные ключи достаточно
ясно это показывают. Если вы помещаете ограничение столбца UNIQUE в
поле при создании таблицы, база данных отклонит любую попытку ввода в
это поле для одной из строк, значения, которое уже представлено в дру-
гой строке. Это ограничение может применяться только к полям которые
были обьявлены как непустые(NOT NULL), так как не имеет смысла позво-
лить одной строке таблицы иметь значение NULL, а затем исключать дру-
гие строки с NULL значениями как дубликаты. Имеется дальнейшее усовер-
шенствование нашей команды создания таблицы Продавцов :
CREATE TABLE Salespeople
( Snum integer NOT NULL UNIQUE,
Sname char (10) NOT NULL UNIQUE,
city char (10),
comm decimal );
Когда вы обьявляете поле sname уникальным, убедитесь, что две Mary
Smith будут введены различными способами - например, Mary Smith и M.
Smith. В то же время это не так уж необходимо с функциональной точки
зрения - потому что поле snum в качестве первичного ключа, все равно
обеспечит отличие этих двух строк - что проще для людей использующих
данные в таблицах, чем помнить что эти Smith не идентичны. Столбцы (
не первичные ключи ) чьи значения требуют уникальности, называются
ключами-кандидатами или уникальными ключами.
Вы можете также определить группу полей как уникальную с помощью ко-
манды ограничения таблицы - UNIQUE. Объявление группы полей уникаль-
ной, отличается от объявления уникальными индивидуальных полей, так
как это комбинация значений, а не просто индивидуальное значение, ко-
торое обязано быть уникальным. Уникальность группы - это представление
порядка, так что бы пары строк со значениями столбцов "a", "b" и "b",
"a" рассматривались отдельно одна от другой. Наша база данных сделана
так чтобы каждый заказчик был назначен одному и только одному продав-
цу. Это означает что каждая комбинация номера заказчика(cnum) и номера
продавца(snum) в таблице Заказчиков должна быть уникальной. Вы можете
убедиться в этом, создав таблицу Заказчиков таким способом:
CREATE TABLE Customers
( cnum integer NOT NULL,
cname char (10) NOT NULL,
city char (10),
rating integer,
snum integer NOT NULL,
UNIQUE (cnum, snum));
Обратите внимание что оба поля в ограничении таблицы UNIQUE все еще
используют ограничение столбца - NOT NULL . Если бы мы использовали
ограничение столбца UNIQUE для поля cnum, такое ограничение таблицы
было бы необязательным. Если значения поля cnum различно для каждой
строки, то не может быть двух строк с идентичной комбинацией значений
полей cnum и snum. То же самое получится если мы обьявим поле snum
уникальным, хотя это и не будет соответствовать нашему примеру, так
как продавец будет назначен многочисленым заказчикам. Следовательно,
ограничение таблицы - UNIQUE, наиболее полезно когда вы не хотите зас-
тавлять индивидуальные поля быть уникальными.
Предположим, например, что мы разработали таблицу чтобы следить за
всеми порядками каждый день для каждого продавца. Каждая строка такой
таблицы представляет сумму чисел любых порядков, а не просто индивиду-
альный порядок. В этом случае, мы могли бы устранить некоторые возмож-
ные ошибки убедившись что на каждый день имеется не более чем одна
строка для данного продавца, или что каждая комбинация полей snum и
odate является уникальной. Вот как например мы могли бы создать табли-
цу с именем Salestotal :
CREATE TABLE Salestotal
( cnum integer NOT NULL,
odate date NULL,
totamt decimal,
UNIQUE (snum, odate));
Кроме того, имеется команда которую вы будете использовать чтобы по-
мещать текущие данные в эту таблицу:
INSERT INTO Salestotal
SELECT snum, odate, SUM (amt)
FROM Orders
GROUP BY snum, odate;
До этого мы воспринимали первичные ключи исключительно как логичес-
кие понятия. Хоть мы и знаем что такое первичный ключ, и как он должен
использоваться в любой таблице, мы не ведаем "знает" ли об этом SQL.
Поэтому мы использовали ограничение UNIQUE или уникальные индексы в
первичных ключах чтобы предписывать им уникальность. В более ранних
версиях языка SQL , это было необходимо, и могло выполняться этим спо-
собом. Однако теперь, SQL поддерживает первичные ключи непосредственно
с ограничением Первичный Ключ ( PRIMARE KEY ). Это ограничение может
быть доступным или недоступным вашей системе.
PRIMARY KEY может ограничивать таблицы или их столбцы. Это ограниче-
ние работает так же как и ограничение UNIQUE, за исключением когда
только один первичный ключ ( для любого числа столбцов ) может быть
определен для данной таблицы. Имеется также различие между первичными
ключами и уникальностью столбцов в способе их использоваться с внешни-
ми ключами, о которых будет рассказано в Главе 19. Синтаксис и опреде-
ление их уникальности те же что и для ограничения UNIQUE.
Первичные ключи не могут позволять значений NULL. Это означает что,
подобно полям в ограничении UNIQUE, любое поле используемое в ограни-
чении PRIMARY KEY должно уже быть обьявлено NOT NULL. Имеется улучше-
ный вариант создания нашей таблицы Продавцов :
CREATE TABLE Salestotal
( snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL UNIQUE,
city char(10),
comm decimal);
Как вы видете, уникальность (UNIQUE) полей может быть обьявлена для
той же самой таблицы. Лучше всего помещать ограничение PRIMARY KEY в
поле(или в поля) которое будет образовывать ваш уникальный идентифика-
тор строки, и сохранить ограничение UNIQUE для полей которые должны
быть уникальными логически ( такие как номера телефона или поле sname
), а не для идентификации строк.
Ограничение PRIMARY KEY может также быть применено для многочислен-
ных полей, составляющих уникальную комбинацию значений. Предположим
что ваш первичный ключ - это имя, и вы имеете первое имя и последнее
имя сохраненными в двух различных полях ( так что вы можете организо-
вывать данные с помощью любого из них ). Очевидно, что ни первое ни
последнее имя нельзя заставить быть уникальным самостоятельно, но мы
можем каждую из этих двух комбинаций сделать уникальной.
Мы можем применить ограничение таблицы PRIMARY KEY для пар:
CREATE TABLE Namefield
( firstname char (10) NOT NULL,
lastname char (10) NOT NULL
city char (10),
PRIMARY KEY ( firstname, lastname ));
Одна проблема в этом подходе та, что мы можем вынудить появление
уникальности - например, введя Mary Smith и M. Smith. Это может ввести
в заблуждение, потому что ваши служащие могут не знать кто из них кто.
Обычно более надежный способ чтобы определять числовое поле которое
могло бы отличать одну строку от другой, это иметь первичный ключ, и
применять ограничение UNIQUE для двух имен полей.
Конечно, имеется любое число ограничений которые можно устанавливать
для данных вводимых в ваши таблицы, чтобы видеть, например, находятся
ли данные в соответствующем диапазоне или правильном формате, о чем
SQL естественно не может знать заранее. По этой причине, SQL обеспечи-
вает вас ограничением CHECK, которое позволяет вам установить условие
которому должно удовлетворять значение вводимое в таблицу, прежде чем
оно будет принято. Ограничение CHECK состоит из ключевого слова CHECK
сопровождаемого предложением предиката, который использует указанное
поле. Любая попытка модифицировать или вставить значение поля которое
могло бы сделать этот предикат неверным - будет отклонена.
Давайте рассмотрим еще раз таблицу Продавцов. Столбец комиссионных
выражается десятичным числом и поэтому может быть умножен непосредс-
твенно на сумму приобретений в результате чего будет получена сумма
комиссионных(в долларах) продавца с установленым справа значком долла-
ра( $ ) . Кто-то может использовать понятие процента, однако ведь,
можно об этом и не знать. Если человек введет по ошибке 14 вместо .14
чтобы указать в процентах свои комиссионные, это будет расценено как
14.0 , что является законным десятичным значением, и будет нормально
воспринято системой. Чтобы предотвратить эту ошибку, мы можем наложить
ограничение столбца - CHECK чтобы убедиться что вводимое значение
меньше чем 1.
CREATE TABLE Salespeople
( snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL UNIQUE,
city char(10),
comm decimal CHECK ( comm < 1 ));
Мы можем также использовать ограничение CHECK чтобы защитить от вво-
да в поле определенных значений, и таким образом предотвратить ошибку.
Например, предположим, что единствеными городами в которых мы имели
ведомства сбыта являются Лондон, Барселона, Сан Хосе, и Нью Йорк. Если
вам известны все продавцы работающие в каждом из этих ведомств, нет
необходимости позволять ввод других значений. Если же нет, использова-
ние ограничения может предотвратить опечатки и другие ошибки.
CREATE TABLE Salespeople
(snum integer NOT NULL UNIQUE,
sname char(10) NOT NULL UNIQUE,
city char(10) CHECK,
(city IN ('London', 'New York', 'San Jose', 'Barselona')),
comm decimal CHECK (comm < 1 ));
Конечно, если вы собираетесь сделать это, вы должны быть уверены что
ваша компания не открыла уже новых других ведомств сбыта. Большинство
программ баз данных поддерживают команду ALTER TABLE( см. Главу 17 )
которая позволяет вам изменять определение таблицы, даже когда она на-
ходится в использовании. Однако, изменение или удаление ограничений не
всегда возможно для этих команд, даже там где это вроде бы поддержива-
ется.
Если вы использовали систему которая не может удалять ограничения,
вы будете должны создавать (CREATE) новую таблицу и передавать инфор-
мацию из старой таблицы в нее всякий раз, когда вы хотите изменить ог-
раничение. Конечно же Вы не захотите делать это часто, и со временем
вообще перестанете это делать.
Создадим таблицу Порядков:
CREATE TABLE Orders
(onum integer NOT NULL UNIQUE,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL,
snum integer NOT NULL);
Как мы уже говорили в Главе 2, тип DATЕ(ДАТА) широко поддерживается,
но не является частью стандарта ANSI. Что же делать если мы используем
базу данных, которая следуя ANSI, не распознает тип DATЕ? Если мы обь-
явим поле odate любым типом числа, мы не сможем использовать наклонную
черту вправо (/) или черточку (-) в качестве разделителя. Так как пе-
чатаемые номера - это символы ASCII, мы можем обьявить тип поля odate
- CHAR. Основная проблема в том, что мы будем должны использовать оди-
ночные кавычки всякий раз, когда ссылаемся на значение поля odate в
запросе. Нет более простого решения этой проблемы там где тип DATЕ
стал таким популярным. В качестве иллюстрации, давайте обьявим поле
odate - типом CHAR. Мы можем по крайней мере наложить на него наш фор-
мат с ограничением CHECK:
CREATE TABLE Orders
( onum integer NOT NULL UNIQUE,
amt decimal,
odate char (10) NOT NULL CHECK ( odate LIKE
'--/--/----'),
cnum NOT NULL,
snum NOT NULL );
Кроме того, если вы хотите, вы можете наложить ограничение, гаранти-
рующие что введенные символы - числа, и что они - в пределах значений
нашего диапазона.
Вы можете также использовать CHECK в качестве табличного ограниче-
ния. Это полезно в тех случаях когда вы хотите включить более одного
поля строки в условие. Предположим что комиссионные .15 и выше, будут
разрешены только для продавца из Барселоны. Вы можете указать это со
следующим табличным ограничением CHECK :
CREATE TABLE Salespeople
( snum integer NOT NULL UNIQUE,
sname char (10) NOT NULL UNIQUE,
city char(10),
comm decimal,
CHECK (comm < .15 OR city = 'Barcelona'));
Как вы можете видеть, два различных поля должны быть проверены чтобы
определить, верен предикат или нет. Имейте в виду, что это - два раз-
ных поля одной и той же строки. Хотя вы можете использовать многочис-
леные поля, SQL не может проверить более одной строки одновременно. Вы
не можете например использовать ограничение CHECK чтобы удостовериться
что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL
должен всякий раз просматривая другие строки таблицы, когда вы модифи-
цируете или вставляете строку, видеть, что значение комиссионных ука-
зано для текущего города. SQL этого делать не умеет.
Фактически, вы могли бы использовать сложное ограничение CHECK для
вышеупомянутого, если бы знали заранее, каковы должны быть комиссион-
ные в разных городах. Например, вы могли бы установить ограничение ти-
па этого:
CHECK ( ( comm = .15 AND clty = 'London' )
OR ( comm = .14 AND city = 'Barcelona' )
OR ( comm = 11 AND city = 'San Jose').. )
Вы получили идею. Чем налагать такой комплекс ограничений, вы могли
бы просто использовать представление с предложением WITH CHECK OPTION
которое имеет все эти условия в своем предикате ( смотри Главу 20 и 21
для информации о представлении и о WITH CHECK OPTION). Пользователи
могут обращаться к представлению таблицы вместо самой таблицы. Одним
из преимуществ этого будет то, что процедура изменения в ограничении
не будет такой болезненной или трудоемкой. Представление с WITH CHECK
OPTION - хороший заменитель ограничению CHECK, что будет показано в
Главе 21.
Когда вы вставляете строку в таблицу без указания значений в ней для
каждого поля, SQL должен иметь значение по умолчанию для включения его
в определенное поле, или же команда будет отклонена. Наиболее общим
значением по умолчанию является - NULL. Это - значение по умолчанию
для любого столбца, которому не было дано ограничение NOT NULL или ко-
торый имел другое назначение по умолчанию.
Значение DEFAULT(ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE
тем же способом что и ограничение столбца, хотя, с технической точки
зрения, значение DEFAULT не ограничительного свойства - оно не ограни-
чивает значения которые вы можете вводить, а просто определяет, что
может случиться если вы не введете любое из них. Предположим что вы
работаете в оффисе Нью Йорка и подавляющее большинство ваших продавцов
живут в Нью Йорке. Вы можете указать Нью Йорк в качестве значения поля
city, по умолчанию, для вашей таблицы Продавцов:
CREATE TABLE Salespeople
( snum integer NOT NULL UNIQUE,
sname char(10) NOT NULL UNIQUE,
city char(10) DEFAULT = 'New York',
comm decimal CHECK (comm < 1);
Конечно, вводить значение Нью Йорк в таблицу каждый раз когда назна-
чается новый продавец, не такая уж необходимость, и можно просто пре-
небречь им ( не вводя его ) даже если оно должно иметь некоторое зна-
чение. Значение по умолчанию такого типа, более предпочтительно, чем,
например, длинный конторский номер указывающий на ваше собственное ве-
домство, в таблице Порядков. Длинные числовые значения - более распо-
ложены к ошибке, поэтому если подавляющее большинство ( или все ) ва-
ших порядков должны иметь ваш собственный конторский номер, желательно
устанавливать для них значение по умолчанию.
Другой способ использовать значение по умолчанию - это использовать
его как альтернативу для NULL. Так как NULL (фактически) неверен при
любом сравнении, ином чем IS NULL, он может быть исключен с помощью
большинства предикатов. Иногда, вам нужно видеть пустые значения ваших
полей не обрабатывая их каким-то определенным образом. Вы можете уста-
новить значение по умолчанию, типа нуль или пробел, которые функцио-
нально меньше по значению чем просто не установленное значение - пус-
тое значение(NULL). Различие между ними и обычным NULL в том, что SQL
будет обрабатывать их также как и любое другое значение.
Предположим, что заказчикам не назначены оценки изначально. Каждые
шесть месяцев, вы повышаете оценку всем вашим заказчикам, имеющим
оценку ниже средней, включая и тех кто предварительно не имел никакого
назначения оценки. Если вы хотите выбрать всех этих заказчиков как
группу, следующий запрос исключит всех заказчиков с оценкой = NULL:
SELECT *
FROM Customers
WHERE rating < = 100;
Однако, если вы назначили значение поумолчанию = 000, в поле rating,
заказчики без оценок будут выбраны наряду с другими. Приоритет каждого
метода - зависит от ситуации. Если вы будете делать запрос с помощью
поля оценки, то захотите ли Вы включить строки без значений, или иск-
лючите их?
Другая характеристика значений по умолчанию этого типа, позволит
обьявить Вам поле оценки - как NOT NULL. Если вы используете его поу-
молчанию, чтобы избежать значений = NULL, то это - вероятно хорошая
защита от ошибок.
Вы можете также использовать ограничения UNIQUE или PRIMARY KEY в
этом поле. Если вы сделаете это, то, имеете в виду, что только одна
строка одновременно может иметь значение по умолчанию. Любую строку
которая содержит значение по умолчанию нужно будет модифицировать
прежде, чем другая строка с установкой по умолчанию будет вставлена.
Это не так как вы обычно используете значения по умолчанию, поэтому
ограничения UNIQUE и PRIMARY KEY ( особенно последнее ) обычно не ус-
танавливаются для строк со значениями по умолчанию.
Вы теперь владеете несколькими способами управления значениями кото-
рые могут быть введены в ваши таблицы. Вы можете использовать ограни-
чение NOT NULL чтобы исключать NULL, ограничение UNIQUE чтобы вынуж-
дать все значения в группе из одного или более столбцов отличаться
друг от друга, ограничение PRIMARY KEY, для того чтобы делать в основ-
ном то же самое что и UNIQUE но с различным окончанием, и наконец ог-
раничение CHECK для определения ваших собственных сделанных на заказ
условий, чтобы значения встреченные перед ними могли бы быть введены.
Кроме того, вы можете использовать предложение DEFAULT, которое будет
автоматически вставлять значение по умолчанию в любое поле с именем не
указаным в INSERT, так же как вставляется значение NULL когда предло-
жение DEFAULT не установлено и отсутствует ограничение NOT NULL.
FOREIGN KEY или REFERENCES ограничения о которых вы узнаете в Главе
19 очень похожи на них, за исключением того, что они связывают группу
из одного или более полей с другой группой, и таким образом сразу воз-
действуют на значения которые могут быть введены в любую из этих
групп.
1. Создайте таблицу Порядков так чтобы все значения поля onum, а также
все комбинации полей cnum и snum отличались друг от друга, и так
что бы значения NULL исключались из поля даты.
2. Создайте таблицу Продавцов так чтобы комиссионные, по умолчанию,
составляли 10%, не разрешались значения NULL, чтобы поле snum явля-
лось первичным ключом, и чтобы все имена были в алфавитном порядке
между A и M включительно( учитывая, что все имена будут напечатаны
в верхнем регистре ).
3. Создайте таблицу Порядков, будучи уверенными в том что поле onum
больше чем поле cnum, а cnum больше чем snum. Запрещены значения
NULL в любом из этих трех полей.
|
|
 |
|
