Создание баз данных и таблиц

В этой главе описывается процесс создания баз данных и таблиц,

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

 

·         Общее описание баз данных и таблиц;

·         Как создавать и использовать базы данных;

·         Как создавать таблицы и выделять в них столбцы;

·         Как пользователь может определить свой тип данных;

·         Как изменять уже сделанные таблицы;

·         Как получить информацию о базах данных и таблицах.

 

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

 

 

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

Далее перечислены объекты, которые SQL Сервер рассматривает как объекты базы данных:

 

·         Таблицы;

·         Правила;

·         Умолчания (Defaults);

·         Сохраненные процедуры;

·         Триггера;

·         Вьюверы;

·         Ограничения ссылочной целостности данных;

·         Условные ограничения целостности (условия отбора данных).

 

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

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

В столбцах задается тип данных, которые хранятся в таблице, в то время как индексы описывают способ организации этих данных в таблице. SQL Сервер не рассматривает индексы как объекты базы данных, поэтому их нет в списке sysobjects (системные объекты). Столбцы и типы данных также рассматриваются в этой главе, в то время как индексы обсуждаются в главе 11.

 

 

Целостность данных в базе означает их корректность (непротиворечивость) и полноту. Для сохранения целостности пользователь может наложить ограничения на процессы вставки, удаления или обновления данных в базе. Например, для сохранения целостности данных в базе pubs2 требуется, чтобы книга, находящаяся в таблице titles, имела издателя, сведения о котором хранятся в таблице publishers. Пользователь не сможет записать книгу в таблицу titles с неправильным издателем, так как это нарушает целостность данных в базе pubs2.

Язык Transact-SQL предоставляет несколько механизмов для сохранения целостности данных в базе, таких как правила, умолчания, индексы и триггеры. Они позволяют сохранять следующие виды целостности данных:

 

·           Присутствие значения - Для сохранения этого вида целостности требуется, чтобы в указанном столбце всех строк таблицы присутствовали некоторые значения данных, т.е. в данном столбце запрещается использование неопределенного значения (null value). В операторе creat table (создание таблицы) можно ввести это ограничение для любого столбца таблицы.

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

·           Уникальность значения - Для сохранения этого вида целостности требуется, чтобы значения данных в указанном столбце таблицы не повторялись. Для выполнения этого ограничения можно использовать индексы.

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

 

Согласованность (consistensy) данных в базе является еще одним видом целостности, который обсуждается в главе 17.

Как альтернативу использованию правил, умолчаний, индексов и триггеров, Transact-SQL позволяет вводить серию ограничений целостности непосредственно в операторе создания таблицы, как этого требует стандарт языка SQL. Эти ограничения целостности рассматриваются далее в этой главе.

 

 

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

Пользователь может определить свои права доступа (статус) путем выполнения следующей команды:

 

sp_helprotect имя_пользователя

 

где имя_пользователя должно совпадать с именем пользователя, использованном при его входе в систему (логировании).

Чтобы сделать эксперименты с базой данных pubs2 максимально удобными, в ее системной таблице sysusers указан пользователь “guest” (гость), которому предоставляются широкие права для работы с этой базой.

Механизм, связанный со словом “guest”, предоставляет права доступа к базе pubs2 любому пользователю, который вошел в систему, т.е. выполнил команду login и следовательно его имя было записано в список master..syslogin, и позволяет ему создавать и удалять такие объекты базы данных, как таблицы, индексы, правила, процедуры и т.д. Имя “guest” позволяет также использовать некоторые сохраненные процедуры, создавать свои типы данных, делать запросы к базе данных и изменять в ней данные.

Чтобы использовать базу данных pubs2, нужно выполнить команду use. После этого SQL Сервер проверит находится ли имя данного пользователя в списке пользователей этой базы (pubs2..sysuser). Если имени в этом списке нет, то сервер рассматривает этого пользователя как гостя Если имя находится в списке, то сервер просит подтвердить его и в этом случае права пользователя могут отличаться от прав, предоставленных “гостю”. При выполнении примеров из этой главы предполагается, что пользователь имеет права “гостя”.

 

 

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

Когда инсталлируется (запускается) SQL Сервер, то он содержит только следующие системные базы данных:

 

·           Базу данных master, которая используется для управления базами данных пользователей и в целом поддерживает работу SQL Сервера;

·           Базу данных sybsystemprocs, которая содержит системные сохраненные процедуры;

·           Временную базу данных tempdb, которая используется для хранения временных объектов, включая временные таблицы, в названии которых имеется префикс “tempdb..”;

·           Базу данных model, которая используется SQL Сервером как шаблон для создания новых баз данных.

 

Кроме того, системный администратор может проинсталлировать (загрузить) демонстрационную базу данных pubs2 и синтаксическую базу данных sybsyntax с помощью процедуры isql и сценариев (scripts) SQL, которые расположены в директории scripts. База данных pubs2 используется как основа для большинства примеров, приведенных в документации по SQL Серверу. База данных sybsyntax содержит всю синтаксическую информацию по командам и процедурам и может вызываться командой sp_syntax.

Базы данных pubs2 и sybsyntax являются пользовательскими. Все необходимые пользователям данные, из-за чего собственно и используются системы управления базами данных, хранятся также в пользовательских базах данных. SQL Сервер управляет каждой базой данных, основываясь на системных таблицах. Таблица data dictionary (словарь данных) в базе данных master, а также в других базах данных,  рассматривается как системная.

 

 

Большую часть времени пользователь работает с уже существующими базами данных. Команда доступа к уже сущестующей базе данных имеет следующий вид:

 

use название_базы_данных

 

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

 

use pubs2

 

Эта команда предоставляет пользователю доступ к базе данных pubs2 только в том случае, если он известен системе как пользователь этой базы. В противном случае, SQL Сервер выдает сообщение об ошибке. Владелец базы данных должен выполнить системную процедуру sp_adduser, чтобы предоставить пользователю доступ к ней.

Большинство пользователей могут посмотреть системные таблицы в базе данных master благодаря гостевому механизму, который был ранее описан. Если имени пользователя нет в базе данных master, то он рассматривается как “гость”. Пользователь “гость” добавляется в базу данных master в процессе ее инсталляции.

Владелец базы данных “dbo” (DataBase Owner) может добавить пользователя “гостя” в любую пользовательскую базу данных с помощью системной процедуры sp_adduser. Системный администратор автоматически становится владельцем любой базы данных, которую он использует. Более подробная информация по этому поводу приведена в Руководстве системного администратора и Справочном руководстве по SQL Серверу.

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

 

 

Пользователь может создать свою базу данных, если системный администратор разрешил ему использовать команду creat database (создать базу данных). Необходимо использовать базу данных master, чтобы создать новую базу данных. Во многих организациях все базы данных создаются системным администратором. Создатель базы данных является ее собственником (владельцем). Если база данных создается некоторым пользователем, то он может передать права на владение этой базой другому пользователю с помощью системной процедуры sp_changedbowner.

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

Поскольку каждая база данных требует значительного места для хранения даже в том случае, если она содержит мало данных, то многие пользователи не имеют прав на использование команды creat database. Если читатель относится к этой категории, то он может пропустить этот раздел и перейти к разделу, в котором описывается процесс построения таблиц (“Создание таблиц”).

Простейшая форма команды для создания базы данных имеет следующий вид:

 

creat database название_базы_данных

 

Например, чтобы создать базу данных newpubs нужно использовать базу данных master, а не базу pubs2 и выполнить команду:

 

creat database newpubs

 

Название базы данных должно быть уникальным для SQL Сервера и оно должно удовлетворять соглашениям для идентификаторов, указанным в главе 1 этой книги. SQL Сервер может управлять 32767 базами данных. В каждый момент времени можно создать только одну базу данных. Любой базе данных может быть выделено до 32 сегментов.

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

Создание новой базы данных регистрируется в таблицах sysdatabase и sysusages базы данных master.

Полный синтаксис команды создания базы данных имеет следующий вид:

 

create database название_базы_данных

[on {default | database_device} [= размер] 

    [, database_device [= размер]]...]

[log on database_device [= размер]

    [, database_device [= размер]]...]

[with override]

[for load]

 

В данной главе описываются все опции этой команды за исключением опции override. Информация об этой опции дается в Руководстве системного администратора.

 

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

 

Необязательное предложение on позволяет пользователю указать, где сохранить базу данных и память какого размера под нее выделить. Если здесь указывается слово default (по умолчанию), то база данных записывается на доступное устройство хранения (database device), которое выбирается из набора устройств, выделяемых по умолчанию и указанных в таблице sysdevices базы данных master. С помощью системной процедуры sp_helpdevice можно просмотреть список устройств, которые выделяются по умолчанию.

 

Замечание. Системный администратор управляет распределением памяти, основываясь на статистике работы и других соображениях. Поэтому перед созданием базы данных необходимо сообщить об этом системному администратору.

 

Для выделения, например, 5 мегабайт (Мб) для хранения базы данных используйте конструкцию on default = размер как в следующем примере:

 

create database newpubs

on default = 5

 

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

Например, с помощью следующего оператора создается база данных newpubs и для нее выделяется 3 Мб на устройстве pubsdata и 2 Мб на устройстве newdata:

 

create database newpubs

on pubsdata = 3, newdata = 2

 

Если предложение on и спецификация размера опущены, то под базу данных по умолчанию выделяется 2 Мб на одном из устройств, указанных в таблице sysdevices.

Размер памяти, выделяемый под базу данных, может изменяться от 2 Мб до 2^23 мегабайт.

 

 

 

 

За исключением случаев, когда создаются очень маленькие, некритические базы данных в операторе create database следует указывать расширение log on. В этом случае создается журнал транзакций (запросов на изменение) для этой базы данных на отдельном устройстве хранения данных. Для этого имеются следующие основания:

 

·           Это позволяет воспользоваться командой dump transaction (трассировка транзакций) вместо команды dump database (трассировка базы данных), таким образом экономя время и память ;

·           Это позволяет выделить фиксированный размер памяти для журнала транзакций, тем самым предотвращая соревнование за память между активными процессами базы данных.

 

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

 

·           Это повышает производительность;

·           Это гарантирует полное восстановление в случае выхода из строя жесткого диска.

 

По следующей команде выделяется память под журнал транзакций для базы данных newpubs на логическом устройстве “pubslog” размером 1 Мб:

 

create database newpubs

on pubsdata = 3, newdata = 2

log on pushlog = 1

 

Замечание. Когда используется расширение log on журнал  транзакций для этой базы данных записывается на сегмент, который называется “logsegment”. Если когда нибудь возникнет необходимость расширить память для журнала, то придется использовать команду alter database (изменить базу данных) и в некоторых случаях вызывать системную процедуру sp_extendsegment. Дальнейшие детали можно посмотреть в Справочном руководстве SQL Сервера и Руководстве системного администратора.

 

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

 

Необязательное предложение for load (для загрузки) вызывает создание базы данных, которая может использоваться только для хранения дампов базы данных (database dump). Эта опция используется при восстановлении после ошибок или при переносе базы данных с одной машины на другую. См. Руководство системного администратора, в котором более подробно описывается эта опция.

 

 

Удаление базы данных выполняется командой drop database. Эта команда удаляет базу данных и все ее содержимое из системы, освобождает занимаемую ее память и удаляет все ссылки на нее из базы данных master.

Эта команда имеет следующий вид:

 

drop database название_базы_данных [,название_базы_данных]...

 

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

Можно одной командой удалить несколько баз данных, как это было указано в общем виде этой команды, например:

 

drop database newpubs, newdb

 

Испорченную (damaged) базу данных нельзя удалить командой drop database. Для этого нужно использовать команду dbcc dbrepair.

 

 

Если память, отведенная под базу данных, полностью заполнена данными, то в нее нельзя добавить новую информацию или обновить ее содержимое. Если произошло переполнение памяти, то владелец базы данных может увеличить ее размер с помощью команды alter database. По умолчанию права доступа на выполнение этой команды имеет только владелец базы данных и их нельзя передать другому лицу. Эта команда должна выполняться в базе данных master.

По умолчанию размер базы данных увеличивается на 2 Мб, причем память на это увеличение берется из стандартного пула устройств (default pool). Следующий оператор добавляет 2 Мб к памяти, выделенной для базы данных newpub:

 

alter database newpubs

 

В команде alter database можно указать точное количество мегабайтов (минимум 1 Мб), необходимых для расширения, и устройство, на котором должна быть выделена память. Полный синтаксис этой команды имеет следующий вид:

 

alter database название_базы_данных

[on {default | database_device} [= размер] 

    [, database_device [= размер]]...]

[log on database_device [= размер]

    [, database_device [= размер]]...]

[with override]

[for load]

 

Предложение on в этой команде такое же как и в команде создания базы данных creat database. Предложение for load  здесь можно использовать только в том случае, если оно было указано при создании этой базы данных.

В следующем операторе на 2 Мб увеличивается память для базы данных newpubs на устройстве pubsdata и на 3 Мб на устройстве newdata:

 

alter database newpubs

on pubsdata = 2, newdata = 3

 

Когда используется команда alter database, чтобы добавить память на устройстве, которое уже используется для хранения этой базы данных, то все сегменты на этом устройстве могут использовать добавленный фрагмент. Таким образом, все объекты этой базы данных, расположенные на прежних сегментах, могут расти за счет добавленной памяти. Максимальное количество сегментов, которые могут выделяться одной базе данных, равно 32.

Когда используется команда alter database, чтобы добавить память на устройстве, которое еще не использовалось для хранения этой базы данных, то сегменты system и default появляются на новом устройстве. Если необходимо изменить это распределение, то нужно использовать процедуру sp_dropsegment, чтобы удалить нежелательные сегменты с этого устройства.

 

Замечание. Для автоматического перераспределения системных и стандартных (default) сегментов можно использовать процедуры sp_extendsegment, logsegment и device_name.

 

Информация об опции with override дается в Руководстве системного администратора.

 

 

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

Число таблиц в базе данных может доходить до 2 миллиардов.

 

 

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

Для создания таблицы следует использовать команду creat table, которая в своей простейшей форме имеет следующий вид:

 

create table название_таблицы

(название_столбца тип_данных)

 

Например, следующей командой создается таблица names с одним столбцом some_name, имеющем фиксированную длину в 11 байтов:

 

create table names

(some_name char(11))

 

Одна таблица может содержать до 250 столбцов. Если была выполнена команда set quoted_identifier on, то название таблицы и названия столбцов можно заключать в кавычки. В противном случае, нужно придерживаться правил написания идентификаторов, которые были изложены в первой главе “Введение”. Названия столбцов одной таблицы должны быть уникальными, но могут совпадать в различных таблицах одной базы данных.

Для каждого столбца должен быть указан тип данных. Слово “char”, расположенное после названия столбца в предыдущем примере, указывает тип данных,  которые будут находиться в этом столбце. Типы данных обсуждаются в шестой главе “Создание и использование типов данных”.

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

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

 

 

Команда creat table создает новую таблицу в текущей открытой базе данных. Название таблицы должно быть уникальным для каждого пользователя.

Пользователь может создавать временные таблицы путем добавления знака фунта (#) или префикса “tempdb..” к названию таблицы в операторе creat table.

Временные таблицы, названиям которых предшествует знак фунта, доступны только в процессе текущего сеанса работы SQL Сервера и по окончанию сеанса уничтожаются. Первые 13 символов в названии временной таблицы, включая знак фунта, должны быть уникальными. SQL Сервер добавляет к названию таких таблиц 17-байтовый суффикс.

Временные таблицы с префиксом “tempdb..” хранятся в базе данных tempdb и могут использоваться на протяжении нескольких сеансов работы SQL Сервера. SQL Сервер не изменяет названий таких таблиц. Эти таблицы будут сохраняться либо до перезагрузки SQL Сервера, либо пока владелец такой таблицы не удалит ее с помощью команды drop table. Временные таблицы не восставливаются (после сбоя). Следующий оператор создает временную односеансовую таблицу:

 

create table #authors

(au_id char (11))

 

А оператор:

 

create table tempdb..authors

(au_id char(11))

 

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

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

Хотя названия таблиц должны быть уникальными у каждого пользователя, у различных пользователей они могут совпасть. Например, пользователь по имени “jonah” и пользователь по имени “sally” могут создать таблицу с названием info. Пользователи, которые получили разрешение на использование обеих таблиц, должны указывать их расширенные названия jonah.info и sally.info. Салли должна указывать расширенное название при обращении к таблице info другого пользователя, хотя к своей собственной таблице она может обращаться без расширения.

 

 

Команда creat table имеет следующий вид:

 

create table [база_данных.[владелец].]название_таблицы (название_столбца тип

    [default {константное_выражение | user | null}]

    {[{identity | null | not null}]

    | [[constraint название_ограничения]

        {{unique | primary key}

           [clustered | nonclustered]

        [with {fillfactor | max_rows_per_page} = x]

        [on название_сегмента]

         | references [[база_данных.]владелец.]таблица_ссылок

             [(столбец_ссылок)]

         | check (условие_отбора)}]}...

| [constraint название_ограничения]

    {{unique | primary key}

        [clustered | nonclustered]

         (название_столбца [{,название_столбца}...])

        [with {fillfactor | max_rows_per_page} = x]

        [on название_сегмента]

    | foreign key (название_столбца [{,название_столбца}...])

         references [[база_данных.]владелец.]таблица_ссылок

           [(столбец_ссылок [{,столбец_ссылок}...])]

    | check (условие_отбора)}

[{, {следующий_столбец | следующее_ограничение}}...])

+

[with max_rows_per_page = x][on название_сегмента]

 

В операторе creat table определяется каждый столбец таблицы. В нем должно быть приведено название столбца и его тип и указано на возможность появления в этом столбце неопределенных значений. В случае необходимости в нем указывается, какой столбец будет счетчиком (автоинкрементным). В этом операторе также должны быть определены ограничения целостности на уровне отдельных столбцов и ограничение целостности для всей таблицы. В таблице может быть несколько ограничений для отдельного столбца и для всей таблицы.

Например, чтобы создать таблицу titles в базе данных pubs2, нужно выполнить следующий оператор:

 

create table titles

(title_id tid,

 title      varchar(80) not null,

 type      char(12),

 pub_id   char(4) null,

 price     money null,

 advance money null,

 royalty   int null,

 total_sales int null,

 notes     varchar(200) null,

 pubdate  datetime

 contract bit not null)

 

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

 

 Замечание. Расширение on название_сегмента в операторе создания таблицы позволяет поместить таблицу на определенный сегмент памяти, название которого связано с конкретным устройством хранения или с набором таких устройств. Перед использованием этого расширения необходимо узнать у системного администратора или владельца базы данных список сегментов, которые можно использовать, поскольку некоторые сегменты могут быть зарезервированы для опеределенных таблиц или индексов, исходя из соображений производительности системы или из других соображений.

 

 

Для каждого столбца таблицы можно определить возможно ли в нем указание неопределенного значения (null value). Заметим, что неопределенное значение отличается от числа “ноль” и от знака “пробел”. Значение NULL указывает, что в данное поле не было введено никаких данных, поэтому оно интерпретируется как “неопределенная” или “неизвестная” величина. Другими словами, оно означает, что  по каким-то причинам пользователь не вводил данных в это поле. Например, неопределенное значение в столбце price (цена) таблицы titles отнюдь не свидетельствует о том, что данная книга распространяется свободно, а просто в данный момент нам эта цена еще неизвестна или издатель еще не установил ее.

Если пользователь не ввел никакого значения в поле столбца, для которого было указано ключевое слово null, то SQL Сервер автоматически запишет туда значение NULL. Пользователь может и непосредственно ввести это значение в столбец, для которого указано ключевое слово null, независимо от типа данных, расположенных в этом столбце. Однако, надо быть осторожным, когда неопределенное значение вводится в строковые (char) столбцы. Если слово “null” будет указано там в кавычках, то оно будет интерпретироваться SQL Сервером как строка символов, а не как значение NULL.

Если в операторе creat table для данного столбца не указаны ключевые слова null или not null, то SQL Сервер будет использовать для него текущий установленный режим неопределенности. По умолчанию вначале в системе устанавливается запрет на неявное появление неопределенных значений NOT NULL. Для совместимости со стандартом SQL, в котором разрешено по умолчанию дописывать неопределенные значения, следует обратиться к системной процедуре sp_dboption и поменять режим неопределенности, установив опцию allow nulls by default (разрешить неявную запись неопределенных значений) в состояние true (истина).

В столбцах, для которых указаны ключевые слова not null, SQL Сервер будет ожидать ввода данных во всех полях. Если в такое поле не будут введены никакие данные, то будет выдано сообщение об ошибке.

Значение по умолчанию (default) означает значение, которое заносится в данное поле автоматически, если пользователь не ввел в него никаких данных. Значение по умолчанию может указываться как для столбцов с ключевым словом null, так и для столбцов с ключевыми словами not null. Определение значения по умолчанию для данного столбца равносильно его первоначальному вводу в каждое поле этого столбца. Однако, нельзя определять по умолчанию значение NULL в столбце, для которого указаны также ключевые слова not null. Пользователь может указать неопределенное значение в ограничении default оператора creat table или использовать оператор creat default. Ограничение default описывается позже в этой главе, а оператор creat default будет описан в главе 12.

Наличие ключевого слова null в определении столбца означает, что пользователь резервирует место в таблице для последующего ввода данных, которых он пока не знает. Например, в таблице titles в столбцах price, advance, royalty и total_sales разрешено использовать значение NULL.

Однако, в столбцах title_id (идентификатор книги) и title (название книги) это запрещено, поскольку отсутствие данных в этих полях бессмысленно и чревато ошибками. Не может быть книги, имеющей цену, но не имеющей названия, в то время как вполне возможно появление книги, имеющей название, но еще не имеющей цены.

В операторе creat table следует указывать ключевые слова not null в тех столбцах, в которых будет храниться наиболее важная информация по отношению к другим столбцам.

 

 

Каждая таблица может включать один столбец-счетчик. Столбец-счетчик (IDENTITY) содержит последовательные числа, которыми можно нумеровать заявки, работников, записи и т.д. SQL Сервер автоматически генерирует числа  в этом столбце, которые однозначно определяют (идентифицируют) каждую строку в таблице. Счетчик определяется с помощью ключевого слова identity (идентификация), которое указывается вместо ключевых слов null или not null в операторе creat table. (По определению столбец-счетчик не может содержать неопределенных значений). Этот столбец должен иметь числовой тип данных numeric и нулевую шкалу.

Точность представления чисел в этом столбце определяет диапазон их изменения. Максимальное значение равно 10^(точность) - 1. В следующем примере создается таблица со счетчиком, который может расти до числа 10^5 - 1 = 9999:

 

create table sales_daily

    (row_id numeric(5,0) identity,

     stor_id char(4) not null)

 

Пользователь может автоматически задавать столбец-счетчик с помощью опции auto_identity, тогда точность этого счетчика будет определяться конфигурационным параметром size of auto_identity. Для включения счетчика в неуникальный индекс, следует использовать опцию identity in nonique index.

 

 

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

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

 

sp_addtype ident, "numeric(5)", "identity"

 

Тогда определение столбца, который базируется на этом типе, выглядит следующим образом:

 

create table sales_monthly

    (row_id ident, stor_id char(4) not null)

 

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

 

 

Если пользователь определил столбец-счетчик, то он может не запоминать точное название этого столбца. В этом случае можно использовать ключевое слово syb_identity, дополненное названием таблицы там где это необходимо, для обращения к этому столбцу в операторах выбора, обновления и удаления. Например, для выбора строки, в которой значение счетчика row_id равно 30, можно использовать следующий запрос:

 

select * from sales_daily

    where syb_identity = 30

 

 

Вначале, когда в таблицу вводится первая строка, SQL Сервер записывает в столбце-счетчике значение 1 (единица). В каждую последующую строку в этом столбце записывается на единицу большее число, по сравнению с предыдущей строкой. Повторение транзакций, удаление строк и значение конфигурационного параметра identity grab size могут привести к образованию разрывов в цепочке значений этого столбца.

Ошибки сервера также могут привести к образованию разрывов в значениях счетчика. Величина этих разрывов, выраженная в процентах от максимального размера таблицы, будет зависеть от конфигурационного параметра identity burning set factor. Этот параметр устанавливается во время инсталляции и может изменяться системным администратором.

 

 

Если перед названием таблицы в операторе creat table указывается знак фунта (#) или префикс “tempdb..”, то создается новая временная таблица.

Имеется два вида временных таблиц:

 

·           Таблицы, которые можно использовать на протяжении нескольких сеансов работы SQL Сервера.

 

Эти временные таблицы определяются с помощью префикса tempdb, который должен быть частью названия таблицы в операторе creat table. Например:

 

create table tempdb..my_temptb1

 

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

 

·           Таблицы, которые доступны только на протяжении текущего сеанса работы SQL Сервера.

 

Название таких таблиц должно начинаться со знака фунта (#). В этом случае в операторе создания таблицы достаточно указать только ее название, например:

 

create table #my_temptb1

 

SQL Сервер обеспечивает уникальность названий таких временных таблиц. Он укорачивает длинные названия таблиц до 13 символов (включая знак фунта) и удлиняет короткие названия до 13 символов путем добавления символов подчеркивания (_). Затем SQL Сервер добавляет к названию 17 разрядный числовой суффикс, который является уникальным для текущего сеанса работы. Такая таблица сохраняется до конца сеанса работы SQL Сервера или пока она не будет удалена ее владельцем с помощью оператора drop table.

Если перед названием таблицы нет знака фунта или префикса “tempdb..”  и пользователь не работает с временной базой данных tempdb, то будет создана постоянная таблица. Такая таблица будет оставаться в базе данных до тех пор, пока владелец не удалит ее явным образом.

Далее приведен пример создания односеансовой таблицы:

 

create table #myjobs

(task  char(30),

 start  datetime,

 stop  datetime,

 notes varchar(200))

 

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

Временные таблицы не восстанавливаются (not recoverable).

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

Имеется два способа добавления типа данных или любого другого объекта во временную базу данных tempdb. Чтобы добавить объект только на один сеанс, нужно вызвать процедуру sp_addtype, работая с базой данных tempdb. Для добавления постоянного объекта следует вызвать процедуру sp_addtype, находясь в базе данных model, и затем перезапустить SQL Сервер таким образом, чтобы база данных model перекопировалась в базу данных tempdb.

 

 

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

Например, непосредственно из базы pubs2 можно создать таблицу newtab в базе данных newpubs с помощью следующего оператора:

 

creat table newpubs..newtab (cоl l int)

 

Оператор создания таблицы заканчивается успешно, если сеансовая метка пользователя curread превосходит порог доступа (hurdle) к базе данных, в которой создается таблица.

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

 

 

Transact-SQL предоставляет два метода сохранения целостности данных в базе:

 

·           Путем определения правил, значений по умолчанию, индексов и триггеров;

·           Путем определения ограничений целостности в операторе создания таблицы.

 

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

Например, триггера обеспечивают более глубокую проверку ссылочной целостности по сравнению с ограничениями, определенными в операторе создания таблицы. Кроме того, ограничения, определенные в операторе создания таблицы, действуют только для этой таблицы. В отличии от правил и умолчаний, их нельзя связать с другой таблицей, кроме того пользователь может удалить или изменить эти ограничения только оператором alter table. Ограничения не могут содержать подзапросов или агрегирующих функций даже для той таблицы, где они определены.

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

Пользователь может создавать следующие типы ограничений:

 

·           Уникальные (unique) и главные (primary) ключи обеспечивают уникальность (неповторяемость) значений в заданных столбцах таблицы. Кроме того, главный ключ запрещает появление неопределенного значения в любой строке заданного столбца;

·           Ограничения ссылочной целостности (references) означают, что любые значения, которые хранятся в заданных столбцах, должны также встречаться в указанных таблицах и столбцах;

·           Условные ограничения (check constraints) налагают условия на значения, которые можно записывать в заданные столбцы.

 

Можно также вносить ограничения на появление в столбце неопределенного значения (ключевые слова null или not null) и на значение, которое записывается по умолчанию (предложение default). В разделе “Возможность записи неопределенных значений” уже давалась информация о ключевых словах null и not null.

Пользователь может задать сообщение об ошибке и связать его с ограничениями. Сообщение создается системной процедурой sp_addmessage, а его связывание с ограничениями производится процедурой sp_bindmsg. Более подробную информацию об этих процедурах читатель найдет в Справочном руководстве SQL Сервера.

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

 

 

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

Однако, ограничения которые относятся к нескольким столбцам, нужно определять на уровне всей таблицы. Например, в следующем операторе создания таблицы условное ограничение относится к двум столбцам pub_id и pub_name:

 

create table my_publishers

(pub_id          char(4),

 pub_name     varchar(40),

constraint my_chk_constraint

    check(pub_id in ("1389", "0736", "0877") or pub_name not like "Bad News Books"))

 

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

 

create table my_publishers

(pub_id        char(4) constraint my_chk_constraint

                   check(pub_id in ("1389", "0736", "0877")),

 pub_name   varchar(40))

 

В любом из этих случаев ключевое слово constraint и название ограничения указывать не обязательно. Условные ограничения более подробно будут описаны в одном из следующих разделов.

 

 

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

В качестве значений по умолчанию можно указать следующие величины:

 

·           константное_выражение - задает выражение, имеющее постоянное значение, в качестве значения по умолчанию. Это выражение не должно содержать названий столбцов или названий других объектов базы данных, но оно может включать встроенные функции, которые не обращаются к объектам базы данных;

·           user - задает имя пользователя в качестве значения по умолчанию. Тип данных столбца в этом случае должен быть либо char(30) либо varchar(30);

·           null - задает неопределенное значение в качестве значения по умолчанию. Это значение нельзя устанавливать, если для данного столбца было указано ключевое слово not null.

 

Например, следующий оператор создает таблицу со значениями по умолчанию, заданными для двух столбцов:

 

create table my_titles

(title_id        char(6),

 title            varchar(80),

 price           money       default null,

 total_sales    int            default 0)

 

Для каждого столбца таблицы можно указать только одно значение по умолчанию.

Устанавливать значения предложением default проще чем с помощью двухшагового метода, предусмотренного в языке Transact-SQL. В этом языке можно указать значение по умолчанию с помощью оператора creat default, а затем связать его со столбцом таблицы процедурой sp_bindefault.

 

 

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

Поле с уникальным ключом согласно стандарту SQL не может содержать неопределенных значений. По умолчанию SQL Сервер не допускает неопределенных значений в столбце (если не были произведены изменения процедурой sp_dboption), для которого не были указаны ключевые слова null или not null при его определении. В языке Transact-SQL, если пользователь определяет уникальный ключ, то тем самым в этом столбце допускается использование неопределенных значений, поскольку реализация уникальных индексов допускает их использование.

 

Замечание. Не следует создавать противоречий между ограничениями, вводимыми уникальными и главными ключами, и информацией, находящейся в системных процедурах sp_primarykey, sp_foreignkey и sp_commonkey. Для уникальных и главных ключей действительно создаются индексы, для определения атрибутов этих ключей. Процедуры sp_primarykey, sp_foreignkey и sp_commonkey определяют логические взаимосвязи между ключами (в таблице syskeys), которые возникают при создании индексов и триггеров.

 

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

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

 

create table my_sales

(stor_id         char(4),

 ord_num      varchar(20),

 date             datetime,

 unique clustered (stor_id, ord_num))

 

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

Пользователь может вводить ограничения по уникальному или главному ключу для формирования уникальных индексов (включая опции with fillfactor, with max_rows_per_page и on название_сегмента) для сохранения целостности данных. Однако индексы дают дополнительные возможности. Информация об индексах и связанных с ними опциях приводится в главе 11 “Создание индексов для таблиц”.

 

 

Ограничения ссылочной целостности (referential integrity) определяются для того, чтобы значения, записываемые в создаваемую таблицу, совпадали со значениями, которые расположены в таблице, на которую направлена “ссылка”. Ограничения ссылочной целостности будут выполнены, если имеет место одно из двух условий:

 

·           Если во всех указанных столбцах таблицы, на которую направлена ссылка, содержатся неопределенные значения;

·           Если все значения в указанных столбцах “ссылающейся” таблицы, (т.е. таблицы, где вводятся ограничения) совпадают с некоторыми значениями соответствующих столбцов таблицы, на которую направлена ссылка.

 

Например, следующий оператор создает таблицу с двумя ограничениями ссылочной целостности:

 

create table my_salesdetail

(stor_id         char(4),

 ord_num      varchar(20),

 title_id         char(6)        references my_titles(title_id),

 qty              smallint,

constraint salesdet_constr

        foreign key (stor_id, ord_num)

        references my_sales (stor_id, ord_num))

 

Первое ограничение будет выполняться, если каждая строка, записываемая в таблицу my_salesdetail содержит в столбце title_id значение, которое совпадает с некоторым значением в столбце title_id таблицы my_titles. В этом случае ссылка будет направлена из таблицы my_salesdetail (которая будет ссылающейся) в таблицу my_titles (на которую ссылаются). Второе ограничение (называемое salesdet_constr) будет выполняться, если значения, находящиеся в столбцах stor_id и ord_num одной строки таблицы my_salesdetail будут совпадать со значениями, расположенными в одноименных столбцах некоторой строки таблицы my_sales.

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

Ограничения ссылочной целостности, определяемые на уровне всей таблицы, должны включать определение ключа импортирования (foreign key) и названия нескольких столбцов. Названия столбцов в предложении references можно не указывать, только если со всеми сравниваемыми столбцами таблицы, на которую направлена ссылка, связаны главные ключи.

Со всеми сравниваемыми столбцами таблицы, на которую направлена ссылка, должны быть связаны уникальные индексы этой таблицы. Напомним, что пользователь может образовать уникальный индекс определением уникального или  главного ключа, либо с помощью оператора creat index (создать индекс). Типы данных, расположенные в сравниваемых столбцах ссылающейся таблицы, должны в точности совпадать с типами данных соответствующих столбцов таблицы, на которую направлена ссылка. Например:

 

create table test_type

(col1  char(4) not null 

references publishers(pub_id),

 col2  varchar(20) not null)

 

Тип данных столбца col1 в ссылающейся таблице test_type должен совпадать с типом данных столбца pub_id.

Пользователь должен иметь право создавать ссылки на таблицу, которая указывается в определении ссылочной целостности. Информация по поводу этих прав дается в Руководстве пользователя по безопасности (Security Features User’s Guide).

 

 

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

 

·           Список константных выражений, которому предшествует ключевое слово in (в);

·           Диапазон значений, заданный константными выражениями и ключевым словом between (между);

·           Набор условий, которым предшествует ключевое слово like (как) и которые могут содержать символы замены.  

 

Выражение может включать арифметические операции и встроенные в Transact-SQL функции. В условия отбора нельзя включать подзапросы, определения функций и спецификации целей (target specification).

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

 

 

create table my_new_publishers

(pub_id        char(4)

        check (pub_id in ("1389", "0736", "0877", "1622", "1756")

        or pub_id like "99[0-9][0-9]"),

pub_name    varchar(40),

city             varchar(20),

state            char(2))

 

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

 

 

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

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

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

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

 

1.         Решите из каких столбцов будет состоять таблица и какой у них должен быть тип данных, длина, точность представления данных и шкала.

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

3.         Выделите столбец под счетчик, если это необходимо.

4.         Решите какие столбцы могут содержать неопределенное значение.

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

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

7.         Выберите типы индексов, которые необходимы, и решите, где их использовать. Индексы будут обсуждаться в главе 11.

8.         Создайте таблицу и индексы с помощью команд creat table и creat index соответственно.

9.         Создайте новые значения по умолчанию и правила с помощью команд creat default и creat rule соответственно. Эти команды будут обсуждаться в главе 12.

10.     Свяжите значения по умолчанию и правила с таблицей с помощью системных процедур sp_bindefault и sp_bindrule соответствено. Если при создании таблицы были определены некоторые значения по умолчанию и правила, то они будут автоматически учитываться сервером, поэтому в этом случае не нужно обращаться к соответствущим системным процедурам. Эти системные процедуры будут обсуждаться в главе 14.

11.     Сделайте триггеры командой creat trigger. Триггеры будут обсуждаться в главе 15.

12.     Создайте вьюверы командой creat view. Вьюверы будут обсуждаться в главе 9.

 

 

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

Если пользователь решил выполнить все упражнения по созданию таблицы friends_etc самостоятельно, то он должен предупредить об этом системного администратора, чтобы не возникло конфликта с объектами, которые были созданы другими пользователями.

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

 

Таблица 7-1: Пример проектирования таблицы

 

Столбец	Тип данных	Неопред. ?	Индекс	Умолчание	Правило
pname	nm	Not null	nmind(составной)
sname	nm	Not null	nmind(составной)
address	varchar(30)	Null
city	varchar(30)	Not null		citydflt
state	char(2)	Not null		statedflt
zip	char(5)	Null	zipind	zipdflt	ziprule
phone	p#	Null			phonerule
age	tinyint	Null			agerule
bday	datetime	Not null		bdflt
sex	bit	Not null		sexdflt
debt	money	Not null		sexdflt
notes	varchar(30)	Null

 

 

Первые два столбца нашей таблицы отводятся для имени и фамилии. Для них указан тип данных nm. Перед там как создавать таблицу необходимо определить новые (пользовательские) типы данных, к которым относится также тип данных p# из столбца phone.

Тип данных nm определим как строковый тип данных переменной длины, с максимальной длиной 30 байтов. Тип данных p# определим как строковый тип данных с фиксированной длиной 10 байтов.

Определение этих типов происходит следующим образом:

 

execute sp_addtype nm, "varchar(30)"

execute sp_addtype p#, "char(10)"

 

 

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

Фраза Not Null означает, что в данное поле обязательно нужно записать некоторое значение, например в первых двух столбцах необходимо указать имя и фамилию. Данные, отличные от имен, не имеют смысла в этих столбцах. Кроме того, в столбце sex (пол) также не допускается неопределенное значение, поскольку это значение запрещено использовать в столбцах с типом данных bit.

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

 

 

Теперь уже можно написать оператор создания таблицы:

 

create table friends_etc

(pname       nm              not null,

 sname        nm              not null,

 address      varchar(30)    null,

 city           varchar(30)    not null,

 state         char(2)          not null,

 postalcode  char(5)         null,

 phone        p#               null,

 age           tinyint           null,

 bday         datetime        not null,

 sex           bit                 not null,

 debt         money           not null,

 notes        varchar(255)   null

 

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

 

 

Если установлена опция базы данных select into/bulkcopy, то для создания постоянной таблицы можно использовать оператор select into (выбрать в). Системный администратор может установить эту опцию с помощью системной процедуры sp_dboption. Чтобы определить состояние этой опции, нужно выполнить системную процедуру sp_helpdb.

Далее показан пример вызова этой процедуры и ее результат, когда установлена интересующая нас опция:

 

sp_helpdb pubs2

 

name    db_size   owner  dbid    created          status 

------  -------     ------  -----   -----------      ---------------------

pubs     2 MB     sa         5       Jun  3 1988    select into/bulkcopy

 

(Выбрана 1 строка)

 

device              size         usage

------------   ---------    --------------

master            2 MB         data and log

 

                    (Выбрана 1 строка)

 

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

Если установлена опция select into/bulkcopy, то для создания постоянной таблицы можно использовать оператор select into вместо оператора creat table. Оператор select into можно использовать для создания временных таблиц, даже если эта опция не установлена.

 

Замечание. Поскольку оператор select into не регистрируется в журнале, то рекомендуется использовать команду dump database, чтобы сохранить копию базы данных после выполнения оператора select into. Не следует пользоваться командой dump transaction, так как не допускается снятие копии журнала после незарегистрированной операции вместе с load transaction.

 

В отличие от вьюверов, которые показывают только часть таблицы, таблицы, созданные оператором select into, являются отдельными независимыми объектами. Подробное описание вьюверов можно посмотреть в главе 9.

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

Оператор select, в котором имеется предложение into позволяет создавать таблицы и сразу помещать в них данные, основываясь на существующих опеределениях данных, без явного процесса их определения.

В следующем примере приводится оператор select into вместе с результирующей таблицей. Эта таблица, называемая newtable, состоит из двух столбцов, которые выбираются из четырехстолбцовой таблицы publishers. Поскольку в этом примере нет предложения where, то в результирующую таблицу включаются все строки исходной таблицы publishers (но только по двум столбцам).

 

select pub_id, pub_name

into newtable

from publishers

 

(3 rows affected)

 

Сообщение SQL Сервера "3 rows affected" указывает на то, что в новую таблицу было включено три строки. Новая таблица выглядит следующим образом:

 

select *

from newtable

 

pub_id  pub_name

------  -----------------------

0736    New Age Books

0877    Binnet & Hardley

1389    Algodata Infosystems

 

                    (Выбрано 3 строки)

 

Таким образом, новая таблица содержит результаты оператора select. Она становится частью базы данных также как и породившая ее таблица. Предложение into особенно полезно для создания тестовых таблиц, копий существующих таблиц и создания маленьких таблиц из больших. Можно также использовать оператор select into для создания каркаса новой таблицы, не содержащей данных, путем указания тождественно ложного условия в предложении where. Например:   

 

select *

into newtable2

from publishers

where 1=2

 

(0 rows affected)

 

select *

from newtable2

 

pub_id    pub_name          city      state

------    --------------    --------  -----

 

(0 rows affected)

 

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

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

 

select type, "Total_amount" = sum(advance)

into #whatspent

from titles

group by type

 

(Выбрано 6 строк)

 

select * from #whatspent

 

 type                       Total_amount

 ----------------      ----------------

 UNDECIDED        NULL

 business                                25,125.00

 mod_cook             15,000.00

 popular_comp      15,000.00

 psychology           21,275.00

 trad_cook             19,000.00

 

(Выбрано 6 строк)

 

Надо всегда давать названия столбцам в операторе select into для всех результатов вычислений агрегирующих функций или результатов вычисления любых других выражений, таких как арифметические выражения (amount*2), конкатенации строк (lname+fname) или выражения со встроенными функциями SQL Сервера (lower(lname)). Далее приводится пример, содержащий конкатенацию строк:

 

select au_id,

    "Full_Name" = au_fname + ' ' + au_lname

into #g_authortemp

from authors

where au_lname like "G%"

 

(Выбрано 3 строки)

 

select * from #g_authortemp

 

 au_id                      Full_Name  

 --------                 -------------------------

 213-46-8915                           Marjorie Green

 472-27-2349                           Burt Gringlesby

 527-72-3246                           Morningstar Greene

 

(Выбрано 3 строки)

 

 

Для копирования столбца-счетчика из старой таблицы в новую достаточно включить этот столбец в список выбора или указать ключевое слово syb_identity. Новый столбец будет наследовать свойства счетчика за исключением следующих случаев:

 

·         В таблице указано более одного счетчика;

·         Столбец-счетчик задается как часть выражения;

·           В операторе выбора имеется предложение group by, или агрегирующие функции, или операция объединения или операция соединения.

 

 

Чтобы создать новый столбец-счетчик, следует расположить определение этого столбца перед предложением into в операторе select into. Заметим, что в этом определении нужно указать точность счетчика, но не указывать его шкалу:

 

select список_столбцов

название_счетчика = identity (точность)

into название_таблицы

from название_таблицы

 

Нельзя использовать оператор select into для создания таблицы с несколькими счетчиками. Если в этом операторе указать столбец-счетчик из прежней таблицы и создать новый, то будет выдано сообщение об ошибке.

Дополнительная информация о счетчиках приводится в разделах “оператор выбора” и “столбцы-счетчики” в Справочном руководстве SQL Сервера.

 

 

Таблицы удаляются из базы данных командой drop table. Эта команда имеет следующий общий вид:

 

drop table [база_данных.[владелец].] название_таблицы

  [, [база_данных.[владелец].] название_таблицы] ...

 

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

Только владелец таблицы имеет право удалить ее. Однако никто не может удалить таблицу, которая находится в работе, т.е. таблицу, к которой обращаются пользователи или прикладная программа. Командой drop table нельзя удалять системные таблицы как в базе данных master, так и в базе данных пользователя.

Как видно из синтаксиса этой команды, владелец таблицы может удалить ее и в другой базе данных.

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

Права на исполнение команд drop table и truncate table не могут передаваться другим лицам.

 

 

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

 

·           Можно добавить столбцы или ограничения целостности, удалить некоторые ограничения, изменить значения, подразумеваемые по умолчанию, с помощью команды alter table (изменить таблицу);

·           Можно изменить название таблицы или столбца, или любого другого объекта базы данных с помощью системной процедуры sp_rename.

 

 

Команда alter table позволяет произвести следующие изменения в существующей таблице:

 

·         Добавить столбцы (за исключением столбцов с типом данных bit);

·         Добавить ограничения целостности;

·         Удалить ограничения целостности;

·         Заменить значения по умолчанию, определенные для столбцов таблицы.

 

Команда alter table имеет следующий общий вид:

 

alter table [база_данных.[владелец].]название_таблицы

{add название_столбца тип_данных

    [default {константное_выражение | user | null}]

    {[{identity | null}]

    | [[constraint название_ограничения]

        {{unique | primary key}

            [clustered | nonclustered]

        [with {fillfactor | max_rows_per_page} = x]

        [on название_сегмента]

         | references [[база_данных.]владелец.]таблица_ссылок

             [(столбец_ссылок)]

         | check (условие_отбора)}]}...

    {[, следующий_столбец]}...

| add {[constraint название_ограничения]

    {unique | primary key}

        [clustered | nonclustered]

        (название_столбца [{, название_столбца}...])

        [with {fillfactor | max_rows_per_page} = x]

        [on название_сегмента]

| foreign key (название_столбца [{, название_столбца}...])

        references [[база_данных.]владелец.]таблица_ссылок

           [(столбец_ссылок [{, столбец_ссылок}...])]

    | check (условие_отбора)}

| drop constraint название_ограничения

| replace название_столбца default { константное_выражение | user | null}}

 

Общее число столбцов в таблице должно быть не больше 250, независимо от того, добавливаются ли они оператором alter table или определяются сразу оператором creat table.

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

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

 

Замечание. Если в сохраненной процедуре используется оператор выбора select *, обращающийся к таблице, которая была изменена, то процедура не будет выбирать данные из добавленных столбцов, даже если включена опция with recompile. В этом случае необходимо удалить процедуру и снова создать ее.

 

Например, можно добавить столбец к таблице friends_etc следующим образом:

 

alter table friends_etc

    add country varchar(20) null

 

Можно ввести также несколько ограничений целостности для нового столбца (или любого другого столбца) таблицы friends_etc:

 

alter table friends_etc

    add constraint no_old_country

        check (country not in ("GDR", "E. Germany", "East Germany"))

 

Если это ограничение больше не нужно, то его можно удалить следующим образом:

 

alter table friends_etc

    drop constraint no_old_country

 

При удалении ограничения нужно указать его название. Чтобы определить названия всех ограничений, связанных с данной таблицей, нужно вызвать системную сохраненную процедуру sp_helpconstraint. Эта процедура описывается в разделе “Использование процедуры sp_helpconstraint”.

Оператор alter table позволяет заменить значение, записываемое в столбец по умолчанию (или добавить такое значение, если его не было). Например:

 

alter table friends_etc

    replace country default "USA"

 

Информация о значениях по умолчанию и ограничениях целостности дается в разделе “Определение ограничений целостности для таблиц”.

 

 

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

Для переименования базы данных следует использовать системную процедуру sp_renamedb. Информацию об этой процедуре можно найти в Справочном руководстве SQL Сервера.

Обращение к процедуре sp_rename имеет следующий вид:

 

sp_rename название_объекта, новое_название

 

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

 

sp_rename friends_etc,  infotable

 

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

 

sp_rename "таблица.столбец", новое_название_столбца

 

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

 

sp_rename "таблица.индекс", новое_название_индекса

 

Здесь также не нужно указывать название таблицы с новым названием индекса.

Следующая команда показывает как переименовать тип данных пользователя tid на t_id:

 

exec sp_rename tid, "t_id"

 

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

 

 

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

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

 

 

Команды SQL Сервера grant (дать) и revoke (отменить) служат для управления правами доступа в системе. Можно предоставить различные права пользователям и группам пользователей с помощью команды grant или аннулировать их с помощью команды revoke. Эти команды могут использоваться для предоставления следующих прав:

 

·         Создание баз данных;

·         Создание объектов в базе данных;

·         Доступ к таблицам, вьюверам и столбцам;

·         Исполнение сохраненных процедур.

 

Некоторые команды могут исполняться в любое время любым пользователем без необходимости предоставления каких-либо прав. Другие (привелегированные) команды могут исполняться только пользователями, имеющими определенные права (например, только системным администратором), и эти права не могут передаваться другим лицам.

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

Владелец базы данных не получает автоматически права на использование объектов, принадлежащих другим пользователям. Однако владелец базы данных и системный администратор всегда могут приобрести любые права при условии идентификации владельца объекта с помощью команды setuser с последующим исполнением оператора grant или revoke.

Два вида прав могут быть предоставлены с помощью команд grant и revoke: права на доступ к объекту и права на создание объекта.

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

Следующий оператор предоставляет пользователям Марии и Джо права на использование команд insert и delete с таблицей titles:

 

grant insert, delete

on titles

to mary, joe

 

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

Следующий оператор аннулирует права Марии на создание таблиц и правил в текущей базе данных:

 

revoke creat table, creat rule

from mary

 

Полная информация об использовании команд grant и revoke для предоставления прав доступа к объектам и на создание объектов дается в Руководстве пользователя по безопасности.

 

 

SQL Сервер предоставляет несколько системных процедур для получения информации о базах данных, таблицах и других объектах базы данных. В этом разделе описываются четыре из них: sp_help, sp_helpdb, sp_helpconstraint, sp_spaceused.

Полная информация о системных процедурах дается в Справочном руководстве SQL Сервера.

 

 

Системная процедура sp_help предоставляет информацию об указанном объекте базы данных (из списка sysobjects), об указанном типе данных (из списка systypes) или о всех объектах и типах данных из текущей базы данных.

Вызов этой процедуры имеет следующий вид:

 

sp_help [название_объекта]

 

Далее приводится результаты вызова этой процедуры с таблицей publishers в качестве аргумента:

 

Name                               Owner        Type

--------------------------    -----------   --------

publisher                           dbo             user table

Data_located_on_segment          When_created

------------------------------     --------------------

default                                     Jan  1 1900 12:00AM

 

Column_name        Type        Length     Prec            Scale

-----------             -------      ------      -----           -----

pub_id                    char                        4              NULL     NULL

pub_name                              varchar   40            NULL     NULL         

city                                          varchar   20            NULL     NULL         

state                                        char                        2              NULL     NULL

 

Nulls      Default_name   Rule_name         Identity

-----      -------------      ---------            --------

    0       NULL                         NULL                 0

    1       NULL                         NULL                 0

    1       NULL                         NULL                 0

    1       NULL                         NULL                 0

 

index_name     index_description                            index_keys

-------------- ------------------------------------     ----------

pubind           clustered, unique located on default     pub_id

 

(1 row affected)

 

keytype     object       related_object  object_keys    related_keys

-------      ------      --------------    -----------    ---------------------------------

primary     publishers -- none --        pub_id,         *,*,*,*,*,*, *,*,*,*,*,*,*,*,*

foreign      titles         publishers        pub_id,         *,*,*,*,*,*,*, pub_id,*,*,*,*,*,*,*

 

(return status = 0)

 

Если процедура sp_help вызывается без аргумента, то выдается краткое описание каждого объекта из списка sysobjects с названием, владельцем и типом каждого объекта. Также выдается описание каждого пользовательского типа данных из списка systypes с названием, видом памяти, длиной, наличием неопределенного значения, названиями значений по умолчанию и правилами, связанными с каждым типом. В отчетах также сообщается, имеются ли в таблицах или вьюверах столбцы с ключами primary и foreign, установленных системными процедурами sp_ primarykey и sp_ foreignkey.

Процедура sp_help выдает также список всех табличных индексов, включая индексы, связанные с главными и уникальными ключами, определенные в операторах creat table или alter table. Однако эта процедура не сообщает об ограничениях целостности, определенных для таблиц. Информацию об ограничениях целостности можно получить с помощью системной процедуры sp_helpconstraint.

 

 

Системная процедура sp_helpdb предоставляет информацию об указанной базе данных или о всех базах данных, с которыми работает SQL Сервер. Она сообщает название базы, ее размер и количество используемых фрагментов памяти, выделенных этой базе данных с помощью операторов creat database или alter database. Эта процедура вызывается следующим образом:

 

sp_helpdb [название_базы_данных]

 

Далее приводится пример получения информации о базе данных pubs2 с помощью этой процедуры:

 

sp_helpdb pubs2

 

name    db_size   owner  dbid   created             status

-----   -------      ------  ----   --------------    -------------

pubs2   2 MB       sa        4     Jan 10 1988       no options set

 

(1 row affected)

 

device             size         usage

------------  ----------  --------------

 

pubsdev         2 MB       data + log

(1 row affected)

 

 

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

 

sp_helpconstraint  название_объекта [, detail ]

 

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

В следующем примере предполагается, что таблица states создавалась следующим оператором:

 

create table states

(rank                smallint,

 abbrev char(2),

 name               varchar(20)     null,

 population       int                    check (population > 1000000),

 constraint stateconstr primary key (rank, abbrev))

 

При вызове процедуры sp_helpconstraint будут выданы следующие ограничения целостности:

 

sp_helpconstraint states

 

name                                defn

---------------------------  ---------------------------------------

states_popula_1088006907  CHECK (population > 1000000)

stateconstr                          PRIMARY KEY INDEX (rank, abbrev):

                                           CLUSTERED,FOREIGN REFERENCE

 

(3 rows affected, return status = 0)

 

 

Пользователь может уточнить объем памяти, занимаемый таблицей, с помощью системной процедуры sp_spaceused. Синтаксис вызова этой процедуры имеет следующий вид:

 

sp_spaceused [название_объекта]

 

Эту системную процедуру можно использовать также для получения информации о памяти, занимаемой индексами, как это описывается в главе 11 “Создание индексов для таблиц”. Эта процедура сообщает о числе строк в таблице, о размере используемых этой таблицей страниц данных, о кластеризованных и некластеризованных индексах. Далее приводится пример получения информации о таблице titles:

 

sp_spaceused titles

 

name    rows   reserved    data   index_size   unused     

-------    -----    ----------    -----    -----------     ---------

titles     18      48 KB      6 KB   4 KB         38 KB

 

(Выбрано 0 строк)

 

Если эта процедура вызывается без параметра (аргумента), то она сообщает о суммарном объеме памяти, занимаемым всеми объектами базы данных.

[ << Назад ] [ Содержание ] [ Далее >> ]