Глава 9. Создание, изменение и удаление таблиц.

Эта глава вводит нас в область SQL называемую - DDL(Data Definition Language - язык определения данных), где создаются объекты данных SQL. Мы рассмотрим создание, изменение, и удаление таблиц и индексов. Индексы позволяют делать поиск в БД более эффективным и, иногда, устанавливают ограничения на вводимую в базу данных информацию.

9.1. Оператор create table

В самом общем виде синтаксис команды создания таблицы может быть представлен следующим образом:

CREATE TABLE <Имя таблицы>

(<Имя столбца> <Тип данных> <Ограничения на столбец>

[{, <Имя столбца> <Тип данных> <Ограничения на столбец> …}]

[<Ограничения на таблицу>]

)

Где:

<Имя таблицы> складывается из следующих элементов

[ database_name . [ schema_name ] . | schema_name . ] table_name

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

schema_name — Имя схемы, которой принадлежит новая таблица.

table_name — Имя новой таблицы. Имена таблиц должны соответствовать правилам для идентификаторов. Аргумент table_name может состоять не более чем из 128 символов, за исключением имен локальных временных таблиц (имена с префиксом номера #), длина которых не должна превышать 116 символов.

<Имя столбца> — Имя столбца в таблице. Имена столбцов должны соответствовать правилам для идентификаторови быть уникальными в данной таблице. Аргументcolumn_name может содержать от 1 до 128 символов. При создании столбцов с типом данных timestamp аргумент column_name может быть пропущен. Если аргумент column_name не указан, столбцу типа timestamp по умолчанию присваивается имя timestamp. Если имя столбца состоит из нескольких слов (содержит пробелы), оно заключается в квадратные скобки [ ].

<Тип данных> — Указывает тип данных столбца. Тип данных может быть одним из следующих.

• CHAR(n) — символьные строки постоянной длины в n символов (максимальная длина 8000 символов, не UNICODE). При задании данного типа под каждое значение всегда отводится n символов, и если реальное значение занимает менее чем n символов, то СУБД автоматически дополняет недостающие символы пробелами.

• NUMERIC[(n,m)] (DECIMAL[(n,m)]) — точные числа, здесь n — общее количество цифр в числе, m — количество цифр слева от десятичной точки. Диапазон -10³⁸-1 .. 10³⁸-1

• BIGINT, INT, SMALLINT, TINYINT — целые числа длиной 8, 4, 2 и 1 байт соответственно. TINYINT — целое без знака.

• FLOAT[(n)](REAL) — числа большой точности, хранимые в форме с плавающей точкой. Здесь n — число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется конкретной реализацией.

• VARCHAR(n) — строки символов переменной длины символов (максимальная длина 8000 символов, не UNICODE).

• TEXT(n) — символьные строки, место выделяется страницами размером 8 Кбайт (не UNICODE)

• NCHAR(N), NVARCHAR(n), NTEXT(n) — строки локализованных символов постоянной и переменной длины (4000 символов, UNICODE).

• BIT — один бит (допускает NULL-значение).

• BINARY(n), VARBINARY(n) — строки битов постоянной и переменной длины (8000).

• DATE, TIME, DATETIME, SMALLDATETIME — календарная дата и время.

• TIMESTAMP(точность) — временная метка, уникальное внутри БД значение.

• IMAGE — двоичные данные переменной длины.

Либо тип может быть создан с помощью инструкции CREATE TYPE. Большинство коммерческих СУБД поддерживают еще дополнительные типы данных, которые не специфицированы в стандарте.

<Ограничения на столбец> — Ограничения на данные вводимые в данный столбец.

<Ограничения на таблицу> — Ограничения, накладываемые на несколько столбцов одновременно.

Пример:

Создание простейшей таблицы без ограничений:

CREATE ТABLE Справочник_вид_блюда(

ID_Вид INT,

Вид VARCHAR(20) );

Виды ограничений:

1. Исключение Null-значений.

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

CREATE ТABLE Справочник_вид_блюда (

ID_Вид INT NOT NULL,

Вид VARCHAR(20) );

2. Ограничение целостности сущности (PRIMARY KEY).

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

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

Поскольку ограничение PRIMARY KEY гарантирует уникальность данных, оно часто определяется для столбцов-счетчиков.

Простой первичный ключ (ограничение на столбец)

CREATE ТABLE Справочник_вид_блюда (

ID_Вид INT NOT NULL PRIMARY KEY,

Вид VARCHAR(20) );

Составной первичный ключ (ограничение на таблицу)

CREATE ТABLE Состав (

Блюдо INT NOT NULL ,

Продукт INT NOT NULL,

Вес INT,

PRIMARY KEY(Блюдо, Продукт) );

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

CREATE TABLE Сотрудник(

[Табельный номер] INT NOT NULL PRIMARY KEY,

[серия паспорта] INT,

[номер паспорта] INT,

[дата выдачи] DATETIME,

ФИО VARCHAR(25) NOT NULL,

Адрес VARCHAR(100),

UNIQUE ([серия паспорта], [номер паспорта]) );

Для ограничений PRIMARY KEY по умолчанию создается кластеризованный индекс (CLUSTERED), а для ограничений UNIQUE — некластеризованный (NONCLUSTERED).

В инструкции CREATE TABLE параметр CLUSTERED можно задать только для одного ограничения. Если для ограничения UNIQUE указан параметр CLUSTERED, и, кроме того, указано ограничение PRIMARY KEY, то для PRIMARY KEY применяется по умолчанию значение NONCLUSTERED.

3. Ограничения целостности CHECK.

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

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

Пример использования

Задание шаблона для ввода (ограничение на столбец)

Номер CHAR (6) CHECK(Номер LIKE '[A-Z][A-Z][A-Z][1-9][0-9][FM]')

Ограничение на вводимые данные (значение только из списка) (ограничение на столбец)

Город VARCHAR(10) CHECK (Город IN ('Ярославль', 'Москва', 'Иваново'))

Ограничение на группу полей (ограничение на таблицу)

CHECK (Комиссионные <0.5 OR Город = 'Ярославль')

4. Ограничение по умолчанию (DEFAULT).

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

Пример использования

Город VARCHAR(20) DEFAULT('Ярославль')

5. Ограничение ссылочной целостности (FOREIGN KEY).

Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной БД. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY, применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом. Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES. Данные в столбцах, определенных в качестве внешнего ключа, могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Единственным требованием остается соответствие столбцов по типу и размеру данных.

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

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

Столбцы внешнего ключа могут содержать значение NULL, однако проверка на ограничение FOREIGN KEY игнорируется. Внешний ключ может быть проиндексирован, тогда сервер будет быстрее отыскивать нужные данные. Внешний ключ определяется как при создании, так и при изменении таблиц.

Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION, принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE.

Синтаксис:

[ FOREIGN KEY ]

REFERENCES referenced_table_name [ (ref_column [ , ... ]) ]

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT}]

        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

