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);

Ограничения для таблиц

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

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

CREATE TABLE Студент (

Фамилия   CHAR(20),

Имя        CHAR (15),

Отчество CHAR (20),

Специальность INTEGER,

Примечание  VARCHAR,

CONSTRAINT PRIMARY KEY (Фамилия, Имя, Отчество)

);

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

INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность)

VALUES (' Петров ', ' Петр ', ' Петрович ', 5);

INSERT INTO Студент (Фамилия, Имя, Отчество, Специальность)

VALUES (' Петров ', ' Петр ', ' Иванович ', 5);

Внешние ключи

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

Пусть в базе данных имеются две таблицы:

Заказы (ID_заказа, ID_клиента)- содержит сведения о том, какие заказы сделал тот или иной клиент;

Клиенты (ID_клиента, Имя, Адрес, Телефон)- сведения о клиентах (справочник).

В таблице Клиенты столбец ID_клиента является первичным клю­чом, т. е. его значения отличны от NULL и уникальны. В таблице Заказы столбец 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 | SET NULL (при удалении каскадировать | установить 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.

 

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

Удалить таблицу из базы данных можно следующим образом:

DROP TABLE имяТаблицы;

Разумеется, при удалении таблицы теряются и все содержащиеся в ней данные. Во время работы с базой данных нередко создают­ся таблицы для временного хранения данных, полученных на каком-то промежуточном этапе. Рано или поздно такие таблицы подлежат удалению. Однако можно забыть это сделать. Кроме того, промежуточные таблицы, создаваемые приложениями, могут остаться в базе данных из-за сбоев. Поэтому для созда­ния временных таблиц лучше использовать оператор CREATE TEMPORARY TABLE,а не CREATE TABLE.

 

Модификация таблиц

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

Язык SQL обладает специальными средствами модификации таблиц. Если бы их не было или если отказаться их применять, то изменение структуры таблицы можно выполнить следующим образом:

1. Создать новую рабочую таблицу с необходимой структурой.

2. С помощью оператора INSERT INTO вставить в рабочую таблицу данные из исходной таблицы.

3. Удалить исходную таблицу.

4. Переименовать рабочую таблицу, присвоив ей имя исходной.

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

1. Создать новую таблицу с именем исходной и с такой же структурой, как у рабочей таблицы.

2. С помощью оператора INSERT INTO вставить в новую таблицу данные из рабочей таблицы.

3. Удалить рабочую таблицу.

Как видите, изменение структуры таблицы обычными операто­рами SQL довольно трудоемко. Значительно проще изменить структуру таблицы с помощью оператора ALTER TABLE (изменить таблицу). С помощью дополнительных ключевых слов можно выполнить следующие операции:

– ADD COLUMN - добавить столбец;

– DROP COLUMN - удалить столбец;

– ALTER COLUMN - изменить тип, размер и ограничение столбца;

– RENAME COLUMN - переименовать столбец;

– RENAME ТО - переименовать таблицу.

Типичной задачей изменения структуры таблицы является добав­ление столбца. Это можно сделать с помощью SQL-выражения с таким синтаксисом: