Часть 3. Модификация таблиц

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

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

• добавление в таблицу нового столбца:

• удаление столбца из таблицы;

• добавление в определение таблицы нового ограничения;

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

• задание для столбца значения по умолчанию;

• отмена для столбца значения по умолчанию.

Оператор изменения таблицы имеет следующий обобщенный формат:

<изменение_таблицы> ::=

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

[ADD [COLUMN] имя_столбца

[ NOT NULL ] [UNIQUE]

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

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

[ DROP [ COLUMN ] имя_столбца [RESTRICT CASCADE ]]

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

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

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

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

[ MATCH { PARTIAL | FULL}

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

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

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

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

RESTRICT | CASCADE]]

[ALTER [COLUMN] SET DEFAULT <значение>]

[ALTER [COLUMN] DROP DEFAULT]

Здесь параметры имеют то же самое назначение, что и в определении

оператора CREATE TABLE.

Оператор alter table реализован не во всех диалектах языка SQL.

В некоторых диалектах он поддерживается, однако не позволяет

удалять из таблицы уже существующие столбцы,

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

Дополнение таблиц

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

Например:

ALTER TABLE Author

ADD

[ИНН] char (16) NULL

CONSTRAINT inn CHECK [ИНН] LIKE '78%',

[Пол] bit NULL

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

Новые столбцы могут представлять собой вычисляемые выражения и объяв­ляться с ограничениями уровня столбцов.

Удаление отдельных полей из таблицы

Из созданной таблицы можно удалить столбцы или ограничения. При удале­нии ограничений следует помнить, что выполнению команд могут помешать некоторые зависимости. Например, если столбец является первичным клю­чом, вы должны удалить это ограничение до удаления столбца. Если в другой таблице существует ссылка на столбец, SQL Server также не позволит уда­лить его до снятия ограничения. Примеры команды alter table drop:

ALTER TABLE Authors DROP CONSTRAINT inn

ALTER TABLE Authors DROP COLUMN [ИНН]

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

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

Вот команда, которая делает это:

ALTER TABLE Authors

ALTER COLUMN FirstName varchar(100)

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

Временные таблицы

Временные таблицы похожи на обычные, однако они не предназначены для

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

Они создаются, удаляются и используются как обычные таблицы.

Перечислим главные отличия временных таблиц от обычных:

• имя временной таблицы должно начинаться с символов # или ##;

• длина имени временной таблицы ограничивается 116 символами;

• временные таблицы удаляются при отключении пользователя от базы данных;

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

В SQL Server существуют два типа временных таблиц: локальные и глобальные.

Локальные временные таблицы

Локальные временные таблицы доступны лишь для своего владельца.

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

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

# TEMPL:

CREATE TABLE # TEMPL ( a int NULL, b varchar(25) NULL)

Глобальные временные таблицы

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

Следующий фрагмент создает глобальную временную таблицу:

CREATE TABLE ##globalTempTablel (a int NULL, b varchar(25) NULL)

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

Замечание.

При выполнении SQL-команды вы можете определить, для какой именно ба­зы данных они предназначены, используя команду use.

Синтаксис этой команды:

USE [имя базы]

Например:

USE [Обычные покупатели] „

SELECT * FROM [Покупатели]

USE [VIP покупатели]

SELECT * FROM [Покупатели]

Здесь сначала возвращается список покупателей из базы обычных покупате­лей, а потом из базы VIP-покупателей.