Условия уникальности столбцов и значения NULL

Значения null в столбце первичного ключа таблицы или в столбце, для которого задано условие уникальности, создают проблему. Предположим, что вы пытаетесь добавить в таблицу строку с первичным ключом, имеющим значение null (или частично имеющим значение null, если первичный ключ является составным). Из-за значения null СУБД не может однозначно решить, является ли первичный ключ дубликатом уже имеющегося в таблице ключа или нет. Может оказаться верным и то и другое, в зависимости от настоящего значения отсутствующих данных.

По этой причине в SQL требуется, чтобы любой столбец, являющийся частью первичного ключа, и любой столбец, на который наложено условие уникальности, был объявлен с офаничением not null.

Ссылочная целостность

в главе 4 уже были рассмотрены первичные и внешние ключи, а также отношения предок/потомок между таблицами, создаваемые этими ключами. На рис. И.1 изображены таблицы salesreps и office, а также показана связь между ними, реализованная через первичный и внешний ключи. Столбец office является первичным ключом таблицы offices и уникальным образом идентифицирует каждую строку в этой таблице. Столбец rep office таблицы salesreps представ/гяет собой внешний ключ для таблицы offices. Он идентифицирует офис, за которым закреплен каждый служащий.

Столбцы Rep 0ffice и office создают между сфоками таблиц offices и salesreps отношение предок/потомок. Для каждой сфоки таблицы obtices (предок) существует ноль или более сфок таблицы salesreps (потомки) с таким же идентификатором офиса. Для каждой сфОки таблицы salesreps (потомок) существует ровно одна строка таблицы offices (предок) с таким же идентификатором офиса

Таблица OFFICES

Первичный ключ

Таблица SALESREPS

Ссылка

Внешний ключ

Рис 1 J. 1. Отношение внешний ключ ~ первичный ключ

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

INSERT INTO SALESREPS (EMPL NUM, NAME, REP OFFICE, AGE,

HIRE DATE, SALES) VALT3ES (115, George Smith, 31, 37, Ol-APR-90, 0.00)

Ha первый взгляд, это нормальная инструкция insert. И действительно, во многих СУБД такая строка будет благополучно добавлена в таблицу. В базе данных появится информация о том, что Джордж Смит (George Smith) работает в офисе с идентификатором 31, хотя офиса с таким идентификатором в таблице offices нет. Ясно, что новая строка нарущает отношение предок/потомок, существующее между таблицами offices и salesreps. Скорее всего, идентификатор офиса 31 в инструкции insert является ошибочным - вероятно, пользователь намеревался ввести идентификатор 11, 21 или 13.

Кажется достаточно очевидным, что допустимое значение для столбца rep OF-fice должно быть равно одному из значений, содержащихся в столбце office. Это правило известно как условие ссылочной целостности. Оно обеспечивает целостность отношений предок/потомок, создаваемых внешними и первичными ключами.

Ссылочная целостность стала ключевым элементом реляционной модели с тех пор, как доктор Кодд впервые описал эту модель. Однако условия ссылочной целостности отсутствовали как в System/R - экспериментальной СУБД компании IBM, так и в первых версиях DB2 и SQL/DS. Компания IBM ввела поддержку ссылочной целостности в DB2 только в 1989 году, а в стандарт SQL1 она была добавлена уже после выхода его первой редакции. В настоящее время большинство поставщиков либо уже реализовали в своих СУБД поддержку ссылочной целостности, либо объявили о планах сделать это в следующих версиях своих профаммных продуктов.

Проблемы, связанные со ссылочной целостностью

Существует четыре типа изменений базы данных, которые могут нарушить ссылочную целостность отношений предок/потомок. Рассмофим каждую из этих четырех ситуаций на примере таблиц offices и salesreps, представленных на рис. II.1.

