Ограничения (constraints) в СУБД IBM® DB2® для платформ Linux®, UNIX® и Windows® (DB2) призваны обеспечить выполнение бизнес-правил для данных и помочь сохранить целостность базы данных. В этой статье рассматриваются различные типы ограничений, поддерживаемых DB2, а также приводятся примеры для каждого типа ограничений. В дополнение к этому автор разъясняет основы управления ограничениями (с помощью командной строки или центра управления DB2 Control Center). [Прим. ред.: эта статья обновлена в соответствии с версией DB2 9.7 и выше.]

Введение

В этом разделе описываются различия в структуре 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.

Рисунок 1. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограничений
Рисунок 1. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограниченийРисунок 1. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограниченийРисунок 1. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограничений

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

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

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

Рисунок 2. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограничений (продолжение)
Рисунок 2. Фрагменты синтаксиса оператора CREATE TABLE, содержащие выражения для задания ограничений (продолжение)

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

Таблица 1. Информация об ограничениях в каталоге базы данных
Представление каталогаСтолбец представленияОписаниеПример запроса
SYSCAT.CHECKSСодержит по одной записи для каждого ограничения table checkdb2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKSСодержит по одной записи для каждого столбца, на которое ссылается ограничение table checkdb2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNSNULLSПоказывает, может ли столбец содержать null-значения (Y) или нет (N)db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'
SYSCAT.CONSTDEPСодержит по одной записи для каждой зависимости ограничения от другого объектаdb2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXESСодержит по одной записи для каждого индексаdb2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'
SYSCAT.KEYCOLUSEСодержит по одной записи для каждого столбца в ключе, определенном ограничениями unique, primary key или foreign keydb2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCESСодержит по одной записи для каждого ограничения на ссылкиdb2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONSTСодержит по одной записи для каждого ограничения unique (U), primary key (P), foreign key (F) или table check (K)db2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLESPARENTSКоличество родительских таблиц данной таблицы (количество ограничений на ссылки, в которых эта таблица является зависимой)db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLESCHILDRENКоличество зависимых таблиц для данной таблицы (количество ограничений на ссылки, в которых эта таблица является родительской)db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLESSELFREFSКоличество самоссылающихся ограничений на ссылки для данной таблицы (количество ограничений на ссылки, в которых эта таблица является как родительской, так и зависимой)db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLESKEYUNIQUEКоличество ограничений unique (не primary key), определенных для данной таблицыdb2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLESCHECKCOUNTКоличество ограничений table check, определенных для данной таблицыdb2 "select tabname, checkcount from syscat.tables where checkcount > 0"

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

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

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

Рисунок 3. Представление таблиц в Control Center
Рисунок 3. Представление таблиц в Control Center

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

Рисунок 4. Окно Alter Table в менеджере Control Center
Рисунок 4. Окно Alter Table в менеджере Control Center

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

Листинг 1. Запрос к каталогу БД для определения null-столбцов таблицы
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.

Листинг 2. Создание ограничения 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

Имена ограничений

Если при создании ограничения вы не указываете его имя, DB2 назначает ему имя, основанное на времени создания, например, SQL100419222516560.

Ограничение 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.

Листинг 3. Создание ограничения primary key
db2 alter table staff add primary key (id)

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

Рисунок 5. Окно Alter Table
Рисунок 5. Окно Alter Table

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

Рисунок 6. Окно Define Primary Key
Рисунок 6. Окно Define Primary Key

Окно 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.

Листинг 4. Создание ограничения foreign key
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).

Таблица 2. Опции ограничения foreign key
Если при создании ограничения foreign key была указана опция……мы получим следующий результат
RESTRICT или NO ACTIONСтроки не будут удалены.
SET NULLКаждый null-столбец внешнего ключа примет значение null.
CASCADEОперация удаления распространяется на зависимые таблицы родительской таблицы. Эти зависимости называются связанными по удалению (delete-connected) с родительской таблицей.

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

Листинг 5. Примеры правил обновления и удаления в ограничении foreign key
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.

Листинг 6. Создание ограничения table check
db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

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

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

Рисунок 7. Создание ограничения check constraint в окне Alter Table
Рисунок 7. Создание ограничения check constraint в окне Alter Table

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

Рисунок 8. Окно Change Check Constraint позволяет изменить существующее ограничение
Рисунок 8. Окно Change Check Constraint позволяет изменить существующее ограничение

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

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

Отложенная проверка ограничений

Для перевода таблицы в состояние отложенной проверки (check-pending state) можно использовать оператор SET INTEGRITY. Это позволяет создавать новые ограничения table check с помощью оператора ALTER TABLE, не выполняя при этом проверку существующих данных таблицы.

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

Листинг 7. Использование оператора SET INTEGRITY для отложенной проверки ограничений
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).

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

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



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

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=776973
ArticleTitle=Основы DB2: Ограничения
publish-date=11282011