Определение идентификационной колонки (Identity)

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

Тем не менее, SQL Server предоставляет возможность определить колонку, которая будет содержать глобально уникальные значения (так называемые глобальные идентификационные номера–GUID). Тип данных колонки должен быть uniqueidentifier.

Создание таблиц средствами TRANSACT SQL

Чтобы создать таблицу, необходимо, прежде всего, определить ее имя. Имя таблицы можно указать по одному из трех сценариев. В самом банальном случае, когда таблица создается в текущей базе данных, можно просто указать имя таблицы (не больше 128 символов). Если вы принадлежите к стандартной роли сервера sysadmin или стандартным ролям базы данных db_owner и db_ddladmin, то можете создавать объекты для других пользователей. При этом в процессе создания необходимо указать в качестве владельца требуемое имя учетной записи. При этом имя таблицы будет выглядеть как имя_владельца.имя таблицы.

CREATE TABLE [ data base [owner]. | owner. ] name

({<column_definition>

| column_name AS computerd_col_exspr

|<table_contraint>})

[ON {filegroup | DEFAULT}]

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

Определение каждой колонки таблицы, в синтаксисе команды обозначенное как <column_definition>, имеет следующий формат:

column_definition

{column_name data_type}

[DEFAULT constant

| [IDENTITY [(seed, increment) [NOT FOR REPLICATION]]]

[ROWGUIDCOL]

[<column_contraint>]}

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

DEFAULT – определяет значение по умолчанию (constant_expression), которое будет использовано, если при вводе строки явно не указано другое значение.

IDENTITY – предписывает системе осуществлять заполнение колонки автоматически. Если вы решили использовать это ключевое слово, необходимо также указать начальное значение (seed) и приращение (increment). В случае, когда указано NOT FOR REPLICATION, эта колонка не будет автоматически заполняться для строк, вставляемых в таблицу в процессе репликации, так что эти строки сохранят свои значения.

ROWGUIDCOL – данная колонка будет использоваться для хранения глобального идентификационного номера.

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

column_contraint