Где:

referenced_table_name — имя таблицы, на которую ссылается ограничение FOREIGN KEY.

( ref_column [, ... ] ) — столбец или список столбцов из таблицы, на которую ссылается ограничение FOREIGN KEY. Можно не указывать, если ссылка идет на первичный ключ.

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } — определяет операцию, которая производится над строками создаваемой таблицы, если эти строки имеют ссылочную связь, а строка, на которую имеются ссылки, удаляется из родительской таблицы.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } — указывает, какое действие совершается над строками в изменяемой таблице, когда у этих строк есть ссылочная связь и строка родительской таблицы, на которую указывает ссылка, обновляется.

Параметр по умолчанию — NO ACTION.

NO ACTION — ядро СУБД формирует ошибку, и выполняется откат операции удаления (обновления) строки из родительской таблицы.

CASCADE — если из(в) родительской таблицы удаляется (обновляется) строка, соответствующие ей строки удаляются(обновляются) из(в) ссылающейся таблицы.

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

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

Параметр ON DELETE CASCADE нельзя указывать, если в таблице уже существует триггер ON DELETE.

Действие ON UPDATE CASCADE не может быть определено, если в изменяемой таблице уже существует триггер INSTEAD OF ON UPDATE.

Пример использования.

Внешний ключ — ограничение на столбец.

CREATE ТABLE Состав (

Блюдо INT NOT NULL FOREIGN KEY REFERENCES Блюда(ID_Блюда),

Продукт INT NOT NULL REFERENCES Продукты,

Вес INT,

PRIMARY KEY (Блюдо, Продукт));

Внешний ключ — ограничение на таблицу.

CREATE ТABLE Заказы (

ID_Заказ INT NOT NULL,

Блюдо INT NOT NULL,

Количество_порций INT NOT NULL CHECK(Количество_порций >0),

Дата DATE NOT NULL,

PRIMARY KEY(ID_Заказ, Блюдо, Дата),

FOREIGN KEY (Блюдо, Дата) REFERENCES Меню (Блюдо, Дата) ) ;

Рекурсивная ссылка — внешний ключ ссылается на первичный ключ той же таблицы.

CREATE TABLE Сотрудник(

[Табельный номер ] INT NOT NULL PRIMARY KEY,

[серия паспорта] INT,

[номер паспорта] INT,

[дата выдачи] DATETIME,

ФИО VARCHAR(25) NOT NULL,

Адрес VARCHAR(100),

Руководитель INT NULL REFERENCES Сотрудник,

UNIQUE ([серия паспорта], [номер паспорта]) );

Для столбца Руководитель нельзя использовать ограничение NOT NULL, так как ни одну запись нельзя будет добавить в таблицу.