Определение данных
Создание таблиц
Оператор CREATE TABLE
Создание таблицы производится с помощью оператора CREATE TABLE с указанием необходимых параметров.
Для создания таблицы необходимо указать ее имя и определить столбцы. Определение столбца включает его имя и тип. Если указывается длина столбца, то она заключается в круглые скобки после типа. Кроме того, можно указать ограничения для столбца. Все перечисленные элементы определения столбца указываются друг за другом через пробел. Если создаваемая таблица содержит несколько столбцов, то их определения разделяются запятыми.
Далее приведен запрос на создание таблицы authors без ограничений:
CREATE TABLE authors
(id integer,
last_name text,
first_name text);
Ограничения для столбцов
NOT NULL Столбец не может содержать значение NULL, т.е. значения этого столбца должны быть определенными.
UNIQUE Значение, вводимое в столбец, должно отличаться от всех остальных значений в этом столбце, т.е. быть уникальным.
PRIMARY KEY Столбец является первичным ключом. В каждой таблице только один столбец может быть первичным ключом. Это означает, что он не может содержать значение NULL, а вводимое в него значение должно отличаться от всех остальных значений в этом столбце. Таким образом, PRIMARY KEY является комбинацией NOT NULL и UNIQUE.
DEFAULT значение Устанавливает значение по умолчанию. Так, при добавлении новой записи столбец с таким ограничением автоматически получит указанное значение.
CHECK (условие) Позволяет производить проверку условия при вводе данных. Значение будет сохранено, если условие выполняется, в противном случае – нет.
Создадим таблицу authors с использованием ограничений столбцов:
CREATE TABLE authors
(id integer PRIMARY KEY,
last_name text NOT NULL,
first_name text);
Ограничения для таблиц
В ограничениях таблиц, в отличие от ограничений полей, могут участвовать сразу несколько полей таблицы. Для ограничения таблицы может быть задано имя. В будущем имя ограничения может пригодиться для удаления ограничения (например, в секции DROP CONSTRAINT команды ALTER TABLE).
Определение ограничения для таблицы указывается после определения столбцов и состоит из необязательной секции CONSTRAINT имяОграничения, за которой следует выражение, определяющее непосредственно само ограничение.
UNIQUE Ограничение означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Допускается многократное вхождение псевдозначения NULL.
PRIMARY KEY В ограничении могут перечисляться несколько полей, разделенных запятыми. Используется для составного первичного ключа.
CHECK (условие) Команды INSERT или UPDATE для записи завершаются успешно лишь при выполнении заданного условия. Может содержать ссылки на несколько полей.
Создадим таблицу authors с использованием ограничений таблицы:
CREATE TABLE authors
(id integer,
last_name text NOT NULL,
first_name text,
CONSTRAINT id_pkey PRIMARY KEY (id),
CONSTRAINT name_uniq UNIQUE (last_name, first_name));
Внешние ключи
Внешний ключ – это столбец или группа столбцов, соответствующих первичному ключу другой таблицы. Внешний ключ определяется как ограничение столбца с помощью выражения REFERENCES внешняяТаблица (первичныйКлюч). Если первичныйКлюч в ограничении не указан, проверка выполняется по первичному ключу внешней таблицы.
Создадим таблицу books:
CREATE TABLE books
(id integer PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER REFERENCES authors (id),
subject_id INTEGER NOT NULL REFERENCES subjects);
Ограничение внешнего ключа может содержать дополнительные секции ON DELETE и ON UPDATE. В этих секциях указывается, какую операцию следует произвести с полем внешнего ключа, если будет удален или изменен соответствующий первичный ключ. Возможные операции:
NO ACTION Если удаление (изменение) первичного ключа приводит к нарушению целостности ссылок, происходит ошибка. Используется по умолчанию, если операция не указана.
RESTRICT Аналогично NO ACTION.
CASCADE Удаление (обновление) всех записей, содержащих ссылки на удаляемую (обновляемую) запись.
SET NULL Поля, содержащие ссылки на удаляемую (обновляемую) запись, заменяются псевдозначениями NULL.
SET DEFAULT Полям, содержащим ссылки на удаляемую (обновляемую) запись, присваивается значение по умолчанию.
Рассмотрим данные операции на примере создания таблицы books:
CREATE TABLE books
(id integer PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER REFERENCES authors (id)
ON DELETE NO ACTION ON UPDATE CASCADE,
subject_id INTEGER NOT NULL
REFERENCES subjects ON UPDATE CASCADE);
Ограничение внешнего ключа может быть и ограничением таблицы. Если внешний ключ – составной, то единственный способ его задать – это использовать ограничение таблицы. В случае ограничения таблицы определение ограничения предваряется ключевыми словами FOREIGN KEY (списокПолей).
Создадим таблицу books с использованием ограничений таблицы в качестве ограничений внешнего ключа:
CREATE TABLE books
(id integer PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER,
subject_id INTEGER NOT NULL,
CONSTRAINT author_fk
FOREIGN KEY (author_id) REFERENCES authors (id)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects
ON UPDATE CASCADE);
Удаление таблиц
Удалить таблицу из базы данных можно следующим образом:
DROP TABLE имяТаблицы;
Использование команды DROP TABLE требует осторожности, поскольку удаление таблицы приводит к уничтожению всех хранящихся в ней данных.
Модификация таблиц
Модификация таблицы – это изменение ее структуры. Для модификации таблиц предназначена команда ALTER TABLE. Реализация ALTER TABLE в MS ACCESS 2007 поддерживает следующие типы модификации:
· создание полей;
· изменение типа данных существующего поля;
· добавление ограничений;
· удаление полей;
· удаление ограничений.
Создание полей
Для создания нового поля в команду ALTER TABLE включается секция ADD COLUMN:
ALTER TABLE имяТаблицы
ADD COLUMN имяСтолбца типСтолбца;
Ключевое слово COLUMN не является обязательным.
Предположим, в таблицу books базы данных booktown потребовалось включить новое поле publication для хранения даты публикации:
ALTER TABLE books
ADD publication date;
Назначение и отмена значений по умолчанию
Назначать и отменять значения по умолчанию для отдельных полей относительно легко. Для этого используется команда ALTER TABLE с секцией ALTER COLUMN:
ALTER TABLE имяТаблицы
ALTER COLUMN имяСтолбца
SET DEFAULT значение;
или
ALTER TABLE имяТаблицы
ALTER COLUMN имяСтолбца
DROP DEFAULT;
Ключевое слово COLUMN не является обязательным.
Например, назначим и отменим значение 1 в качестве значения по умолчанию для поля edition (номер издания) таблицы editions;
ALTER TABLE editions
ALTER COLUMN edition
SET DEFAULT 1;
ALTER TABLE editions
ALTER edition
DROP DEFAULT;
Переименование таблицы
Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME:
ALTER TABLE имяТаблицы
RENAME TO новоеИмя;
Таблицу можно переименовывать сколько угодно раз, это никак не отражается на состоянии хранящихся в ней данных. В некоторых ситуациях переименования нежелательны – например, если таблица используется внешним приложением.
Переименуем таблицу books в таблицу literature:
ALTER TABLE books
RENAME TO literature;
Переименование полей
Изменять имена полей можно без изменения данных, хранящихся в таблице. Однако, переименование полей – довольно опасная операция, поскольку существующие приложения могут содержать ссылки на имена полей. Если программа обращается к полю по имени, то переименование может нарушить ее работоспособность. Команда переименования полей имеет следующий синтаксис:
ALTER TABLE имяТаблицы
RENAME COLUMN имяПоля TO новоеИмя;
Ключевое слово COLUMN не является обязательным. По двум идентификаторам, разделенным ключевым словом TO, PostgreSQL может определить, что команда переименования относится к одному полю, а не таблице.
Например,
ALTER TABLE stock
RENAME COLUMN stock TO is_in_stock;
Добавление ограничений
После создания таблицы сохраняются некоторые возможности добавления ограничений. В MS ACCESS 2007 команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа. Команда создания новых ограничений имеет следующий синтаксис:
ALTER TABLE имяТаблицы
ADD CONSTRAINT имяОграничения ограничение;
Создадим ограничение внешнего ключа для таблицы editions (связанной с полем id таблицы books):
ALTER TABLE editions
ADD CONSTRAINT foreign_book
FOREIGN KEY (book_id) REFERENCES books (id);
Лабораторная работа 7
Все работы по созданию и модификации таблиц выполнять в базе данных с номером вашей группы.
1. Создать таблицу для хранения данных о студентах с полями: фамилия, номер зачетки, адрес, год поступления в институт, номер специальности, номер группы; ввести ограничения первичного ключа для номера зачетки, отсутствия пустых значений для фамилии и года поступления в институт, значение специальности по умолчанию 0220100.
2. Заполнить данными созданную таблицу (не менее 5 записей), при вводе попытаться ввести записи с дублирующимися номерами зачетных книжек, однофамильцами, без фамилии, без года поступления в институт, без специальности; зафиксировать в отчете свои действия и реакцию на них сервера БД.
3. Создать таблицу для хранения данных о сдаче студентами различных экзаменов по дисциплинам с оценками и заполнить ее данными (каждый студент сдает не менее трех экзаменов); обеспечить выполнение условий целостности данных, чтобы экзамены сдавались только студентами, у которых есть зачетные книжки, а также чтобы отвергались попытки занести запись повторно. При удалении записей о студентах должны удаляляться и записи о сданных ими экзаменах (каскадное удаление).