Часть 2. Работа с таблицами

1. Создание таблиц

Таблицы создаются командой create table.

Создавать таблицы может лю­бой пользователь, имеющий на это права или обладающий ролью владельца базы или системного администратора.

Приступая к созданию таблицы, необходимо иметь ответы на две группы

вопросов:

Группа 1: (обеспечивает поддержку целостности данных)

1. Выявить обязательные данные;

2. Определить ограничения доменов полей;

3. Обеспечить целостность сущностей;

4. Обеспечить ссылочную целостность;

5. Обеспечить выполнение требований конкретного предприятия (требований бизнес-правил).

С учетом результатов ответов на вопросы группы 1 формируются ответы на следующие вопросы.

Группа 2:

2. Как будет называться таблица?

3. Как будут называться столбцы (поля) таблицы?

4. Какие типы данных будут закреплены за каждым столбцом?

5. Какой размер памяти должен быть выделен для хранения каждого столбца?

6. Какие столбцы таблицы требуют обязательного ввода?

7. Из каких столбцов будет состоять первичный ключ?

Базовый формат оператора create table имеет вид:

<определение_таблицы> : : =

CREATE TABLE имя_таблицы

{ ( имя_столбца тип_данных [ NOT NULL ] [ UNIQUE] )

[DEFAULT <значение>]

[CHECK (<условие_выбора>)] [, …n]}

[CONSTRAINT имя_ограничения]

/* Блок определения первичного и альтернативных ключей*/

[PRIMARY KEY (имя_столбца [ ,...n] )

{[UNIQUE (имя_столбца [ ,...n]) }

/* Блок определения внешних ключей*/

{[FOREIGN KEY (имя_столбца_внешнего_ключа [,…n])

REFERENCES имя_род_таблицы [ (имя_столбца_род__таблицы [ , … n ] ) ]

/* Блок определения ссылочной целостности данных*/

[MATCH { PARTIAL | FULL

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

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

{ [СНЕСК (<условие_выбора>) ] [,…n] })

Представленная версия оператора создания таблицы включает сред­ства определения требований целостности данных и ряд других требований.

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

Пояснения:

1. Необязательная фраза DEFAULT предназначена для задания принимаемою по умол­чанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.

2. Ограничения для доменов полей

Каждый столбец имеет собственный домен - некоторый набор до­пустимых значений.

Стандарт SQL предусматривает два различных меха­низма определения доменов.

Первый состоит в использовании предло­жения CHECK, позволяющего задать требуемые ограничения для столбца или таблицы в целом, а второй предполагает применение оператора CREATE DOMAIN.

Соблюдение бизнес-правил. Обновления данных в таблицах могут быть ограничены существую­щими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE

3. Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.

4. Первичный ключ таблицы должен иметь уникальное непустое зна­чение в каждой строке. Стандарт SQL позволяет задавать подобные тре­бования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз.

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

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

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

Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY а фраза REFERENCES определяет имя роди­тельской таблицы.

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

Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблице, то эти действия зависят от правил поддержки ссылочной целостности, указанных во фразах on update и ON delete предложения FOREIGN key.

При определении таблицы предложение FOREIGN KEY может указы­ваться произвольное количество раз.

Определитель MATCH позволяет уточнить способ обработки значе­ния null во внешнем ключе.

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

• CASCADE - выполняется удаление строки из родительской шблицы, сопровождающееся стоматическим удалением всех ссылаюшихся на нее строк дочерней таблицы;

• SET NULL - выполняется удаление строки из родительской таблицы. а во внешние ключи всех ссылающихся на нее строк дочерней таб­лицы записывается значение null;

• SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;

• NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании и внешнего ключа фраза ON DELETE опущена. Те же самые правила применяются в языке SQL и тогда, когда значе­ние потенциального ключа родительской таблицы обновляется.

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

<определение_таблицы> ;:=

CREATE TABLE имя_таблицы

(имя_столбца тип_данных [NULL | NОТ NULL ] [,..n]);

Ключевое слово NULL используется для указания того, что в данном столбце могут содержаться значения NULL.

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

По умолчанию стандарт SQL предполагает наличие ключевого слова NULL.

Выше приведен упрощенный вариант оператора CREATE TABLE стандарта SQL. Его полная версия приводится при обсуждении вопросов ограничения целостности данных.

Пример 2. Создаем таблицу Товары в соответствии со структурой, определенной на практических занятиях. В эту таблицу добавим новое вычисляемое поле Цена_ед_ товара.

CREATE TABLE Товары

(

ID_товара INT IDENTITY Not Null , /*нумерация начнется с 1*/

Название_товара VARCHAR(50) Not Null,

Категория VARCHAR(50) Not Null,

Ед_измерения VARCHAR(10) Not Null,

Цена_ед_товара AS Поставки.Цена_ед_товара*1.2 /*вычисляемый столбец*/

)

Теперь рассмотрим более развернутый вариант синтаксиса команды create table.

Пример 3. Синтаксис команды create table

CREATE TABLE [имя БД . [вдаделец] . ] имя таблицы

(<Столбец> <тип данных>

[ [ DEFAULT <выражение для вычисления константы>]

| [IDENTITY [(нач.значение, приращение) [NOT FOR REPLICATION] ]]]

[ROWGUIDCOL]

[COLLATE <collation name>]

[NULL | NOT NULL] [<column constraints>]

| [column_name AS computed_column_expression]

| [<table_constraint>]

[ON {<filegroup> | DEFAULT}]

[TEXTIMAGE _ON { <filegroup> | DEFAULT} ]

Имена таблиц

Имя таблицы должно быть не больше 128 символов. Они должны быть уни­кальными по отношению к владельцу: две таблицы с одинаковыми именами не могут принадлежать одному владельцу. Но у другого пользователя может быть таблица с тем же именем.

Столбцы

Таблица может содержать до 1024 столбцов. Для каждого столбца определя­ются, как минимум, имя и тип данных.

Имена столбцов имеют длину до 128 символов и являются уникальными в пределах таблицы. Имена столбцов со­ответствуют обычным правилам выбора имен идентификаторов и состоят из букв, цифр и служебных символов (пробел, @ и #).

Типы данных

Могут быть любыми из перечня типов данных, используемых в SQL Server. При выборе типа данных столбца будьте внимательны. Например, для хране­ния данных типа Int необходимо больше места, чем для Tinyint.

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

Если целое значение в столбце никогда не превышает 10, логичнее выбрать Tinyint.

Параметры NULL и NOT NULL

Если столбец создается с параметром null, то при вводе данных столбец можно оставить пустым. Если столбец содержит обязательные данные, его следует создавать с параметром NOT NULL. По умолчанию столбцы создаются с параметром NOT NULL.

Уникальные идентификаторы

Базы данных требуют, чтобы в базе существовал способ однозначной иден­тификации записей.

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

Использование столбцов счетчика IDENTITY

Столбец счетчика представляет собой столбец, автоматически генерирующий уникальные значения с некоторым приращением, например: 1, 2, 3,... или 1, 4, 7, 10.

Ключевое слово IDENTITY позволяет указанный столбец использовать для идентификации записей (это не ключ).

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

Столбец идентификации должен быть числовым - integer, bigint, smallint, tinyint, numeric (p, 0 ) или decimal (p, 0).

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

Следует иметь в виду, что столбцы идентификации IDENTITY и столбцы первичного ключа PRIMARY KEY это не одно и то же, т.к. значения столбцов идентификации не всегда уникальны: нумерация столбцов счетчика может содержать пропуски, обусловленные незавершенными транзакциями или непредвиденными сбоями сервера или база размещена на нескольких серверах.

Опция ROWGUIDCOL

Свойство столбца ROWGUIDCOL в сочетании с типом данных UNIQUEIDENTIFIER обеспечивает глобальную уникальность значений указанного столбца для всех копий базы данных на всех компьютерах в сети.

Для генерации таких идентификаторов используется время в милли­секундах, номера сетевых плат, а также другие параметры компьютера.

Такая возможность бывает особенно полезной при работе с несколькими ба­зами данных, содержимое которых впоследствии требуется объединить.

Пример таблицы со столбцов с глобально-уникальным идентификатором:

USE Торговая_фирма

CREATE TABLE Клиенты

(ID_Клиента UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

DEFAULT (newid( )),

Название фирмы nvarchar(100) NOT NULL,

Адрес nvarchar(100) NOT NULL)

В этом примере используется функция Newid( ), которая всякий раз создает новый иден­тификатор при вставке новой записи.

Опция COLLATE

Устанавливает

1. порядок сортировки данных в таблице;

2. чувствительность к регистру;

3. отдельные зависимости от выбранной кодовой страницы.

Способ сортировки по умолчанию устанавливается при инсталляции СУБД SQL Server. Но в рамках отдельной базы данных эти параметры можно изменять (например, на уровне столбца).