Введение

В этом разделе описываются различия в структуре SQL-процедур DB2 и solidDB.

Ограничения в DB2 для платформ Linux, UNIX и Windows (DB2 LUW) используются для обеспечения выполнения бизнес-правил для данных. В этой статье рассматриваются следующие типы ограничений:

• NOT NULL
• Unique
• Primary key
• Foreign key
• Table check

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

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

Проще и удобнее всего управлять ограничениями через центр управления DB2 Control Center.

Определения ограничений связаны с базой данных, для которой они создаются, и хранятся в каталоге базы данных в соответствии с таблицей 1. Для получения этой информации вы можете выполнить запрос к каталогу базы данных. Это можно сделать напрямую из командной строки (не забудьте сначала установить соединение с базой данных) или же более удобным способом – через DB2 Control Center.

Созданные вами ограничения обрабатываются так же, как и другие объекты базы данных. Они имеют имена, ассоциированную схему (creator ID), и в некоторых случаях могут быть удалены.

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

В начало

"Нельзя быть ничем!" – ограничение NOT NULL

Ограничение NOT NULL предотвращает добавление в столбец null-значений. Благодаря этому для каждой строки таблицы в столбце всегда присутствует какое-либо действительное значение. Например, в определении таблицы EMPLOYEE базы данных SAMPLE используется ограничение LASTNAME VARCHAR(15) NOT NULL, благодаря чему каждая строка таблицы содержит фамилию сотрудника.

Чтобы определить, может ли столбец таблицы содержать null-значения, вы можете обратиться к языку определения данных (Data Definition Language, DDL), который можно сгенерировать с помощью утилиты db2look. Также вы можете использовать DB2 Control Center, как показано на рисунках 3 и 4.

DB2 Control Center предоставляет вам удобный доступ к объектам базы данных – например, к таблицам. На рисунке 3 показаны пользовательские таблицы в базе данных SAMPLE. Перечень таблиц можно увидеть на панели содержимого, выбрав узел Tables в дереве объектов. Если вы выберете таблицу STAFF, вы сможете открыть окно Alter Table и посмотреть определение таблицы, включая атрибуты столбцов, как показано на рисунке 4.

Кроме того, вы можете выполнить запрос к каталогу базы данных, как показано в листинге 1.

db2 select tabname, colname, nulls
    from syscat.columns
    where tabschema = 'DELSVT' and nulls = 'N'

В начало

"Только для единственных" – ограничение unique

Ограничение unique предотвращает появление нескольких одинаковых значений в определенном столбце таблицы. Также оно предотвращает появление нескольких одинаковых наборов значений в определенном наборе столбцов. Столбцы, на которые ссылается ограничение unique, должны быть определены как NOT NULL. Ограничение unique может быть определено в операторе CREATE TABLE с помощью выражения UNIQUE (рисунок 1 и рисунок 2) или в операторе ALTER TABLE, как показано в листинге 2.

В листинге 2 показано, как создать ограничение unique. Таблица ORG_TEMP идентична таблице ORG в базе данных SAMPLE за исключением того, что столбец LOCATION в таблице ORG_TEMP не может содержать null-значений, и поэтому для него можно задать ограничение unique.

db2 create table org_temp (
    deptnumb smallint not null,
    deptname varchar(14),
    manager smallint,
    division varchar(10),
    location varchar(13) not null)

db2 alter table org_temp add unique (location)

db2 insert into org_temp
    values (10, 'Head Office', 160, 'Corporate', 'New York')

DB20000I  The SQL command completed successfully.

db2 insert into org_temp
    values (15, 'New England', 50, 'Eastern', 'New York')

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DELSVT.ORG_TEMP" from having duplicate values for the index key.
SQLSTATE=23505

Ограничение unique помогает обеспечивать целостность данных, предотвращая случайное дублирование значений. В примере из листинга 2 ограничение unique предотвращает вставку второй записи, которая содержит имя New York в качестве имени филиала организации. Ограничение unique приводится в действие через уникальный индекс.

В начало

"Мы – номер один!" – ограничение primary key

Ограничение primary key обеспечивает уникальность значений в столбце или наборе столбцов, образующих первичный ключ. Первичный ключ используется для идентификации определенных строк в таблице. Таблица не может содержать более одного первичного ключа, но может содержать несколько уникальных ключей. Ограничение primary key является особым случаем ограничения unique и приводится в действие через первичный индекс.

Столбцы, на которые ссылается ограничение primary key, должны быть определены как NOT NULL. Ограничение primary key может быть определено в операторе CREATE TABLE с помощью выражения PRIMARY KEY (рисунок 1 и рисунок 2) или в операторе ALTER TABLE, как показано в листинге 3.

В листинге 3 показано, как создать ограничение primary key. Столбец ID в таблице STAFF не может содержать null-значений, и поэтому для него можно задать ограничение primary key.

