Лабораторная работа № 7. Управляющие запросы: создание, изменение и удаление таблиц

В данной лабораторной работе мы рассмотрим новый тип запросов — управляющие запросы. Они служат для создания, изменения и удаления таблиц или индексов, но тема индексов пока затрагиваться не будет. В окне конструктора запросов, не добавляя него таблиц, нажмите кнопку Управление. В открывшемся окне в режиме SQL введите следующий запрос:

CREATE TABLE [Элемент].

(ElemID COUNTER PRIMARY KEY,.

ElemName VARCHAR (50) NOT NULL);

Его выполнение приведет к созданию таблицы Элемент с первичным ключом ElemID типа Счетчик и текстовым полем ElemName длиной до 50 символов. Типы данных Access и ограничение NOT NULL мы рассматривали в лабораторной работе № 1.

В общем виде формат оператора CREATE TABLE предполагает, что в круглых скобках идет перечень определений столбцов и ограничений, причем ограничения уровня столбца записываются в определении столбца, а ограничения уровня таблицы идут после определения столбцов. В приведенном выше примере оба ограничения (PRIMARY KEY и NOT NULL записаны как ограничения уровня столбца). Если создается составной первичный ключ, его можно описать только как ограничение уровня таблицы. Различие в приведенных ниже выражениях SQL состоит в том, что в первом из них название ограничения явно указывается, во втором — назначается СУБД автоматически:

CREATE TABLE [Элемент1].

(ElemIDl INTEGER,.

ElemID2 INTEGER,.

ElemName VARCHAR (50) NOT NULL,.

CONSTRAINT pkl PRIMARY KEY (ElemIDl, ElemID2));

или.

CREATE TABLE [Элемент1].

(ElemIDl INTEGER,.

ElemID2 INTEGER,.

ElemName VARCHAR (50) NOT NULL,.

PRIMARY KEY (ElemIDl, ElemID2));

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

CREATE TABLE [Элементы].

(ElementSlD COUNTER PRIMARY KEY,.

ElemID INTEGER REFERENCES [Элемент] NOT NULL,.

Comment MEMO);

Задание. Выполните запрос, создающий таблицу Элемент. Для таблицы Элементы модифицируйте код запроса, чтобы он описывал внешний ключ как именованное ограничение уровня таблицы (имя fill). Откройте схему данных, добавьте на нее созданные таблицы. Проверьте, что на схеме отображается связь между таблицами типа «один-ко-многим» .

Задание. Напишите три запроса, создающие структуру данных нашей библиотечной базы (см. лабораторную номер 1). Для текстовых данных используйте тип varchar (N), где N — максимально допустимое количество символов, но не более 255. Особое внимание уделите ограничениям первичного и внешнего ключа, NOT NULL, уникальности значения — UNIQUE. Задать ограничение на диапазон допустимых значений поля (в SQL — это ограничение CHECK) в Access через SQL не получится, это можно сделать только через графический интерфейс. Обратите внимание, что подстановку запросами задать не удастся.

Задание. Добавьте созданные вами таблицы на схему данных, сравните их с таблицами из образца.

Изменения в структуре уже созданной таблицы можно сделать с помощью оператора ALTER TABLE. Его формат.

ALTER TABLE где выражение, описывающее выполняемое действие, может принимать значения, перечисленные в табл. П. 7.1.

Таблица П. 7.1

Действия оператора ALTER TABLE.

Стоит ОТМСТИТЬ, ЧТО В конструкциях ADD COLUMN И ADD CONSTRAINT нужно приводить описание ограничения (подобно тому, как ранее это сделалось для CREATE TABLE), а в DROP CONSTRAINT требуется указать только имя удаляемого ограничения.

Рассмотрим два примера. В первом мы создаем новый столбец в таблице «Элемент1», название — info, тип — varchar (200):

ALTER TABLE [Элемент1].

ADD COLUMN info varchar (200) ;

Второй фрагмент кода создает в этой же таблице ограничение с названием un_info, требующее 5'никальности значения созданного поля:

ALTER TABLE [Элемент1].

ADD CONSTRAINT un_info UNIQUE (info);

Задание. Выполните приведенные примеры. Напишите запрос, удаляю — щий созданное ограничение.

Задание. Для созданного вами аналога таблицы BookStatus напишите запрос, создающий поле Comment типа varchar{200) значение этого поля должно быть обязательно определено.

Конструкция SELECT … INTO … позволяет создать таблицу и сразу же внести в нее какие-то данные из другой таблицы. Пусть, например, нам нужен список авторов книг, размещенный в отдельной таблице. Откройте конструктор запросов, выберите таблицу Book и из нее столбец Author. Найдите на панели инструментов кнопку Создание таблицы. Задайте имя таблицы Auth. Теперь в режиме SQL можно увидеть такой текст запроса:

SELECT Book. Author INTO Auth FROM Book;

Задание. Выполните приведенный пример и опишите результат. Аналогичным образом создайте таблицу NewBook, содержащую всю информацию из Book о книгах, изданных после 2000 г.

Удалить таблицу можно с помощью управляющего запроса, содержащего оператор DROP TABLE. Например, удалим только что созданную таблицу:

DROP TABLE Auth;

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