{[NULL | NOT NULL]

|[{ PRIMARY KEY | UNIQUE }

[CLUSTERED | NONCLUSTERED]

[WITH FILLFACTOR = fill factor]

[ON {file | DEFAULT}

|[[FOREIGN]

REFERENCES ref_table ]

Наложение ограничения на значение колонки должно начинаться с ключевого слова CONSTRAINT, после которого необходимо указать имя ограничения на зна­чение. Для каждого ограничения желательно указать, допустимо ли для колонки значение NULL, выбрав соответствующее ключевое слово (NULL или NOT NULL). После этого требуется определить тип ограничения:

PRIMARY KEY – определяет колонку как первичный ключ таблицы. В качестве альтернативы можно определить колонку как уникальную, воспользовавшись ключевым словом UNIQUE. При необходимости можно также указать, будет ли индекс, создаваемый для данного ограничения, кластерным (ключевое слово CLUSTERED) или некластерным (NONCLUSTERED). Однако необходимо помнить, что кластерный индекс можно определить только для одного ограничения, поэтому требуется решить, с каким ограничением (первичный ключ или уникальная колонка) вы будете его использовать. Если вы создаете индекс, необходимо также указать степень заполнения его страниц (ключевое слово WITH FILLFACTOR).

FOREIGN KEY – определяет колонку как внешний ключ таблицы. Одновременно, используя ключевое слово REFERENCES, необходимо указать имя таблицы, с которой будет связана создаваемая таблица. Дополнительно потребуется указать ее колонки, которые будут связаны с данной колонкой.

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

Однако можно определять ограничения на значения колонок и другим спосо­бом – через определение ограничений на уровне всей таблицы:

[CONTRAINT constraint_name]

{[{PRIMARY KEY | UNIQUE}

[CLUSTERED | NONCLUSTERED]

{(column [....n])}

[ON FILEGROUP | DEFAULT}] ]

| FOREIGN KEY [(column [..n])]

REFERENCES ref_table [(ref_column [...n])]

[NOT FOR REPLICATION]

|CHECK (search_conditions)}

Ключевые слова и аргументы имеют смысл, полностью аналогичный описанным ранее.

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

имя_колонки AS выражение

Выражение может включать имена колонок, функции и арифметические операции.

Изменение структуры таблицы при помощи Transact-SQL

Более сложный способ изменения структуры таблицы предлагает Transact-SQL, в котором для этого предусмотрена специальная команда:

ALTER TABLE table

{[ALTER COLUMN

column_name {new_data_type [(precision [, scale])]

[NULL | NOT NULL]

| {ADD | DROP} ROWGUIDCOL}]

|ADD

{[<column_definition> ]

column_name AS computed_column_expression}[,...n]

[WITH CHECK | WITH NOCHECK] ADD

{<table_contraint>}[...n]

|DROP

[CONSTRAINT] contraint_name | COLUMN column }[....n]

{CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name[,...n]}

{ENABLE | DISABLE} TRIGGER {ALL | trigger_name[....n]}}

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

Изменение определения колонки.

Данная операция осуществляется с использованием ключевого слова ALTER COLUMN, после которого помещается имя изменяемой колонки (column_name). Вы можете изменить тип данных колонки (new_data_type), размерность (precision) и точность (scale). При желании можно указать, разрешено ли колонке содержать значения NULL. В этом случае обязательно нужно указать тип данных для колонки, даже если вы не хотите его изменять (просто укажите существующий тип данных). Если вы определяете для колонки свойство NOT NULL, необходимо предварительно позаботиться о том, чтобы на момент изменения колонка не содержала ни одного значения NULL.

Добавление в таблицу новой колонки.

Для определения новой колонки необходимо использовать ключевое слово ADD. За ним следует описание колонки, которое имеет такой же формат, как и при создании колонки с помощью команды CREATE TABLE. Здесь же можно наложить на таблицу но­вые ограничения на значения колонок. Определив ключевое слово WITH CHECK, вы предписываете системе при добавлении новых ограничений на значения колонок FOREIGN KEY или CHECK осуществлять проверку данных в таблице на соответствие этим ограничениям. По умолчанию данная про­верка проводится для всех вновь создаваемых ограничений. Когда выпол­нение подобной проверки не требуется, необходимо использовать ключе­вое слово WITH NOCHECK.

Удаление колонок из таблицы.

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

– колонки, задействованные в индексе;

– колонки, полученные в результате репликации;

– колонки, для которых определены любые ограничения на значения;

– колонки, для которых определены значения по умолчанию;

– колонки, связанные с правилом.

Управление ограничениями на значения колонок.

Иногда бывает необхо­димо отключить ограничения на значения колонок FOREIGN KEY или CHECK. Отключение конкретного ограничения (NOCHECK CONSTRAINT) означает, что при вводе новых строк данные не будут проверяться на соответствие это­му ограничению. Когда снова потребуется сделать ограничение активным, используйте ключевое слово CHECK CONSTRAINT. При необходимости вы мо­жете управлять всеми ограничениями сразу. Для этого необходимо использовать ключевое слово ALL.

Управление триггерами.

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

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

Для удаления таблиц средствами Transact SQL используется команда DROP TABLE. Перед удалением необходимо убедиться, что удаляемая таблица не имеет взаимосвязи с другими таблицами.

Удаление записей из таблиц.

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

Удаление данных из таблицы выполняется построчно. За одну операцию можно выполнить удаление как одной строки, так и нескольких тысяч строк. Если необходимо удалить из таблицы все данные, то можно удалить саму табли­цу. Естественно, при этом будут удалены и все данные, хранящиеся в ней. Этот способ следует использовать в самых крайних случаях. Чаще всего для удаления данных используется команда DELETE, удаляющая строки таблицы. Синтаксис команды DELETE следующий:

DELETE

[FROM ]

{ table_name WITH (<table_hintlimited> [,..n])

| view name

| rowset_function_limited }

[ FROM {<table_source>} [,...n] ]

[WHERE

{ <search_condition>

| { [ CURRENT OF

{ { [ GLOBAL ] cursorname }

| cursor_variable_name }] }]

[OPTION (<query_hint> [,...n])]

При работе с командой DELETE необходимо определить диапазон строк, кото­рый будет уничтожен. Для определения этого диапазона используются обычные запросы, описанные ранее в этой главе. Разделы, используемые в команде DELETE, были описаны в разделе «Выборка данных».

Предварительно можно просмотреть список удаляемых строк, если заменить ключевое слово DELETE на SELECT.