db2 alter table staff add primary key (id)

Вы также можете создать ограничение primary key для таблицы с помощью DB2 Control Center, как показано на рисунках 5 и 6. В окне Alter Table выберите вкладку Keys и нажмите кнопку Add Primary.

В результате откроется окно Define Primary Key, изображенное на рисунке 6.

Окно Define Primary Key позволяет вам выбрать один или несколько столбцов из списка доступных столбцов Available columns. Нажмите кнопку >, чтобы переместить нужные столбцы из списка Available column в список Selected columns. Обратите внимание на то, что выбранные столбцы не должны быть null-столбцами.

В начало

"Все относительно" – ограничение foreign key

Ограничение foreign key иногда называют ограничением на ссылки (referential constraint). Ссылочная целостность определяется как состояние базы данных, в котором все значения всех внешних ключей действительны. Что же такое внешний ключ? Внешний ключ – это столбец или набор столбцов в таблице, значения которых должны совпадать как минимум с одним первичным или уникальным ключом строки в ее родительской таблице. Что это означает? На самом деле все не так плохо, как это звучит. Это попросту означает, что если столбец C2 в таблице T2 содержит значения, совпадающие со значениями столбца C1 другой таблицы (T1), и C1 является столбцом первичного индекса таблицы T1, то столбец C2 в таблице T2 является внешним ключом. Таблица, содержащая родительский ключ (первичный или уникальный ключ) называется родительской таблицей, а таблица, содержащая внешний ключ – зависимой таблицей. Давайте рассмотрим следующий пример.

В таблице PROJECT базы данных SAMPLE содержится столбец под названием RESPEMP. Значения этого столбца отражают номера сотрудников, ответственных за каждый из проектов, перечисленных в таблице. Столбец RESPEMP не является null-столбцом. Поскольку этот столбец соответствует столбцу EMPNO таблицы EMPLOYEE, и столбец EMPNO теперь является первичным ключом для таблицы EMPLOYEE, столбец RESPEMP может быть определен как внешний ключ в таблице PROJECT, как показано в листинге 4. Это гарантирует нам, что при удалении данных из таблицы EMPLOYEE в таблице PROJECT не останется несуществующих ответственных сотрудников.

Ограничение foreign key может быть определено в операторе CREATE TABLE с помощью выражения FOREIGN KEY (рисунок 1 и рисунок 2) или в операторе ALTER TABLE, как показано в листинге 4.

db2 alter table project add foreign key (respemp) references employee on delete cascade

В этом ограничении выражение REFERENCES указывает на родительскую таблицу. Синтаксис для определения ограничения foreign key содержит правило, в котором вы можете указать DB2, каким образом должны выполняться операции обновления или удаления, исходя из условий ссылочной целостности (рисунок 1).

Операции вставки (insert) выполняются стандартным образом, и вы не можете управлять этим. Правило вставки (insert rule), указанное в ограничении foreign key, означает, что вставляемое значение внешнего ключа должно совпадать с каким-либо значением родительского ключа родительской таблицы. Это соответствует тому, о чем уже было сказано. Если в таблицу PROJECT вставляется новая запись, то эта запись должна содержать ссылку (через отношение родитель–внешний ключ) на существующую запись в таблице EMPLOYEE.

Правило обновления (update rule), указанное в ограничении foreign key, означает, что новое значение внешнего ключа при обновлении должно совпадать с каким-либо значением родительского ключа родительской таблицы, и что все значения внешнего ключа должны иметь соответствующие значения родительского ключа на момент завершения операции обновления родительского ключа. Опять же, это означает, что не может существовать зависимых записей, не имеющих родительских записей.

Правило удаления (delete rule), указанное в ограничении foreign key, применяется в момент удаления строки из родительской таблицы и зависит от того, какая опция была указана при определении ограничения на ссылки (таблица 2).

В листинге 5 рассмотрены некоторые примеры.

db2 update employee set empno = '350' where empno = '000200'
DB20000I  The SQL command completed successfully.

db2 update employee set empno = '360' where empno = '000220'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0531N  The parent key in a parent row of relationship
"DELSVT.PROJECT.FK_PROJECT_2" cannot be updated.  SQLSTATE=23504

db2 "select respemp from project where respemp < '000050' order by respemp"

RESPEMP
-------
000010
000010
000020
000030
000030

  5 record(s) selected.

db2 delete from employee where empno = '000010'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0532N  A parent row cannot be deleted because the relationship
"DELSVT.PROJECT.FK_PROJECT_2" restricts the deletion.  SQLSTATE=23001

db2 "select empno from employee where empno < '000050' order by empno"

EMPNO
------
000010
000020
000030

  3 record(s) selected.

