Добавление и изменение ограничения внешнего ключа

Добавим опять же в таблицу людей people ограничение внешнего ключа. Пусть это будет внешний ключ, ссылающийся на таблицу стран. Иными словами, мы собира­емся для каждого человека указывать и страну проживания.

Щелкните правой кнопкой в Обозревателе объектов по имени таблицы PEOPLE и в контекстном меню выберите команду Проект.

Вначале добавим в таблицу новый столбец codctr, который и станет внешним ключом, ссылающимся на таблицу стран. Для этого в последней пустой строке списка столбцов таблицы введем имя столбца, CODCTR, в поле Тип данных выберем из вы­падающего списка значение пользовательского типа данных — char(3). Это тип данных, который установлен для столбца первичного ключа таблицы стран. Напоминаю, типы данных первичного ключа родительской таблицы и ссылающегося на него внешнего ключа дочерней таблицы должны полностью совпадать. Установим допустимость значения NULL. Впрочем, это значение и так устанавливается по умолчанию.

Теперь список столбцов таблицы выглядит так, как показано на рис. 5.93.

Щелкнем правой кнопкой мыши по любому столбцу таблицы и в контекстном меню выберем команду Отношения. Можно также в главном меню программы выбрать Конструктор таблиц | Отношения.

Рис. 5.93. Новый список столбцов таблицы людей

 

Появится окно, в котором содержится список внешних ключей, связанных с данной таблицей (рис. 5.94). Здесь представлены не только внешние ключи самой таблицы PEOPLE, но также внешние ключи других таблиц базы данных, которые ссылаются на первичный ключ этой таблицы.

Рис. 5.94. Список внешних ключей, связанных с таблицей PEOPLE

 

Для создания нового внешнего ключа нужно в левой нижней части окна щелкнуть мышью по кнопке Добавить. В левой части окна в списке появится новое ограничение с именем fk_people_people*. В этом окне ничего изменять не будем. Щелкните мышью по кнопке Закрыть.

Далее следует сохранить выполненные изменения для таблицы и закрыть окно Проект. Теперь в Обозревателе объектов нужно обновить список, чтобы можно было увидеть внесенные в таблицу изменения. Щелкните правой кнопкой мыши по имени таблицы people и в контекстном меню выберите Обновить.

После этого раскройте опять таблицу PEOPLE, раскройте папку ключей, щелкните в появившемся списке ключей правой кнопкой мыши по имени вновь созданного ограничения fk_people_people и в контекстном меню выберите команду Изменить.

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

Выделите в левой части окна созданный ключ FK_PEOPLE_PEOPLE. В правой части окна внесем необходимые изменения в характеристики этого ключа. Вначале изменим его имя. Выделите строку (Имя) и в правой части строки введите новое имя:

FK4_PEOPLE.

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

Рис. 5.95. Начальная заготовка таблиц и столбцов для создаваемого внешнего ключа

 

В левой части окна (Таблица первичного ключа) выберем характеристики родительской таблицы, на первичный ключ которой будет ссылаться наш внешний ключ. Из первого выпадающего списка выберем имя таблицы REFCTR, ниже из выпадающего списка выберем имя столбца этой таблицы, CODCTR. В правой части окна (Таблица внешнего ключа) выберем имя столбца, входящего в состав внешнего ключа, CODCTR.

После внесенных изменений окно будет выглядеть так, как показано на рис. 5.96.

Щелкнем мышью по кнопке OK. Теперь остается только для внешнего ключа в ок­не характеристик установить поведение системы при удалении соответствующей строки родительской таблицы и при изменении соответствующего значения пер­вичного ключа в родительской таблице.

Рис. 5.96. Установленные характеристики внешнего ключа

 

Рис. 5.97. Характеристики созданного нового внешнего ключа для таблицы PEOPLE

 

Раскройте узел Спецификация INSERT и UPDATE (неудачное название узла; ко­нечно же, не insert, а delete). Для строки Правило удаления из выпадающего списка выберите Присвоить NULL, для строки Правило обновления — Каскад- но. Эти установки соответствуют заданию в языке Transact-SQL предложений

ON DELETE SET NULL ON UPDATE CASCADE

Напомню, эти предложения означают, что при удалении соответствующей строки родительской таблицы значение внешнего ключа дочерней таблицы устанавливается в NULL, а при изменении значения первичного ключа родительской таблицы это изменение вносится и в значения внешнего ключа всех строк дочерней таблицы.

Полученный вид окна показан на рис. 5.97.

