Глава 18

 S Q L

ОГРАНИЧЕНИЕ ЗНАЧЕНИЙ ВАШИХ ДАННЫХ

В ГЛАВЕ 17, ВЫ УЗНАЛИ КАК СОЗДАЮТСЯ ТАБЛИЦЫ. Теперь более тщательно с этого места мы покажем вам как вы можете устанавливать ограничения в таблицах. Ограничения - это часть определений таблицы, которое ограничивает значения которые вы можете вводить в столбцы. До этого места в книге, единственным ограничением на значения которые вы могли вводить, были тип данных и размер вводимых значений которые должны быть совместимы с теми столбцами в которые эти значения помещаются ( как и определено в команде CREATE TABLE или команде ALTER TABLE ). Ограничения дают вам значительно большие возможности и скоро вы это увидите. Вы также узнаете как определять значения по умолчанию в этой главе. По умолчанию - это значение которое вставляется автоматически в любой столбец таблицы, когда значение для этого столбца отсутствует в команде INSERT для этой таблицы. NULL - это наиболее широко используемое значение по умолчанию, но в этой главе будет показано как определять и другие значения по умолчанию.

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

ОБЪЯВЛЕНИЕ ОГРАНИЧЕНИЙ

      CREATE TABLE < table name > 
         (< column name >
         < column constraint >, 
         < column name > < data type > < column constraint > ... 
         < table constraint > ( < column name > 
         [, < column name > ])... ); 
 
 

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

ИСПОЛЬЗОВАНИЕ ОГРАНИЧЕНИЙ ДЛЯ ИСКЛЮЧЕНИЯ ПУСТЫХ( NULL ) УКАЗАТЕЛЕЙ

       CREATE TABLE Salespeople 
          ( Snum     integer NOT, 
            Sname   char (10) NOT, 
            city      char (10), 
            comm   decimal); 
 

Если ваша система поддерживает использование 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; 
 

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 для двух имен полей.

ПРОВЕРКА ЗНАЧЕНИЙ ПОЛЕЙ

       CREATE TABLE Salespeople 
          ( snum     integer NOT NULL PRIMARY KEY, 
            sname    char(10) NOT NULL UNIQUE, 
            city       char(10), 
            comm     decimal CHECK ( comm < 1 )); 
 

    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, мы можем объявить тип поля date - 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.

УСТАНОВКА ЗНАЧЕНИЙ ПО УМОЛЧАНИЮ

       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); 
 

         SELECT * 
            FROM Customers 
            WHERE rating < = 100; 
 

Вы можете также использовать ограничения UNIQUE или PRIMARY KEY в этом поле. Если вы сделаете это, то, имеете в виду, что только одна строка одновременно может иметь значение по умолчанию. Любую строку которая содержит значение по умолчанию нужно будет модифицировать прежде, чем друга строка с установкой по умолчанию будет вставлена. Это не так как вы обычно используете значения по умолчанию, поэтому ограничения UNIQUE и PRIMARY KEY ( особенно последнее ) обычно не устанавливаются для строк со значениями по умолчанию.

РЕЗЮМЕ

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

РАБОТА С SQL

( См. Приложение A для ответов. )

 > НАЗАД <