Создание и модификация таблиц базы данных обычно производятся специальными диалоговыми инструментами сред разработки, которые поставляются вместе с СУБД. Хотя при их использовании, как правило, не требуется написания программ, эти средства включают в себя языки программирования, с помощью которых можно создавать сложные приложения, ориентированные на работу с базами данных. Примерами являются dBase, Paradox, Delphi, Access и др. Вместе с тем язык SQL также обладает средствами создания таблиц базы данных, т. е. тех таблиц, которые сохраняются в составе базы данных в долговременной памяти компьютера, например, на жестком диске. Напомню, что таблицы, возвращаемые запросами на выборку данных, являются виртуальными и доступны только тому, кто инициировал эти запросы.
Создание таблиц
Создание таблицы производится с помощью оператора CREATE TABLE (создать таблицу) с указанием необходимых параметров. При этом создается постоянная таблица. Чтобы удалить ее из базы данных, требуется выполнить специальный SQL-оператор.
Для создания временной таблицы используется оператор CREATE TEMPORARY TABLE (создать временную таблицу). Временная таблица, в отличие от постоянной, существует только в течение сеанса работы с базой данных, в котором она была создана. Однако временная таблица может быть доступна другим пользователям, как и постоянная таблица. Обычно временные таблицы создаются для представления в них текущих итоговых (отчетных) данных, доступных нескольким пользователям базы данных. Далее приведен синтаксис оператора CREATE:
CREATE [TEMPORARY] TABLE имя Таблицы (
{ Столбец1 тип [(размер]] [ограничение столбца] [,...] }
{[, CONSTRAINT ограничение таблицы] [,...]}
);
Здесь квадратные и фигурные скобки, в отличие от круглых, не являются элементами синтаксиса. В квадратных скобках заключены необязательные элементы, а в фигурных — элементы, которые могут повторяться.
Для создания таблицы необходимо указать ее имя и определить столбцы. Определение столбца включает его имя и тип. Если указывается длина столбца, то она заключается в круглые скобки после типа. Кроме того, можно указать ограничения для столбца. Все перечисленные элементы определения столбца указываются друг за другом через пробел. Если создаваемая таблица содержит несколько столбцов, то их определения разделяются запятыми. Ограничение может быть определено и для всей таблицы, а не только для ее столбцов. В этом случае используется ключевое слово CONSTRAINT (ограничение), после которого указывается само ограничение.
Только что созданная таблица пуста, т. е. не содержит ни одной записи. Для наполнения ее данными следует воспользоваться оператором INSERT INTO.
Далее приведен запрос на создание простой таблицы без ограничений:
CREATE TABLE Студент (
ID_студента INTEGER,
ФИО CHAR (20),
Специальность CHAR(15),
примечание VARCHAR
);
Данный запрос создает таблицу Студент, содержащую четыре столбца. Первый столбец целочисленный, а три других — символьные.
Ограничения устанавливаются на данные, которые вводятся в таблицу. Например, на числовой столбец можно наложить ограничение, заключающееся в том, что вводимое число должно находиться в некотором диапазоне. Другой пример ограничения: значение столбца не должно быть неопределенным.
Нередко ограничения устанавливаются и отслеживаются в приложениях, работающих с базами данных. Тем не менее, ограничения могут быть установлены и могут поддерживаться многими современными СУБД. Если вы создаете таблицу посредством SQL, то также имеете возможность задать эти ограничения, а выполнять их будет СУБД. При этом если одна и та же база данных используется несколькими приложениями, то вам придется установить ограничения только один раз, а не столько, сколько имеется приложений.
Ограничения для столбцов
В таблице ниже приведены основные ограничения для столбцов. Однако существуют и более сложные ограничения, которые нами не рассматриваются.
Таблица Ограничения для столбцов
| NOT NULL | Столбец не может содержать значение NULL, т. е. значения этого столбца должны быть определенными. |
| UNIQUE | Значение, вводимое в столбец, должно отличаться от всех остальных значений в этом столбце, т. е. быть уникальным |
| PRIMARY KEY | Столбец является первичным ключом. В каждой таблице только один столбец может быть первичным ключом. Это означает, что он не может содержать значение NULL, а вводимое в него значение должно отличаться от всех остальных значений в этом столбце. Таким образом, PRIMARY KEY является комбинацией NULL и UNIQUE |
| DEFAULT значение | Устанавливает значение по умолчанию. Так, при добавлении новой записи столбец с таким ограничением автоматически получит указанное значение |
| СНЕСК (условие) | Позволяет производить проверку условия при вводе данных. Значение будет сохранено, если условие выполняется, в противном случае — нет |
Примечание
Microsoft Access не поддерживает ключевые слова DEFAULT и СНЕСК в определениях ограничений.
Рассмотрим в качестве примера создание таблицы Студент (ID_студента, ФИО, Специальность, Примечание). Идентификатор студента (целочисленный столбец ID_студента) должен однозначно идентифицировать запись о студенте, т. е. иметь определенные и уникальные значения. Таким образом, данный столбец должен быть первичным ключом. От столбца ФИО (фамилия, имя и отчество) потребуем, чтобы в нем не было неопределенных значений. Целочисленный столбец Специальность должен содержать номера специальностей, которые не превышают 12. Тогда запрос на создание такой таблицы будет иметь вид:
CREATE TABLE Студент (
ID_студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность INTEGER CHECK(Специальность <12),
Примечание VARCHAR
);
Предположим, что данный запрос выполнен. Тогда следующий Запрос на добавление новой записи в таблицу Студент вызовет сообщение об ошибке и не будет выполнен:
INSERT INTO Студент;
Это произойдет потому, что данный запрос добавляет пустую запись, все столбцы которой содержат значение NULL, и, следовательно, не выполняются ограничения для первых двух столбцов. А следующий запрос, добавляющий в таблицу первую запись и устанавливающий определенные значения для столбцов, невызовет проблем со стороны СУБД:
INSERT INTO Студент (ID студента, ФИО, Специальность)
VALUES (1, 'Иванов Иван Иванович', 9);
Попытаемся вслед за данным запросом добавить еще одну запись:
INSERT INTO Студент (ID студента, ФИО, Специальность)
VALUES (1, 'Петров Петр Петрович', 5);
Данный запрос не будет выполнен из-за нарушения ограничения, наложенного на первый столбец: его значения должны быть не только определенными (отличными от NULL), но и уникальными. Запись можно добавить, если столбцу ID_студента присвоить, например, значение 2.
Ограничения для столбцов можно выразить и иначе — через ограничения доменов. Напомню, что домен определяется как множество значений. Домен в реляционной теории связывается с атрибутом отношения и, как таковой, определяет некоторое ограничение на этот атрибут (атрибут может принимать значения только из этого домена). В SQL можно создать домен, сначала не связанный ни с какими атрибутами (столбцами), определив для него допустимые значения: тип данных и дополнительные ограничения. Затем этот домен можно задать в качестве ограничений для любого столбца в любой таблице подобно тому, как задаются типы столбцов. Другими словами, домен можно связать с одним или несколькими столбцами различных таблиц.
В ряде случаев этот прием очень удобен, особенно если у вас имеется несколько "однотипных" столбцов в различных таблицах базы данных.
Чтобы создать домен, используется такой синтаксис:
CREATE DOMAIN имяДомена типДанных Ограничения;
Например, таблицу студент(ID_студента, ФИО, Специальность, примечание) можно определить традиционным образом:
CREATE TABLE Студент (
ID_студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность INTEGER CHECK (Специальность < 12),
Примечание VARCHAR);
Однако можно сначала определить домен с именем specDomain, задав для него тип данных и ограничение:
CREATE DOMAIN specDomain INTEGER CHECK (Специальность < 12);
Обратите внимание, что в определении домена, пока не связанного ни с каким столбцом какой-либо таблицы, задается тип значений домена и ограничение на эти значения.
С учетом того, что домен specDomain создан, определение таблицы студент можно задать следующим образом:
CREATE TABLE Студент (
ID_студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность specDomain,
Примечание VARCHAR
);
Рассмотрим еще один пример. Предположим, в нескольких таблицах вашей базы данных имеется столбец с именем код продукта и типом данных CHAR(6) значения которого должны начинаться с символа 'А', 'С' или 'Х'. Для таких столбцов, раз их несколько, можно предварительно создать общий домен:
CREATE DOMAIN ProdDomain CHAR (б)
CHECK (SUBSTRING (VALUE, 1,1) IN (‘A’, ‘C’, ‘X’));
После определения домена можно создать таблицу использующую этот домен:
CREATE TABLE Товары (
Код продукта ProdDamain,
Описание VARCHAR);
Ограничения для таблиц
Ограничения на вводимые данные можно назначить не только для отдельных столбцов, но и для таблицы в целом. Это удобно в тех случаях, когда необходимо для нескольких столбцов назначить одинаковые ограничения. Кроме того, если первичный ключ составной, (т, е. состоит из нескольких столбцов), то указать его можно только как ограничение для таблицы, а не для столбца. Все определение ограничения для таблицы указывается после определений столбцов и состоит из ключевого слова CONSTRAINT, за которым следует выражение, определяющее непосредственно само ограничение. В таблице ниже приведены основные ограничения для таблицы.
Ограничения для таблицы
| Определение | Описание |
| UNIQUE (списокСтолбцов) | Значения в столбцах, указанных в списке, должны быть уникальными |
| PRIMARY KEY (списокСтолбцов) | В каждой таблице должен быть только один первичный ключ, который определен либо как ограничение для столбца, либо как ограничение для таблицы. Если первичный ключ составной, то он должен быть определен как ограничение для таблицы. Справа от ключевых слов PRIMARY KEY в круглых скобках указывается список столбцов, определяющих составной первичный ключ. |
| CHECK (условие) | Позволяет производить проверку условия при вводе данных. Значение будет сохранено, если условие выполняется, в противном случае — нет. В отличие от ограничения для столбца, здесь можно использовать условия, оперирующие значениями различных столбцов таблицы |
| FOREIGN KEY REFERENCES | Ограничение типа "внешний ключ |
В следующем, примере создается таблица Студент (Фамилия, Имя, Отчество, Специальность, Примечание). Предполагается, что комбинация значений первых трех столбцов должна однозначно идентифицировать запись в таблице, т. е. являться первичным ключом
CREATE TABLE Студент (
Фамилия CHAR (20),
Имя СНАЯ (15),
Отчество CHAR (20),
Специальность INTEGER,
Примечание VARCHAR,
CONSTRAINT PRIMARY KEY (Фамилия, Имя, Отчество)
);
Следующие два запроса на добавление записей будут выполнены, поскольку комбинации значений столбцов, определяющих первичный ключ, не содержат NULL, и отличаются друг от друга:
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность) VALUES ('Петров', 'Петр',. 'Петрович', 5);
INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность) VALUES ('Петров.', -'Петр', 'Иванович'„ 5);
Внешние ключи
Одна из важнейших разновидностей ограничений связана с определением внешних ключей. Внешний ключ — это столбец или группа столбцов, соответствующих первичному ключу другой таблицы. Чтобы понять синтаксис выражения, определяющего внешний ключ, рассмотрим пример.
Пусть в базе данных имеются две таблицы:
Заказы (ID_заказа, ID_клиента) — содержит сведения о том, какие заказы сделал тот или иной клиент;
клиенты (ID_клиента, Имя, Адрес, Телефон) — сведения о клиентах (справочник).
В таблице клиенты столбец ID_клиента является первичным ключом, т. е. его значения отличны от NULI, и уникальны. В таблице заказы столбец ID_клиента не обязан иметь уникальные значения, поскольку один и тот же клиент может сделать несколько заказов. Вместе с тем любому значению столбца заказы.ID_клиента соответствует единственное значение столбца клиенты.ID клиента. При описанных условиях столбец ID_клиента таблицы Заказы является внешним ключом, ссылающимся на первичный ключ ID_клиента таблицы клиент.
Внешний ключ определяется как ограничение для таблицы в выражении с ключевыми словами CONSTRAIN FOREIGN KEY (ограничение "внешний ключ"):
CONSTRAINT FOREIGN KEY внешнийКлюч REFERENCES внешняя таблица (первичный ключ)
Здесь внешний ключ — имя столбца или список столбцов, разделенных запятыми, которые определяют внешний ключ, за ключевым СЛОВОМ REFERENCES (ссылки) указывается внешняя таблица и ее первичный ключ, на который ссылается внешний ключ.
Для рассмотренного ранее примера таблицу Заказы можно определить следующим образом:
CREATE TABLE Заказы (ID_заказа INTEGER,
ID_клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES клиенты (ID_клиента)
);
Использование внешних ключей обеспечивает сохранение ссылочной целостности базы данных при изменении и удалении записей. Если бы таблицы заказы и клиенты не были связаны, то при удалении записи из таблицы клиенты в таблице заказы могли остаться ссылки на клиента, о котором уже нет сведений. Этот факт обычно расценивается как аномалия удаления. В случае определения в таблице заказы внешнего ключа ID_клиента из таблицы клиенты не удастся удалить клиента, если он сделал хотя бы один заказ. Если требуется удалить из базы данных все, что касается определенного клиента, то сначала удаляются записи в таблице заказы, а потом — в таблице клиенты.
Аналогичная ситуация может произойти и при обновлении данных. Например, в таблице клиенты вы изменили идентификатор клиента, имеющего заказы, и, таким образом, внешнему ключу теперь не на что ссылаться. Эго аномалия изменения. В данном случае необходимо сначала добавить новую запись в таблицу клиенты, указав в ней необходимое значение ID_клиента, затем изменить в таблице заказы все старые значения ID_клиента на то, которое вы только что ввели в новой записи таблицы клиенты, а затем удалить из таблицы клиенты запись со старым идентификатором клиента.
Чтобы в таблицах, связанных внешним ключом, не делать модификацию данных в несколько этапов, в выражении CONSTRAIN FOREIGN KEY можно использовать дополнительные ключевые слова:
ON DELETE CASCADE (при удалении каскадировать | Установить NULL);
ON UPDATE CASCADE | SET NULL (при обновлении каскадировать | установить NULL).
Здесь вертикальная черта не является элементом синтаксиса, а лишь разделяет возможные варианты ключевых слов.
Так, при использовании ON DELETE CASCADE в случае удаления записи со значением первичного ключа, которое имеется во внешнем ключе другой таблицы, соответствующие записи удаляются автоматически из двух таблиц. Например, при удалении из таблицы клиенты записи о клиенте, имеющем заказы, в таблице Заказы также будут удалены все записи, ссылающиеся на данного клиента. Чтобы данная стратегия выполнялась, таблица заказы должна быть определена следующим образом:
CREATE TABLE Заказы (
ID_заказа INTEGER,
ID_Клиента INTEGER,
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES Клиенты (ID_клиента)
ON DELETE CASCADE
);
Однако на практике обычно предпочитают сначала убедиться, что клиент не имеет заказов, и лишь затем вычеркнуть его из справочника.
Вариант SET NULL обычно используется при обновлении данных. Например:
CREATE TABLE Заказы (
ID_заказа INTEGER,
ID_клиента INTEGER,-
CONSTRAINT FOREIGN KEY ID_клиента REFERENCES Клиенты (ID_клиента)
ON UPDATE SET NULL
);
В данном случае при изменении (в том числе и при удалении) в таблице клиенты записи, на которую ссылается внешний ключ таблицы Заказы, значения внешнего ключа устанавливаются в NULL. Однако этот вариант не сработает, если на столбец Заказы. ID_клиента наложено ограничение NOT NULL. Обычно так и бывает, поскольку при оформлении заказа клиент должен быть обязательно указан. Поэтому, на всякий случай, лучше использовать ключевые слова ON UPDATE CASCADE.
Примечание
Некоторые СУБД (например, PostgreSQL) допускают комбинирование дополнительных ключевых слов. Например:
ON UPDATE SET NULL ON DELETE CASCADE
Удаление таблиц
Удалить таблицу из базы данных можно следующим образом:
DROP TABLE имятаблицы;
Разумеется, при удалении таблицы теряются и все содержащиеся в ней данные. Во время работы с базой данных нередко создаются таблицы для временного хранения данных, полученных на каком-то промежуточном этапе. Рано или поздно такие таблицы подлежат удалению. Однако можно забыть это сделать. Кроме того, промежуточные таблицы, создаваемые приложениями, могут остаться в базе данных из-за сбоев. Поэтому для создания временных таблиц лучше использовать оператор CREATE TEMPORARY TABLE, а не CREATE TABLE. Временная таблица, созданная с помощью CREATE TEMPORARY TABLE, автоматически уничтожается по окончании сеанса работы с базой данных.
Модификация таблиц
Разработка базы данных обычно происходит итеративно. Редко когда удается сразу оптимально определить структуру входящих в нее таблиц, чтобы потом их не переделывать. Кроме того, уже готовая база данных со временем может пополняться новыми таблицами, которые связываются с уже имеющимися. А установка новых связей требует коррекции параметров в старых таблицах. Модификация таблицы — это изменение ее структуры, т. е. добавление, удаление и переименование столбцов, а также изменение их типов и размеров.
Язык SQL обладает специальными средствами модификации таблиц. Если бы их не было или если отказаться их применять, то изменение структуры таблицы можно выполнить следующим образом:
1. Создать новую рабочую таблицу с необходимой структурой.
2. С помощью оператора INSERT INTO вставить в рабочую таблицу данные из исходной таблицы.
3. Удалить исходную таблицу.
4. Переименовать рабочую таблицу, присвоив ей имя исходной. Все предыдущие операции можно выполнить посредством рассмотренных ранее операторов SQL. Переименование таблицы можно сделать средствами программной среды разработки базы данных, Если же ограничиться только операторами SQL, то вместо переименования придется выполнить следующее:
1. Создать новую таблицу с именем исходной и с такой же структурой, как у рабочей таблицы.
2. С помощью оператора INSERT INTO вставить в новую таблицу данные из рабочей таблицы.
3. Удалить рабочую таблицу.
Как видите, изменение структуры таблицы обычными операторами SQL довольно трудоемко. Значительно проще изменить структуру таблицы с помощью оператора ALTER TABLE (изменить таблицу). С помощью дополнительных ключевых слов можно выполнить следующие операции:
ADD COLUMN — добавить столбец;
DROP COLUMN — удалить столбец;
ALTER COLUMN — изменить тип, размер и ограничение столбца;
RENAMECOLUMN — переименовать столбец;
RENAME то — переименовать таблицу.
Типичной задачей изменения структуры таблицы является добавление столбца. Эго можно сделать с помощью SQL-выражения с таким синтаксисом:
ALTER TABLE имяТаблицы ADD COLUMN имяСтолбца тип(размер)
Например:
ALTER TABLE Студенты
ADD COLUMN Appec CHAR(25);
Добавленный столбец оказывается последним в таблице, т. е. занимает крайнюю правую позицию. Иногда это неудобно. Пусть, например, столбец имя в таблице занимает первую позицию, а ранее забытый и позднее добавленный столбец Фамилия — десятую позицию. Чтобы выборка данных из этой таблицы выглядела привычным образом, приходится специально указывать необходимое расположение столбцов в операторе SELECT, например,
SELECT имя, Фамилия FROM
Выражение ADD COLUMN может использоваться в выражении ALTER TABLE несколько раз- — для каждого отдельного столбца. При определении параметров добавляемого столбца можно указать ограничение для него. Следующее SQL-выражение добавляет в таблицу студенты столбец ID_ст и объявляет его первичным ключом:
ALTER TABLE Студенты
ADD COLUMN ID_ст INTEGER PRIMARY KEY;
Не следует забывать, что первичный ключ в таблице может быть только один. Если в таблице уже есть первичный ключ, то добавление столбца как первичного ключа не будет выполнено. Если вам требуется переназначить первичный ключ, то сначала необходимо скорректировать соответствующим образом параметры уже имеющегося столбца, объявленного как первичный ключ, а затем добавить новый столбец с параметром PRIMARY REY. Подробнее об ограничениях было рассказано ранее. Коррекцию столбца можно выполнить так: либо сначала удалить его, а затем добавить новый столбец с требуемыми параметрами либо изменить параметры существующего столбца с помощью оператора ALTER COLUMN.
Чтобы удалить столбец из таблицы, достаточно выполнить SQL- выражение со следующим синтаксисом;
ALTER TABLE имяТаблицы DROP COLUMN имяСталбца;
При удалении столбца удаляются все содержащиеся в нем данные. Если удаляемый столбец является первичным ключом, на который ссылается внешний ключ из другой таблицы, то удаление не будет выполнено. В этом случае вам придется сначала скорректировать данные в другой таблице.
Например, следующее выражение удаляет из таблицы Студент столбец Примечание.
ALTER TABLE Студенты
DROP COLUMN Примечание;
Для изменения параметров существующего столбца, таких как тип, размер и ограничение, применяются ключевые слова ALTER COLUMN:
ALTER TABLE имяТаблицы ALTER COLUMN имяСтолбца тип(размер) [ограничение];
Здесь квадратные скобки указывают, что заключенное в них выражение не является обязательным.
В следующем примере в таблице студент уже существующий столбец ФИО приобретает новые параметры. А именно будучи символьным, он получает увеличение длины до 50 символов и становится первичным ключом:
ALTER TABLE Студенты
ALTER COLUMN ФИО CHAR(50) PRIMARY- КЕУ;
Разумеется, если в таблице уже имеется первичный ключ (например, столбец ID_студента), то сделать первичным ключом еще один столбец не удастся. Кроме того, не следует забывать, что при преобразовании типов могут быть потеряны данные. Так, если вы преобразуете столбец символьного типа, содержащий фамилии или адреса, в числовой тип, то все данные будут потеряны. При уменьшении размера символьного столбца его значения могут оказаться обрезанными справа. Таким образом, следует очень внимательно изменять параметры столбцов, содержащих некоторые данные.
Переименовывать столбцы приходится редко, поскольку в выборках и представлениях всегда можно присвоить им нужные псевдонимы. Тем не менее, переименовать столбцы и таблицу можно с помощью оператора ALTER TABLE с ключевыми словами RENAME COLUMN….. TO (переименовать столбец... в).
Для переименования столбца используется следующий синтаксис:
ALTER TABLE имя Таблицы RЕNАМЕ COLUMN имяСтолбца TO новоеИмяСтолбца;
При переименовании столбца его прежний тип, размер и ограничение сохраняются.
Для переименования таблицы используется похожий синтаксис:
ALTER TABLE имяТаблицы RENAME TO новоеИмяТаблицы;
Изменение структуры таблицы является небезопасной операцией с точки зрения возможности потери данных. Если вы не вполне уверены, что получите требуемый результат, то лучше использовать резервное копирование данных, поступая следующим образом:
1. Создайте временную таблицу с помощью оператора CREATE
TEMPORARY TABLE.
2. Скопируйте во временную таблицу данные из таблицы, структуру которой вы собираетесь модифицировать. Это можно сделать с помощью оператора INSERT INTO.
3. Далее можно поступить двумя способами: • изменить структуру исходной таблицы так, как вам требуется. Этот способ лучше применять при коррекции структуры, не связанной с изменениями существующих ограничений на столбцы и/или на таблицу в целом;
• удалить исходную таблицу и создать новую под тем же именем и с требуемой структурой. Я предпочитаю использовать этот способ, если требуется изменить ограничения.
4. Скопируйте данные из временной таблицы в таблицу с новой структурой, используя оператор INSERT INTO. Больше временная таблица не нужна, она будет автоматически удалена по окончании сеанса работы с базой данных.
Рассмотрим пример. Пусть имеется таблица студенты (ID_студента,
ФИО, специальность), в которой первичным ключом является ФИО,
Требуется изменить первичный ключ, а именно сделать первичным ключом столбец ID_студента. Кроме того, необходимо в столбце ФИО запретить неопределенные значения, а также добавить столбец примечание неограниченной длины. Чтобы решить эту задачу, можно выполнить следующую последовательность запросов:
CREATE TEMPORARY TABLE stud_tmp(
ID_студента INTEGER,
ФИО CHAR(20),
Специальность CHAR(15)
);
INSERT INTO stud_tmp
SELECT * FROM Студенты;
DROP TABLE Студенты;
CREATE TABLE Студенты (
ID_студента INTEGER PRIMARY KEY,
ФИО CHAR(20) NOT NULL,
Специальность CHAR(15),
Примечание VARCHAR);
INSERT INTO Студенты
SELECT * FROM stud_tmp;