Щелкните в этом окне по кнопке Закрыть. Новый внешний ключ со всеми необходимыми характеристиками будет создан.

 

Изменение ограничения CHECK

Сейчас внесем изменения в ограничение CHECK (довольно бессмысленные) в столбец SEX (напомню, что этот столбец содержит признак пола человека) таблицы PEOPLE.

В Обозревателе объектов раскройте таблицу PEOPLE, раскройте папку ограничений, щелкните правой кнопкой мыши по имени ограничения CH_PEOPLE и в контекстном меню выберите команду Изменить. Появится окно Проверочные ограничения, содержащее сведения о существующем единственном ограничении для столбца sex этой таблицы (рис. 5.98).

Рис. 5.98. Ограничение CHECK таблицы PEOPLE

 

Чтобы изменить ограничение, щелкните мышью по строке Выражение. Здесь со­держится формула ограничения:

([SEX]='1' OR [SEX]='0')

Для изменения ограничения щелкните по кнопке с многоточием справа от формулы. Появится окно, в котором можно редактировать формулу (рис. 5.99).

Рис. 5.99. Формула ограничения CHECK таблицы PEOPLE

Измените формулу на следующую, добавив в список допустимых значений для столбца и '2':

(([SEX]='1' OR [SEX]='0') OR [SEX]='2')

Опять же, следите за правильностью расстановки скобок в формуле. Щелкните по кнопке OK. В предыдущем окне щелкните по кнопке Закрыть. Сохраните обыч­ным образом изменения таблицы.

Добавление ограничения CHECK

Теперь попытаемся добавить в эту же таблицу новое ограничение check. Вначале будем добавлять конструкцию, содержащую правильный оператор SELECT, который, однако, недопустим в подобных ограничениях.

Щелкните правой кнопкой мыши по имени ограничения CH_PEOPLE и в контекстном меню выберите команду Изменить. Появится окно Проверочные ограничения (см. рис. 5.98). Чтобы добавить новое ограничение, щелкните по кнопке Добавить. В списке ограничений появится ограничение с именем ck_people*. В правой части окна замените это имя на сн2 people. Щелкните мышью по строке Выражение, щелкните по кнопке UI справа от формулы. В появившемся окне Выражение про­верочного ограничения введите выражение для проверки значения столбца codctr:

(SELECT COUNT(*) FROM REFCTR R WHERE R.CODCTR = CODCTR) > 0

Это условие (на самом деле излишнее) требует, чтобы в таблице REFCTR присутст­вовала строка, первичный ключ которой (codctr) равен вводимому или изменяемо­му значению столбца codctr таблицы people. Излишним это условие является по­тому, что при помещении в базу данных строки дочерней таблицы или при измене нии значения ее внешнего ключа система проверяет наличие соответствующей строки в родительской таблице, в таблице refctr. Таким образом, мы просто дуб­лируем, а точнее, пытаемся дублировать аналогичные действия системы.

Такой же результат можно получить и при использовании функции exists():

exists(SELECT * FROM REFCTR R WHERE R.CODCTR = CODCTR)

Щелкните в окне Выражение проверочного ограничения по кнопке OK. После этого закройте окно Проверочные ограничения, щелкнув по кнопке Закрыть. Попытайтесь сохранить изменения таблицы. Вы получите сообщение, что в ограничении CHECK недопустимо использование вложенного оператора SELECT.

Теперь создадим "правильное" ограничение check для таблицы REFCTR. Сделаем так, чтобы пользователь в поле кода страны мог ввести не менее двух символов.

Вызовите окно Проект для таблицы REFCTR, щелкните правой кнопкой мыши по имени любого столбца и в контекстном меню выберите элемент Проверочные ограничения. Появится окно ограничений CHECK, в котором не будет ни одного ограничения. Щелкните мышью по кнопке Добавить. В списке появится ограничение с именем CK_REFCTR. Измените в поле (Имя) имя ограничения на CH1_REFCTR. В поле Выражение введите выражение для проверки количества символов столбца CODCTR: (LEN(CODCTR) > 1)

В поле Описание введите дополнительное текстовое описание, например:

Проверка на количество символов кода страны

В результате окно списка ограничений check примет следующий вид (рис. 5.100). В этом окне щелкните по кнопке Закрыть. Сохраните изменения таблицы REFCTR.

Рис. 5.100. Созданное ограничение CHECK таблицы REFCTR

 

Можете проверить работоспособность нового ограничения. В Обозревателе объектов щелкните правой кнопкой мыши по имени таблицы и в контекстном меню выберите команду Изменить первые 200 строк. Попытайтесь в любой стране из­менить код, оставив один символ. Вы получите сообщение об ошибке.

 

Удаление столбца

Столбец можно легко удалить, если он не входит в состав ограничения первичного, уникального или внешнего ключа, не используется для получения значения вычис­ляемого столбца и на него нет ссылок в ограничениях check.

Если столбец входит в состав первичного ключа, и на эту таблицу ссылаются дру­гие таблицы базы данных, то будет выдано сообщение, аналогичное тому, как было показано на рис. 5.83.

Когда столбец используется при получении значения вычисляемого столбца, то при его удалении никаких сообщений, как обычно, не выдается, но при попытке сохра­нить изменения таблицы будет появляться диалоговое окно с соответствующим предупреждающим сообщением.

Например, давайте в таблице people удалим столбец namei, который используется в выражении для вычисляемого столбца fullname (здесь мы не учитываем исполь­зование этого столбца в созданном только что ограничении уникального ключа). Столбец fullname в таблице people определен следующим образом:

FULLNAME AS  /* Вычисляемый столбец */

(NAME3 + ' ' + NAME1 + ' ' + NAME2),

На вкладке Проект удалим столбец NAME1, щелкнув по его имени правой кнопкой мыши и выбрав в контекстном меню элемент Удалить столбец. При попытке со­хранения таблицы появится диалоговое окно (рис. 5.101).

Здесь выдается сообщение о возникновении ошибки при проверке достоверности формулы для вычисляемого столбца FULLNAME.

При щелчке мышью по кнопке Да (что означает требование продолжить попытку сохранения изменений) появляется следующее окно, сообщающее о невозможности изменения таблицы (рис. 5.102).

Здесь остается только щелкнуть мышью по кнопке OK. Удаление столбца выполнено не будет.

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

Щелкните мышью по столбцу FULLNAME. В нижней части окна на вкладке Свойства столбца раскройте группу Спецификация вычисляемого столбца. В строке (Формула) содержится задание формулы получения значения этого вычисляемого столбца. В нашем случае будет записано:

(((([NAME3]+' ')+[NAME1])+' ')+[NAME2])

 

Рис. 5.101. Диалоговое окно, сообщающее о наличии вычисляемого столбца

 

Рис. 5.102. Диалоговое окно, сообщающее о невозможности изменения таблицы

 

Нам нужно из этой формулы убрать упоминание столбца name1. Аккуратно удалим его из операции конкатенации. Следите за количеством левых и правых круглых скобок в выражении. Теперь формула должна выглядеть следующим образом:

 

[NAME3]+' ')+[NAME2]

 

Чтобы формула была изменена, нажмите клавишу <Enter> или перейдите к другому элементу этой вкладки.

Сохраните изменения в таблице. Вначале вы получите диалоговое окно (рис. 5.103).

Рис. 5.103. Диалоговое окно, содержащее список изменяемых таблиц

 

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

Щелкните по кнопке Да. Изменения таблицы будут сохранены в базе данных.

Теперь пару слов о созданном только что ограничении уникального ключа для таб­лицы people. Если мы удаляем столбец, входящий в состав уникального ключа, то система автоматически без каких-либо предупреждений удаляет уникальный ключ, куда входил удаляемый столбец таблицы, если на этот уникальный ключ не ссыла­ются внешние ключи других таблиц или той же самой таблицы. Учтите это поведе­ние системы, когда надумаете удалять столбцы ваших таблиц.

 

Удаление ограничений

Удаление ограничения CHECK

Удалить можно любое ограничение check без каких-либо ошибок и неприятностей. От такого ограничения не зависит никакой объект базы данных. Давайте, например, удалим ограничение для столбца sex таблицы people, которое чуть раньше мы с вами немного покалечили.

Откройте окно Проект для таблицы people. Щелкните правой кнопкой мыши по любому столбцу таблицы и в контекстном меню выберите команду Проверочные ограничения. Появится уже хорошо нам знакомое окно, описывающее все ограни­чения check этой таблицы (рис. 5.104).

Чтобы удалить ограничение ch_people, нужно, выделив это ограничение, щелкнуть мышью по кнопке Удалить в левой нижней части окна. Ограничение пропадет из списка. Затем щелкните по кнопке Закрыть для закрытия окна. Для завершения процесса нужно сохранить изменения таблицы.

Рис. 5.104. Список ограничений таблицы PEOPLE