Добавление новой строки-потомка. Когда происходит добавление новой строки в таблицу-потомок (salesreps), значение ее внешнего ключа (rep office) должно быть равно одному из значений первичного ключа (office) в таблице-предке (offices). Если значение внешнего ключа не равно ни одному из значений первичного ключа, то добавление такой сфоки разрушит базу данных, поскольку появится потомок без предка ( сирота ). Обратите внимание на то, что добавление Сфоки в таблицу-предок не вызовет проблем; она просто станет предком без потомков.

Обновление внешнего ключа в строке-потомке. Это та же проблема, что и в предыдущей ситуации, но выраженная в иной форме. Если внешний ключ (rep of-Fice) обновляется инсфукцией update, то его новое значение должно быть равно одному из значений первичного ключа (office) в таблице-предке (offices). В противном случае обновленная сфока окажется сиротой.

Удаление строки-предка. Если из таблицы-предка (offices) будет удалена строка, у которой есть хотя бы один потомок (в таблице salesreps), то строки-потомки станут сиротами. Значения внешних ключей (rep office) в этих строках больше

не будут равны ни одному из значений первичного ключа (office) таблицы-предка. Обратите внимание на то, что удаление строки из таблицы-потомка не вызовет проблем; просто предок этой строки после удаления будет иметь на одного потомка меньше, ш Обновление первичного ключа в строке-предке. Это иная форма проблемы, рассмотренной в предыдущем пункте. Если происходит изменение первичного ключа (office) некоторой строки в таблице-предке (offices), все существующие потомки этой строки становятся сиротами, поскольку их внешние ключи больше не равны ни одному первичному ключу.

Средства поддержки ссылочной целостности, имеющиеся в DB2 и в стандарте ANSI/ISO, позволяют обрабатывать каждую из четырех описанных ситуаций. Первая проблема (добавление строки в таблицу-потомок) решается путем проверки значений в столбцах внешнего ключа перед выполнением инструкции insert. Если они не равны ни одному из значений первичного ключа, то инструкция insert отбрасывается и выдается сообщение об ошибке. По отношению к рис. 11.1 это означает, что для добавления в таблицу salesreps нового служащего необходимо, чтобы в таблице offices уже был офис, в который назначается данный служащий. Как видите, в учебной базе данных это ограничение имеет смысл.

Вторая проблема (обновление таблицы-потомка) решается аналогично: путем проверки нового значения внешнего ключа. Если нет ни одного равного ему значения первичного ключа, инструкция update отбрасывается с выдачей сообщения об ошибке. По отношению к рис. 11.1 это означает, что для перевода служащего в другой офис необходимо, чтобы этот офис уже присутствовал в таблице offices. В учебной базе данных это ограничение также имеет смысл.

Третья проблема (удаление строки-предка) является более сложной. Предположим, например, что вы закрыли офис в Лос-Анджелесе и хотите удалить соответствующую строку из таблицы offices. Что при этом должно произойти с двумя строками-потомками в таблице salesreps, которые представляют служащих, закрепленных за офисом в Лос-Анджелесе? В зависимости от ситуации можно:

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

автоматически удалить двух служащих из таблицы salesreps;

в столбце rep office установить для этих двух служащих значение null, показывая тем самым, что идентификатор их офиса неизвестен;

в столбце rep office для этих двух служащих установить по умолчанию некоторое значение, например идентификатор главного офиса в Нью-Йорке, указывая тем самым, что служащие автоматически переводятся в этот офис. Аналогичные сложности существуют и в четвертой ситуации (обновление первичного ключа в таблице-предке). Допустим, по каким-либо причинам требуется изменить идентификатор офиса в Лос-Анджелесе с 21 на 23. Подобно предыдущему примеру, возникает вопрос о том, как поступить с двумя строками-потомками в таблице salesreps, представляющими служащих лос-анджелесского офиса. И снова проблему можно решить четырьмя способами:

не изменять идентификатор офиса до тех пор, пока служащие не будут переведены в другой офис; в таком случае в таблицу offices следует вначале добавить строку с новым идентификатором офиса в Лос-Анджелесе, затем обновить таблицу salesreps и, наконец, удалить строку со старым идентификатором лос-анджелесского офиса;