Управление таблицами с помощью T-SQL и Enterprise Manager

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

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

В данной лекции мы рассмотрим использование Transact-SQL (T-SQL) и Microsoft SQL Server 2000 Enterprise Manager для управления вашими таблицами. Следует помнить, что T-SQL и Enterprise Manager имеют различные уровни гибкости для модифицирования таблицы. Enterprise Manager более полезен в том смысле, что позволяет вам выполнять определенные модификации проще, чем при использовании T-SQL. Enterprise Manager выводит на экран информативные сообщения об ошибках и иногда предлагает альтернативы, если вы пытаетесь выполнить неверную модификацию. Однако T-SQL имеет одно преимущество, состоящее в том, что если вы запускаете команды с записью сценария, то получаете записанную трассировку того, как и в каком порядке выполнялись ваши модификации. В этой лекции мы рассмотрим преимущества и недостатки использования этих двух методов для модифицирования таблиц.

Прежде чем начать изучение, нам нужно создать две таблицы в базе данных MyDB – Bicycle_Sales (Продажи велосипедов) и Bicycle_Inventory (Запасы велосипедов), – которые будут использоваться для примеров этой лекции. Таблица Bicycle_Sales содержит информацию по продажам велосипедов и состоит из следующих колонок: make_id (идентификатор изделия), model_id (идентификатор модели), description (описание), year (год), sale_id (идентификатор продажи), price (цена), quantity (количество) и sale_date (дата продажи). Колонки make_id и model_id указываются вместе как ограничение foreign key (внешний ключ). Это ограничение содержит ссылку на колонки make_id и model_id в таблице Bicycle_Inventory, образуя уникальный кластеризованный индекс. Как вы увидите в "Создание и использование умолчаний, ограничений и правил" , ограничение foreign key может содержать ссылку только на колонку первичного ключа (primary key) или другую колонку с уникальным ограничением в ссылочной таблице. (Подробное описание ограничений дается в "Создание и использование умолчаний, ограничений и правил" , описание индексов – в "Создание таблиц баз данных" .)

Колонка sale_id объявлена как кластеризованный индекс по первичному ключу для таблицы Bicycle_Sales. Ниже приводится оператор CREATE TABLE для создания каждой из этих таблиц:

USE MyDB 
GO 
CREATE TABLE Bicycle_Inventory 
( 
       make_name       		char(10)     		NOT NULL, 
       make_id         		tinyint      		NOT NULL,     
       model_name      		char(12)     		NOT NULL, 
       model_id        		tinyint      		NOT NULL, 
       in_stock        		tinyint      		NOT NULL, 
       on_order        		tinyint      		NULL, 
       CONSTRAINT      	MI_clu_indx 
       UNIQUE CLUSTERED(make_id, model_id) 
) 
GO 

CREATE TABLE Bicycle_Sales 
( 
       make_id      		tinyint     		NOT NULL,--Использована в ограничении
                                            				           --foreign key
       model_id     		tinyint     		NOT NULL,--Также использована в ограничении
                                           			      --foreign key
       description  		char(30)    		NULL, 
       year         		char(4)     		NOT NULL, 
       sale_id      		int         		NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,
       price        		smallmoney       	NOT NULL,
       quantity     		tinyint     		NOT NULL,
       sale_date    		datetime    		NOT NULL,
	CONSTRAINT   	sales_inventory_fk FOREIGN KEY (make_id, model_id)
	REFERENCES   	Bicycle_Inventory(make_id, model_id)
)
GO

Внимание. Таблица Bicycle_Inventory должна быть создана до таблицы Bicycle_Sales. Если попытаться сначала создать таблицу Bicycle_Sales, то будет выдано сообщение об ошибке. Таблица Bicycle_Sales ссылается на таблицу Bicycle_Inventory с помощью ограничения, и если отсутствует таблица Bicycle_Inventory, то ограничение не может быть создано, что приведет к появлению ошибки.

Теперь, создав наши примеры таблиц базы данных, внесем некоторые изменения, используя сначала T-SQL и затем – Enterprise Manager.

Модифицирование таблицы с помощью T-SQL

В этом разделе вы узнаете, как использовать операторы T-SQL для изменения, добавления, удаления и переименования колонок в существующей таблице. Для осуществления всех модификаций таблицы используется оператор T-SQL ALTER TABLE.

Изменение колонок

Создав таблицу, вы можете изменить для колонки тип данных, точность (для числовых типов) и null-атрибут, а также добавить к колонке свойство ROWGUIDCOL или удалить его из колонки, и все это с помощью оператора ALTER TABLE. Используя другие операторы T-SQL, вы можете выполнять и другие модификации колонок, такие как добавление значения по умолчанию. (Подробно об этих операторах см. "Создание и использование умолчаний, ограничений и правил" .)

Не все колонки можно изменять. В общем случае вы не можете изменять следующие типы колонок.

• Колонка, которая является частью ограничения primary key или foreign key.
• Колонка, используемая в репликации. (О репликации см. "Репликация в Microsoft SQL Server: обзор типов репликации и репликация моментальных снимков" .)
• Колонка, имеющая тип данных text, ntext, image или timestamp.
• Расчетная колонка.
• Колонка ROWGUIDCOL (вы можете, однако, добавлять к колонке свойство ROWGUIDCOL или удалять его).
• Колонка, используемая в индексе.
• Колонка, используемая в ограничении check или unique. ( Об ограничениях см. "Создание и использование умолчаний, ограничений и правил" .)
• Колонка, используемая в формировании статистики путем явного выполнения оператора CREATE STATISTICS (формируемые с помощью SQL Server данные статистики удаляются с помощью оператора ALTER TABLE ).
• Колонка, связанная со значением по умолчанию.

Все другие типы колонок можно изменять с помощью оператора ALTER TABLE. В некоторых из предыдущих случаев вы можете снять запрет на изменение колонки. Например, вы можете удалить ограничение foreign key или какое-либо другое ограничение или удалить индекс по колонке, и если к данной колонке не относятся какие-либо другие запреты, то можете затем изменять эту колонку.