Значение 000200 столбца EMPNO родительской таблицы (EMPLOYEE) можно изменить, поскольку не существует значения 000200 столбца RESPEMP в зависимой таблице (PROJECT). Однако для значения 000220 столбца EMPNO существуют совпадающие значения внешнего ключа в таблице PROJECT, и поэтому его нельзя обновить. Правило удаления, в котором была указана опция RESTRICT, приводит к тому, что строки, содержащие значение 000010 первичного ключа, не могут быть удалены из таблицы EMPLOYEE, если связанная по удалению таблица PROJECT содержит совпадающие с ними значения внешнего ключа.

В начало

"Проверяем снова и снова" – ограничение table check

Ограничение table check накладывает определенные ограничения на данные при их добавлении в таблицу. Например, ограничение table check может обеспечить нам то, что каждый раз при добавлении или обновлении данных в таблице EMPLOYEE длина телефонного номера сотрудника будет составлять ровно четыре цифры. Ограничение table check может быть определено в операторе CREATE TABLE с помощью выражения CHECK (рисунок 1 и рисунок 2) или в операторе ALTER TABLE, как показано в листинге 6.

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

Ограничение PHONENO_LENGTH обеспечивает то, что телефонные номера, добавляемые в таблицу EMPLOYEE, состоят в точности из четырех цифр.

Для создания ограничения table check вы также можете использовать DB2 Control Center, как показано на рисунке 7.

Для задания нового ограничения нажмите кнопку Add, в результате чего откроется окно Add Check Constraint. Чтобы изменить существующее ограничение, выберите его из списка и нажмите кнопку Change, как показано на рисунке 8.

Вы не сможете создать ограничение table check, если существующие строки таблицы содержат значения, нарушающие правила этого ограничения. Это показано на рисунке 9. Вы сможете добавить или изменить ограничение после того, как все несоответствующие записи будут обновлены в соответствии с требуемыми правилами.

С помощью оператора SET INTEGRITY можно включать или отключать ограничения table check. Это может оказаться полезным, например, при оптимизации производительности во время операций загрузки больших объемов данных в таблицу. В листинге 7 приведен простой сценарий использования оператора SET INTEGRITY. В этом примере телефонный номер сотрудника с номером 000100 обновляется до значения 123, после чего проверка целостности таблицы EMPLOYEE отключается. Затем для таблицы EMPLOYEE создается ограничение, требующее, чтобы телефонный номер состоял из четырех цифр, и создается таблица EMPL_EXCEPT, идентичная таблице EMPLOYEE. Далее включается проверка целостности, и строки, нарушающее ограничение table check, перемещаются в таблицу исключений (EMPL_EXCEPT). Запросы, выполненные для этих двух таблиц, показывают нам, что несоответствующие ограничению записи существуют только в таблице исключений.

db2 update employee set phoneno = '123' where empno = '000100'

db2 set integrity for employee off

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

db2 create table empl_except like employee

db2 set integrity for employee immediate checked for exception in employee use empl_except
SQL3602W  Check data processing found constraint violations and moved them to
exception tables.  SQLSTATE=01603

db2 select empno, lastname, workdept, phoneno from empl_except

EMPNO  LASTNAME        WORKDEPT PHONENO
------ --------------- -------- -------
000100 SPENSER         E21      123

  1 record(s) selected.

В начало

Заключение

В этой статье были рассмотрены различные типы ограничений, поддерживаемые СУБД DB2 для платформ Linux, UNIX и Windows – ограничения NOT NULL, unique, primary key, foreign key (ограничение на ссылки) и table check. Ограничения в DB2 используются для выполнения бизнес-правил и для обеспечения целостности базы данных. Также вы узнали, как использовать для работы с ограничениями (и для выполнения запросов к каталогу базы данных) командную строку и DB2 Control Center.

Ресурсы

Научиться

• Оригинал статьи DB2 Basics: Constraints (EN).
  
  
• На сайте IBM Support Portal вы можете найти такие ресурсы, как DB2 Information Center (версий 9.1, 9.5 и 9.7) и руководства по продукту в формате PDF (EN).
  
  

Получить продукты и технологии

• Загрузите бесплатную ознакомительную версию DB2 9.7 для платформ Linux, UNIX и Windows (EN).
  
  
• Загрузите DB2 Express-C 9.7 – бесплатную версию сервера БД DB2 Express для сообщества (EN).
  
  

Обсудить

• Примите участие в обсуждении материала на форуме.
  
  

Об авторе

Леон Квок (Leon Kwok) является членом группы расширенной поддержки IBM DB2 для Linux, UNIX и Windows и специализируется в области механизмов БД.

Оценить эту статью

Комментарии

В начало

Содержание

• Введение
• "Нельзя быть ничем!" – ограничение NOT NULL
• "Только для единственных" – ограничение unique
• "Мы – номер один!" – ограничение primary key
• "Все относительно" – ограничение foreign key
• "Проверяем снова и снова" – ограничение table check
• Заключение
• Ресурсы
• Об авторе
• Комментарии