SQL Server 2000

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

Разбить на страницы
Показывать лекцию целиком

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

Примечание. В данном случае 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, образуя уникальный кластеризованный индекс. Как вы увидите в лекции 16, ограничение foreign key может содержать ссылку только на колонку первичного ключа (primary key) или другую колонку с уникальным ограничением в ссылочной таблице. (Подробное описание ограничений дается в лекции 16, описание индексов – в лекции 17.)

Колонка 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, вы можете выполнять и другие модификации колонок, такие как добавление значения по умолчанию. (Подробно об этих операторах см. лекцию 16.)

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

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

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

    Изменение типа данных

    Чтобы можно было изменить тип данных какой-либо колонки, для исходного типа данных должно существовать неявное преобразование в новый тип данных. Таблицу допустимых преобразований можно найти под заголовком "CAST and CONVERT (T-SQL)" (Приведение и преобразование [T-SQL]) в Books Online. Для доступа к этой теме найдите "CAST" в Books Online и затем выберите в диалоговом окне Topics Found "CAST and CONVERT (T-SQL)".

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

    ALTER TABLE		<имя_таблицы>
ALTER COLUMN	<имя_колонки> 	<новый_тип_данных>

    Если вы измените для таблицы Bicycle_Sales тип данных datetime колонки sale_date на тип smalldatetime, то строки данных будут занимать меньше места.

    Дело в том, что datetime занимает 8 байтов, а smalldatetime – только 4. Чтобы выполнить это изменение, используйте следующий оператор:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	sale_date smalldatetime 	NOT NULL 
GO

    Любые существующие данные таблицы будут неявно преобразованы в новый тип данных – smalldatetime. Null -атрибут NOT NULL не изменился.

    Чтобы изменить тип данных char(30) колонки description на тип varchar(20) (имеющий меньшую длину), используйте следующий оператор:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN		description varchar(20) 	NULL
GO

    Отметим, что исходный тип данных char(30) допускает неявное преобразование в новый тип данных varchar(20), но varchar(20) короче. Поэтому для всех существующих строк будет без оповещения выполнено усечение значений колонки description длиной более 20 символов и преобразование в тип varchar(20).

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

    Изменение null-атрибута

    Чтобы изменить null -атрибут, вы можете изменить для колонки атрибут NOT NULL на NULL, за исключением колонок с ограничением primary key. (По определению в таких колонках не допускаются null -значения.) Вы можете изменить атрибут NULL на NOT NULL, только если в строках соответствующей колонки нет null -значений. Если колонка содержит null -значения, то вы можете выполнить оператор UPDATE для замены всех null -значений на некоторое фактическое значение и затем изменить NULL на NOT NULL для этой колонки. Если null -атрибут не указан для измененной колонки, то в этой колонке допускаются null -значения по умолчанию. Рассмотрим некоторые примеры.

    Для изменения null -атрибута колонки quantity с тем, чтобы допускались null -значения, выполните следующий оператор:

    ALTER TABLE		Bicycle_Sales 
ALTER COLUMN	quantity tinyint 	NULL 
GO

    Тип данных колонки, tinyint, остался без изменений; изменился только null -атрибут. Теперь в колонке quantity будут допускаться null -значения. Если не введено никакого значения, будет происходить автоматическая вставка значения NULL. Это изменение не влияет на значения колонки quantity в существующих строках, но оно позволяет вставлять NULL -значения для новых строк, добавляемых к таблице.

    Теперь изменим описание колонки description на NOT NULL. Мы предположим, что в этой колонке уже имеются null -значения. Поэтому сначала мы должны заменить эти null -значения на какое-то фактическое значение – в данном случае – на значение None (Нет), совместимое с типом данной колонки. Чтобы выполнить проверку на значение NULL, надежнее всего не использовать оператор равенства (=), а применять ключевые слова IS NULL или IS NOT NULL. Дело в том, что NULL означает неизвестное значение, а оператор равенства, возможно, не может выполнять сравнение для null -значений (это зависит от установки параметра базы данных ANSI nulls – ON [активизирован] или OFF [отключен]). Если для этого параметра задано значение OFF, то оператор равенства выражение = NULL возвратит значение TRUE, если выражение содержит null -значение. Этот оператор возвратит значение FALSE, если выражение не содержит null -значения. Если для параметра ANSI nulls задано значение ON, то оператор выражение = NULL возвратит для всех сравнений значение UNKNOWN и не будет выдано никаких результатов. SQL Server не возвращает значений NULL, как можно было бы ожидать, если параметр ANSI nulls установлен в значение ON. IS NULL и IS NOT NULL действуют одинаковым образом независимо от установки параметра ANSI_NULLS. Чтобы заменить null -значения колонки description на значение None, используйте следующий оператор UPDATE SET:

    UPDATE				Bicycle_Sales SET description = "None"
WHERE				description IS NULL 
GO

    Теперь изменим null -атрибут колонки description на NOT NULL:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	description char(30) 	NOT NULL 
GO

    И в данном случае мы изменили только null -атрибут этой колонки, а исходный тип данных char(30) остался без изменений. Вы можете одновременно изменить тип данных и null -атрибут в одном операторе ALTER TABLE, как это показано ниже:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	description varchar(20) 	NOT NULL 
GO

    Этот оператор изменяет тип данных и null -атрибут колонки description.

    Добавление или удаление свойства ROWGUIDCOL Property

    Чтобы добавить к колонке или удалить из колонки свойство ROWGUIDCOL, используйте следующий синтаксис:

    ALTER TABLE		<имя_таблицы>
ALTER COLUMN	<имя_колонки> ADD | DROP ROWGUIDCOL

    Свойство ROWGUIDCOL можно добавлять только в колонку типа uniqueidentifier. Если предположить, что в нашу таблицу Bicycle_Sales была включена колонка типа uniqueidentifier с именем unique_id, то вы могли бы добавить свойство ROWGUIDCOL с помощью следующего оператора:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	unique_id ADD ROWGUIDCOL
GO

    И вы могли бы удалить это свойство с помощью следующего оператора:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	unique_id DROP ROWGUIDCOL 
GO

    Добавление колонок

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

    Добавляя колонку с атрибутом NOT NULL, вы должны также объявить значение по умолчанию, чтобы это значение появилось в новой колонке уже существующих строк. Это значение по умолчанию задается с помощью ключевого слова DEFAULT. Чтобы добавить колонку, используйте следующий синтаксис:

    ALTER TABLE		<имя_таблицы>
ADD				<имя_колонки> <тип_данных> <null-атрибут>
DEFAULT			значение_по_умолчанию

    Например, чтобы добавить к таблице Bicycle_Sales колонку с именем salesperson_id (идентификатор продавца), используйте следующий оператор. (В новой колонке не допускаются null -значения, и она имеет значение по умолчанию, равное 0.)

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NOT NULL 
DEFAULT			0 
GO

    Поскольку колонка объявлена как NOT NULL, то во всех существующих строках таблицы в новую колонку будет занесено значение 0.

    Если вместо этого колонка salesperson_id будет объявлена как NULL (см. ниже), то значение по умолчанию не будет обязательным:

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NULL 
DEFAULT			0    	--Необязательное значение по умолчанию
GO

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

    Чтобы в существующих строках вместо значения NULL также было присвоено значение по умолчанию (0), используйте в ключевом слове DEFAULT опцию WITH VALUES, как это показано ниже:

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NULL
DEFAULT			0 WITH VALUES 
GO

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

    Удаление колонок

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

  • Колонка, используемая в ограничении primary key, foreign key, unique или check.
  • Колонка, используемая для репликации.
  • Колонка, используемая в индексе (если не удалить сначала этот индекс).
  • Колонка, ограниченная каким-либо правилом.
  • Колонка, связанная со значением по умолчанию.
    •  Примечание. Эти запрещения тоже действуют, но обрабатываются по-другому, если вы используете для удаления колонки Enterprise Manager. (Более подробную информацию см. в разделе "Модифицирование таблицы с помощью Enterprise Manager" ниже.)

    Для удаления колонки из таблицы используйте следующий синтаксис:

    ALTER TABLE 	<имя_таблицы>
DROP COLUMN 	<имя_колонки>

    Следующий оператор удаляет колонку description из таблицы Bicycle_Sales:

    ALTER TABLE		Bicycle_Sales
DROP COLUMN		description 
GO

    Колонка description и все ее значения удаляются изо всех строк таблицы.

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

    Переименование колонок

    Чтобы переименовать колонку с помощью операторов T-SQL, вы должны запустить системную хранимую процедуру sp_rename, используя следующий синтаксис:

    sp_rename 'таблица.исходное_имя_колонки', 'новое_имя_колонки', 'COLUMN'

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

    sp_rename 'Bicycle_Sales.description', 'bicycle_desc', 'COLUMN'
GO

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

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

    Как уже говорилось, модифицирование таблицы с помощью Enterprise Manager выполняется проще и дает вам больше функциональных возможностей и гибкости, чем операторы T-SQL. Вы можете выполнять все свои модификации в окне Design Table (Проектирование таблицы) или с помощью схемы (диаграммы) базы данных. Рассмотрим сначала использование окна Design Table. Чтобы открыть окно Design Table для вашей таблицы Bicycle_Sales, выполните следующие шаги:

  • Раскройте папку базы данных MyDB в левой панели Enterprise Manager.
  • Щелкните на папке Tables (Таблицы), чтобы в правой панели появился список всех таблиц базы данных MyDB (рис 15.1(рис 15.1) Enterprise Manager
  • Щелкните правой кнопкой мыши на таблице Bicycle_Sales в правой панели. Выберите из контекстного меню пункт Design Table, чтобы появилось окно Design Table (рис 15.2(рис 15.2) Окно Design Table (Проектирование таблицы)
    •

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

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

    Для некоторых ситуаций, когда T-SQL не позволяет выполнить определенные модификации и возвращает сообщение об ошибке, Enterprise Manager предоставляет дополнительные возможности, позволяющие вам правильно осуществить процесс модификаций. Например, если вы попытаетесь с помощью оператора T-SQL ALTER TABLE изменить длину данных колонки, имеющей ограничение primary key или foreign key, то получите сообщение об ошибке, аналогичное следующему сообщению:

    Column or parameter #0: Cannot specify a column width on data type int.
(Колонка или параметр #0: Невозможно задать ширину колонки по типу данных int.)

    Но если вы используете Enterprise Manager, то увидите окно сообщения, в котором можно изменить длину данных для этой колонки.

    Например, чтобы изменить тип данных tinyint на тип smallint для колонки make_id (имеющей ограничение foreign key, содержащее ссылку на колонку make_id таблицы Bicycle_Inventory), просто щелкните на tinyint, щелкните на направленной вниз стрелке, чтобы появился раскрывающийся список Data Type (Тип данных) и затем выберите smallint (рис. 15.3).

    (рис 15.3) Изменение типа данных колонки с помощью Enterprise Manager

    Поскольку колонка make_id имеет ограничение foreign key, появится диалоговое окно Data Type Change Required (Требуется изменение типа данных) (рис. 15.4). Щелкните на кнопке Yes (Да), чтобы автоматически преобразовать в обеих таблицах тип колонки make_id из tinyint в smallint.

    (рис 15.4) Диалоговое окно Data Type Change Required (Требуется изменение типа данных)

    Как и в случае использования T-SQL, при изменении типа данных с помощью Enterprise Manager исходный тип данных должен допускать неявное преобразование в новый тип данных. Если вы попытаетесь выполнить недопустимое преобразование, Enterprise Manager возвратит сообщение об ошибке, аналогичное тому, что отображено на рис. 15.5, где показан результат недопустимого способа изменения типа данных для колонки sale_date из типа datetime в тип text. Щелкните на кнопке OK, чтобы закрыть окно с сообщением об ошибке, и измените недопустимый тип данных на тип, допускающий неявное преобразование.

    (рис 15.5) Сообщение об ошибке, которое выводится после попытки изменения типа данных на тип, не допускающий неявного преобразования

    Чтобы сохранить ваши изменения, щелкните на кнопке Save Disk (Сохранение на диске) панели инструментов окна Design Table. Затем в диалоговом окне Save (Сохранение) (рис. 15.6), нужно подтвердить, что указанные таблицы должны быть записаны на диск. Чтобы подтвердить, что вы хотите сохранить ваши изменения, щелкните на кнопке Yes.

    (рис 15.6) Диалоговое окно Save (Сохранить)

    Добавление колонок

    Чтобы добавить колонку, щелкните на столбце Column Name (Имя колонки) первой пустой строки окна Design Table, введите имя новой колонки, выберите тип ее данных и присвойте ей нужные атрибуты ( Allow Nulls [Разрешить null -значения], Default Value [Значение по умолчанию], Identity и т.д.). Как показано на рис. 15.7, мы добавили колонку с именем salesperson_id с типом данных tinyint, с разрешением использования null -значений и значением по умолчанию 0. Щелкните на кнопке Save Disk, чтобы сохранить ваши изменения. SQL Server добавит к таблице эту новую колонку

    (рис 15.7) Добавление новой колонки с именем salesperson_id

    Удаление колонок

    Удаление колонки – это простой процесс, если вы работаете с Enterprise Manager. В окне Design Table нужно просто щелкнуть правой кнопкой мыши на имени колонки или любом из ее атрибутов (на любой ячейке строки с именем данной колонки) и выбрать из контекстного меню пункт Delete Column (Удалить колонку). Соответствующая строка будет удалена из таблицы. Не забудьте щелкнуть на кнопке Save Disk, чтобы сохранить ваши изменения.

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

    (рис 15.8) Окно сообщения, которое появляется при попытке удаления колонки, имеющей связи с другими колонками и таблицами

    Создание и использование схемы базы данных

    Вы можете также модифицировать таблицы в Enterprise Manager с помощью схемы базы данных. Чтобы создать схему для базы данных MyDB с двумя таблицами Bicycle_Sales и Bicycle_Inventory, выполните следующие шаги:

  • Раскройте MyDB в левой панели Enterprise Manager и затем щелкните правой кнопкой мыши на Diagrams (Схемы). Выберите из контекстного меню пункт Choose New Database Diagram (Выбор новой схемы базы данных), чтобы появилось начальное окно мастера создания схемы базы данных Create Database Diagram Wizard (рис. 15.9).
  • Щелкните на кнопке Next (Далее), чтобы появилось окно Select Tables to be Added (Выбор таблиц для добавления) (рис. 15.10). Выделите таблицы, которые хотите включить в вашу схему, в списке Available Tables (Имеющиеся таблицы) и затем щелкните на кнопке Add. В этом примере мы добавили таблицы Bicycle_Inventory и Bicycle_Sales.
  • Щелкните на кнопке Next, чтобы появилось окно Completing the Create Database Diagram Wizard (Завершение работы мастера создания схемы базы данных). Щелкните на кнопке Finish (Готово), если выбраны нужные таблицы, или щелкните на кнопке Back (Назад) и внесите необходимые изменения.
  • После щелчка на кнопке Finish вы увидите схему базы данных (рис. 15.11).
  • Сохраните вашу схему, указав описательное имя (щелкните на кнопке Save Disk и введите имя, когда появится соответствующий запрос).
    •  (рис 15.10) Начальное окно мастера Create Database Diagram Wizard (Создание схемы базы данных)(рис 15.9) Окно Select Tables to be Added (Выбор таблиц для добавления)

    Вертикальная линия, которая заканчивается изображением ключа и соединяет две таблицы в вашей схеме, представляет связь между ними, выраженную ограничением foreign key. Чтобы вывести на экран метку этой связи, щелкните правой кнопкой мыши на фоновой части окна и выберите из контекстного меню пункт Show Relationship Labels (Показать метки связей). Появится имя ограничения foreign key (рис. 15.12).

    Чтобы выделить какую-либо таблицу, щелкните на ней; чтобы выделить более одной таблицы, щелкните на каждой таблице, удерживая клавишу Ctrl. Если щелкнуть правой кнопкой мыши на одной из таблиц и затем выбрать из контекстного меню какой-либо пункт, то соответствующая операция будет применена ко всем выделенным таблицам. Например, если выделить в вашей схеме баз данных обе таблицы, щелкнуть правой кнопкой мыши на одной из таблиц и затем выбрать из контекстного меню пункт Table View, затем Standard, то вид обеих таблиц будет изменен, чтобы представить на экране свойства всех колонок (рис. 15.13).

    (рис 15.12) Пример схемы базы данных(рис 15.11) Просмотр меток связей между таблицами(рис 15.13) Просмотр свойств по колонкам в схеме базы данных

    Представление свойств колонок на экране для каждой таблицы аналогично окну Design Table, и каждую таблицу можно модифицировать с помощью тех же методов, что и в окне Design Table. Просто добавьте или измените данные колонок в нужных ячейках и сохраните ваши изменения, щелкнув на кнопке Save Disk. Вы можете также перемещать таблицы и изменять их размеры в схеме базы данных, выводить на экран различные представления таблиц и выполнять другие действия. Опробуйте различные команды контекстного меню. Отметим, что как только вы вносите какое-либо изменение в таблицу, рядом с именем этой таблицы появляется символ "звездочка", указывающий, что внесены изменения.

    Следствия изменения таблицы

    Если вы изменили таблицу, то любые необходимые изменения вносятся в существующие строки данных сразу после исполнения оператора T-SQL ALTER TABLE или после сохранения изменений, если вы используете Enterprise Manager. SQL Server налагает блокировку на данную таблицу, чтобы никакие другие пользователи не имели к ней доступа, пока выполняются изменения. Модифицирование, которое требует изменения всех строк в достаточно большой таблице, такое как добавление колонки со свойством NOT NULL и значением по умолчанию или удаление колонки, может занять некоторое время и должно выполняться с осторожностью в периоды минимального пользовательского доступа. Если вы модифицируете колонку путем изменения длины ее данных, точности или масштаба, то соответствующая таблица повторно создается в базе данных и существующие данные преобразуются в новый тип данных.

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

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

    Если вы удаляете таблицу, то удаляется все, что связано с этой таблицей: определения и данные таблицы, индексы, ограничения, триггеры и полномочия доступа. Представления и хранимые процедуры, содержащие ссылки на удаляемую таблицу, должны быть удалены явным образом. Таблицу нельзя удалить, если на нее есть ссылка с помощью ограничения foreign key в другой таблице; иными словами, таблицу нельзя удалить, если от нее зависит другая таблица. Прежде чем удалить данную таблицу, следует сначала удалить это ограничение или ссылающуюся таблицу. С другой стороны, таблицу, содержащую ограничение foreign key, можно удалить, если от нее не зависит никакая другая таблица. В этом разделе мы рассмотрим удаление таблицы с помощью T-SQL и Enterprise Manager.

    Использование T-SQL для удаления таблицы

    Оператор T-SQL, используемый для удаления таблицы, имеет следующий синтаксис:

    DROP TABLE <имя_таблицы>

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

    Для удаления таблицы Bicycle_Inventory, на которую имеется ссылка из таблицы Bicycle_Sales с помощью ограничения foreign key, мы должны сначала удалить это ограничение foreign key (с именем sales_inventory_fk ) и затем удалить таблицу, как это показано ниже:

    ALTER TABLE      	Bicycle_Sales
DROP CONSTRAINT  	sales_inventory_fk
GO 
DROP TABLE       	Bicycle_Inventory 
GO

    Если вы попытаетесь удалить таблицу до удаления ограничения foreign key, то получите сообщение об ошибке и таблица не будет удалена.

    Использование Enterprise Manager для удаления таблицы

    В Enterprise Manager имеется два метода удаления таблицы: использование диалогового окна Drop Objects (Удаление объектов) или использование схемы базы данных. Диалоговое окно Drop Objects наиболее подходит, если вы удаляете таблицу, от которой не зависят никакие другие таблицы. Чтобы использовать этот метод, выполните следующие шаги:

  • В левой панели Enterprise Manager раскройте базу данных, содержащую таблицу, которую вы хотите удалить, и щелкните на папке Tables; затем в правой панели щелкните правой кнопкой мыши на имени таблицы, которую хотите удалить.
  • Выберите из контекстного меню пункт Delete (Удалить), чтобы появилось диалоговое окно Drop Objects (рис 15.14(рис 15.14) Диалоговое окно Drop Objects (Удаление объектов)
  • Если данная таблица имеет какие-либо зависимые таблицы, щелкните на кнопке Show Dependencies (Показать зависимости), чтобы появилось диалоговое окно Dependencies (рис. 15.15). Любые таблицы, зависящие от данной таблицы, появятся в левом списке этого диалогового окна. Если имеются какие-либо зависимые таблицы, то вы не можете удалить данную таблицу, пока не будут удалены соответствующие зависимости.
  • Если никакие другие таблицы не зависят от выбранной таблицы, то вы можете удалить эту таблицу, щелкнув на кнопке Drop All (Удалить все) в диалоговом окне Drop Objects.
    •  (рис 15.15) Диалоговое окно Dependencies для таблицы Bicycle_Sales

    Чтобы удалить таблицу, которая имеет зависимые таблицы, мы будем использовать второй метод: схему базы данных. В этом примере мы удалим таблицу Bicycle_Inventory, на которую имеется ссылка с помощью ограничения foreign key в таблице Bicycle_Sales. Это удаление не удалось бы выполнить, если бы мы использовали диалоговое окно Drop Objects. Но, используя схему базы данных, где показаны обе таблицы, мы можем удалить любую таблицу, а ограничение foreign key будет удалено автоматически. Для удаления таблицы Bicycle_Inventory выполните следующие шаги:

  • Откройте схему базы данных в Enterprise Manager, раскрыв базу данных, которую хотите использовать, щелкнув на Diagrams и затем дважды щелкнув на имени подходящей схемы в правой панели. Выделите имя таблицы, которую хотите удалить (в данном случае – Bicycle_Inventory).
  • Щелкните правой кнопкой мыши в любом месте этой таблицы и выберите из контекстного меню пункт Delete Table From Database (Удалить таблицу из базы данных). При появлении окна, где нужно подтвердить, что вы хотите удалить данную таблицу из базы данных, щелкните на кнопке Yes. Таблица и ограничение foreign key будут удалены из схемы.
  • Если вы уверены, что хотите окончательно удалить эту таблицу, сохраните свои изменения, щелкнув на кнопке Save Disk. После этого таблица будет удалена из базы данных. Если вы отказались от своего решения удалить таблицу, просто выйдите из окна Edit Diagram (Редактирование схемы) без сохранения изменений, щелкнув на кнопке Close (Закрыть). Если вы затем повторно откроете схему базы данных, исходные таблицы будут на прежнем месте. Никакие изменения не начнут действовать, пока вы не сохраните свою работу.
    •

    Заключение

    В этой лекции вы узнали, как использовать T-SQL и Enterprise Manager для модифицирования таблиц базы данных путем изменения, удаления, добавления и переименования колонок. В процессе изложения материала были показаны некоторые отличия между функциональными возможностями T-SQL и Enterprise Manager. Вы также узнали, как создается схема базы данных с помощью мастера Create Database Diagram Wizard и как происходит удаление таблицы и всех ее данных из базы данных. В таблицу можно вносить другие типы модификаций, включая изменение, добавление и удаление ограничений и умолчаний. (О модификациях см. лекцию 16.)

    Страницы:

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

    Примечание. В данном случае 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, образуя уникальный кластеризованный индекс. Как вы увидите в лекции 16, ограничение foreign key может содержать ссылку только на колонку первичного ключа (primary key) или другую колонку с уникальным ограничением в ссылочной таблице. (Подробное описание ограничений дается в лекции 16, описание индексов – в лекции 17.)

    Колонка 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, вы можете выполнять и другие модификации колонок, такие как добавление значения по умолчанию. (Подробно об этих операторах см. лекцию 16.)

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

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

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

    Изменение типа данных

    Чтобы можно было изменить тип данных какой-либо колонки, для исходного типа данных должно существовать неявное преобразование в новый тип данных. Таблицу допустимых преобразований можно найти под заголовком "CAST and CONVERT (T-SQL)" (Приведение и преобразование [T-SQL]) в Books Online. Для доступа к этой теме найдите "CAST" в Books Online и затем выберите в диалоговом окне Topics Found "CAST and CONVERT (T-SQL)".

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

    ALTER TABLE		<имя_таблицы>
ALTER COLUMN	<имя_колонки> 	<новый_тип_данных>

    Если вы измените для таблицы Bicycle_Sales тип данных datetime колонки sale_date на тип smalldatetime, то строки данных будут занимать меньше места.

    Дело в том, что datetime занимает 8 байтов, а smalldatetime – только 4. Чтобы выполнить это изменение, используйте следующий оператор:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	sale_date smalldatetime 	NOT NULL 
GO

    Любые существующие данные таблицы будут неявно преобразованы в новый тип данных – smalldatetime. Null -атрибут NOT NULL не изменился.

    Чтобы изменить тип данных char(30) колонки description на тип varchar(20) (имеющий меньшую длину), используйте следующий оператор:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN		description varchar(20) 	NULL
GO

    Отметим, что исходный тип данных char(30) допускает неявное преобразование в новый тип данных varchar(20), но varchar(20) короче. Поэтому для всех существующих строк будет без оповещения выполнено усечение значений колонки description длиной более 20 символов и преобразование в тип varchar(20).

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

    Изменение null-атрибута

    Чтобы изменить null -атрибут, вы можете изменить для колонки атрибут NOT NULL на NULL, за исключением колонок с ограничением primary key. (По определению в таких колонках не допускаются null -значения.) Вы можете изменить атрибут NULL на NOT NULL, только если в строках соответствующей колонки нет null -значений. Если колонка содержит null -значения, то вы можете выполнить оператор UPDATE для замены всех null -значений на некоторое фактическое значение и затем изменить NULL на NOT NULL для этой колонки. Если null -атрибут не указан для измененной колонки, то в этой колонке допускаются null -значения по умолчанию. Рассмотрим некоторые примеры.

    Для изменения null -атрибута колонки quantity с тем, чтобы допускались null -значения, выполните следующий оператор:

    ALTER TABLE		Bicycle_Sales 
ALTER COLUMN	quantity tinyint 	NULL 
GO

    Тип данных колонки, tinyint, остался без изменений; изменился только null -атрибут. Теперь в колонке quantity будут допускаться null -значения. Если не введено никакого значения, будет происходить автоматическая вставка значения NULL. Это изменение не влияет на значения колонки quantity в существующих строках, но оно позволяет вставлять NULL -значения для новых строк, добавляемых к таблице.

    Теперь изменим описание колонки description на NOT NULL. Мы предположим, что в этой колонке уже имеются null -значения. Поэтому сначала мы должны заменить эти null -значения на какое-то фактическое значение – в данном случае – на значение None (Нет), совместимое с типом данной колонки. Чтобы выполнить проверку на значение NULL, надежнее всего не использовать оператор равенства (=), а применять ключевые слова IS NULL или IS NOT NULL. Дело в том, что NULL означает неизвестное значение, а оператор равенства, возможно, не может выполнять сравнение для null -значений (это зависит от установки параметра базы данных ANSI nulls – ON [активизирован] или OFF [отключен]). Если для этого параметра задано значение OFF, то оператор равенства выражение = NULL возвратит значение TRUE, если выражение содержит null -значение. Этот оператор возвратит значение FALSE, если выражение не содержит null -значения. Если для параметра ANSI nulls задано значение ON, то оператор выражение = NULL возвратит для всех сравнений значение UNKNOWN и не будет выдано никаких результатов. SQL Server не возвращает значений NULL, как можно было бы ожидать, если параметр ANSI nulls установлен в значение ON. IS NULL и IS NOT NULL действуют одинаковым образом независимо от установки параметра ANSI_NULLS. Чтобы заменить null -значения колонки description на значение None, используйте следующий оператор UPDATE SET:

    UPDATE				Bicycle_Sales SET description = "None"
WHERE				description IS NULL 
GO

    Теперь изменим null -атрибут колонки description на NOT NULL:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	description char(30) 	NOT NULL 
GO

    И в данном случае мы изменили только null -атрибут этой колонки, а исходный тип данных char(30) остался без изменений. Вы можете одновременно изменить тип данных и null -атрибут в одном операторе ALTER TABLE, как это показано ниже:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	description varchar(20) 	NOT NULL 
GO

    Этот оператор изменяет тип данных и null -атрибут колонки description.

    Добавление или удаление свойства ROWGUIDCOL Property

    Чтобы добавить к колонке или удалить из колонки свойство ROWGUIDCOL, используйте следующий синтаксис:

    ALTER TABLE		<имя_таблицы>
ALTER COLUMN	<имя_колонки> ADD | DROP ROWGUIDCOL

    Свойство ROWGUIDCOL можно добавлять только в колонку типа uniqueidentifier. Если предположить, что в нашу таблицу Bicycle_Sales была включена колонка типа uniqueidentifier с именем unique_id, то вы могли бы добавить свойство ROWGUIDCOL с помощью следующего оператора:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	unique_id ADD ROWGUIDCOL
GO

    И вы могли бы удалить это свойство с помощью следующего оператора:

    ALTER TABLE		Bicycle_Sales
ALTER COLUMN	unique_id DROP ROWGUIDCOL 
GO

    Добавление колонок

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

    Добавляя колонку с атрибутом NOT NULL, вы должны также объявить значение по умолчанию, чтобы это значение появилось в новой колонке уже существующих строк. Это значение по умолчанию задается с помощью ключевого слова DEFAULT. Чтобы добавить колонку, используйте следующий синтаксис:

    ALTER TABLE		<имя_таблицы>
ADD				<имя_колонки> <тип_данных> <null-атрибут>
DEFAULT			значение_по_умолчанию

    Например, чтобы добавить к таблице Bicycle_Sales колонку с именем salesperson_id (идентификатор продавца), используйте следующий оператор. (В новой колонке не допускаются null -значения, и она имеет значение по умолчанию, равное 0.)

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NOT NULL 
DEFAULT			0 
GO

    Поскольку колонка объявлена как NOT NULL, то во всех существующих строках таблицы в новую колонку будет занесено значение 0.

    Если вместо этого колонка salesperson_id будет объявлена как NULL (см. ниже), то значение по умолчанию не будет обязательным:

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NULL 
DEFAULT			0    	--Необязательное значение по умолчанию
GO

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

    Чтобы в существующих строках вместо значения NULL также было присвоено значение по умолчанию (0), используйте в ключевом слове DEFAULT опцию WITH VALUES, как это показано ниже:

    ALTER TABLE		Bicycle_Sales
ADD				salesperson_id tinyint NULL
DEFAULT			0 WITH VALUES 
GO

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

    Удаление колонок

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

  • Колонка, используемая в ограничении primary key, foreign key, unique или check.
  • Колонка, используемая для репликации.
  • Колонка, используемая в индексе (если не удалить сначала этот индекс).
  • Колонка, ограниченная каким-либо правилом.
  • Колонка, связанная со значением по умолчанию.
    •  Примечание. Эти запрещения тоже действуют, но обрабатываются по-другому, если вы используете для удаления колонки Enterprise Manager. (Более подробную информацию см. в разделе "Модифицирование таблицы с помощью Enterprise Manager" ниже.)

    Для удаления колонки из таблицы используйте следующий синтаксис:

    ALTER TABLE 	<имя_таблицы>
DROP COLUMN 	<имя_колонки>

    Следующий оператор удаляет колонку description из таблицы Bicycle_Sales:

    ALTER TABLE		Bicycle_Sales
DROP COLUMN		description 
GO

    Колонка description и все ее значения удаляются изо всех строк таблицы.

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

    Переименование колонок

    Чтобы переименовать колонку с помощью операторов T-SQL, вы должны запустить системную хранимую процедуру sp_rename, используя следующий синтаксис:

    sp_rename 'таблица.исходное_имя_колонки', 'новое_имя_колонки', 'COLUMN'

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

    sp_rename 'Bicycle_Sales.description', 'bicycle_desc', 'COLUMN'
GO

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

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

    Как уже говорилось, модифицирование таблицы с помощью Enterprise Manager выполняется проще и дает вам больше функциональных возможностей и гибкости, чем операторы T-SQL. Вы можете выполнять все свои модификации в окне Design Table (Проектирование таблицы) или с помощью схемы (диаграммы) базы данных. Рассмотрим сначала использование окна Design Table. Чтобы открыть окно Design Table для вашей таблицы Bicycle_Sales, выполните следующие шаги:

  • Раскройте папку базы данных MyDB в левой панели Enterprise Manager.
  • Щелкните на папке Tables (Таблицы), чтобы в правой панели появился список всех таблиц базы данных MyDB (рис 15.1(рис 15.1) Enterprise Manager
  • Щелкните правой кнопкой мыши на таблице Bicycle_Sales в правой панели. Выберите из контекстного меню пункт Design Table, чтобы появилось окно Design Table (рис 15.2(рис 15.2) Окно Design Table (Проектирование таблицы)
    •

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

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

    Для некоторых ситуаций, когда T-SQL не позволяет выполнить определенные модификации и возвращает сообщение об ошибке, Enterprise Manager предоставляет дополнительные возможности, позволяющие вам правильно осуществить процесс модификаций. Например, если вы попытаетесь с помощью оператора T-SQL ALTER TABLE изменить длину данных колонки, имеющей ограничение primary key или foreign key, то получите сообщение об ошибке, аналогичное следующему сообщению:

    Column or parameter #0: Cannot specify a column width on data type int.
(Колонка или параметр #0: Невозможно задать ширину колонки по типу данных int.)

    Но если вы используете Enterprise Manager, то увидите окно сообщения, в котором можно изменить длину данных для этой колонки.

    Например, чтобы изменить тип данных tinyint на тип smallint для колонки make_id (имеющей ограничение foreign key, содержащее ссылку на колонку make_id таблицы Bicycle_Inventory), просто щелкните на tinyint, щелкните на направленной вниз стрелке, чтобы появился раскрывающийся список Data Type (Тип данных) и затем выберите smallint (рис. 15.3).

    (рис 15.3) Изменение типа данных колонки с помощью Enterprise Manager

    Поскольку колонка make_id имеет ограничение foreign key, появится диалоговое окно Data Type Change Required (Требуется изменение типа данных) (рис. 15.4). Щелкните на кнопке Yes (Да), чтобы автоматически преобразовать в обеих таблицах тип колонки make_id из tinyint в smallint.

    (рис 15.4) Диалоговое окно Data Type Change Required (Требуется изменение типа данных)

    Как и в случае использования T-SQL, при изменении типа данных с помощью Enterprise Manager исходный тип данных должен допускать неявное преобразование в новый тип данных. Если вы попытаетесь выполнить недопустимое преобразование, Enterprise Manager возвратит сообщение об ошибке, аналогичное тому, что отображено на рис. 15.5, где показан результат недопустимого способа изменения типа данных для колонки sale_date из типа datetime в тип text. Щелкните на кнопке OK, чтобы закрыть окно с сообщением об ошибке, и измените недопустимый тип данных на тип, допускающий неявное преобразование.

    (рис 15.5) Сообщение об ошибке, которое выводится после попытки изменения типа данных на тип, не допускающий неявного преобразования

    Чтобы сохранить ваши изменения, щелкните на кнопке Save Disk (Сохранение на диске) панели инструментов окна Design Table. Затем в диалоговом окне Save (Сохранение) (рис. 15.6), нужно подтвердить, что указанные таблицы должны быть записаны на диск. Чтобы подтвердить, что вы хотите сохранить ваши изменения, щелкните на кнопке Yes.

    (рис 15.6) Диалоговое окно Save (Сохранить)

    Добавление колонок

    Чтобы добавить колонку, щелкните на столбце Column Name (Имя колонки) первой пустой строки окна Design Table, введите имя новой колонки, выберите тип ее данных и присвойте ей нужные атрибуты ( Allow Nulls [Разрешить null -значения], Default Value [Значение по умолчанию], Identity и т.д.). Как показано на рис. 15.7, мы добавили колонку с именем salesperson_id с типом данных tinyint, с разрешением использования null -значений и значением по умолчанию 0. Щелкните на кнопке Save Disk, чтобы сохранить ваши изменения. SQL Server добавит к таблице эту новую колонку

    (рис 15.7) Добавление новой колонки с именем salesperson_id

    Удаление колонок

    Удаление колонки – это простой процесс, если вы работаете с Enterprise Manager. В окне Design Table нужно просто щелкнуть правой кнопкой мыши на имени колонки или любом из ее атрибутов (на любой ячейке строки с именем данной колонки) и выбрать из контекстного меню пункт Delete Column (Удалить колонку). Соответствующая строка будет удалена из таблицы. Не забудьте щелкнуть на кнопке Save Disk, чтобы сохранить ваши изменения.

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

    (рис 15.8) Окно сообщения, которое появляется при попытке удаления колонки, имеющей связи с другими колонками и таблицами

    Создание и использование схемы базы данных

    Вы можете также модифицировать таблицы в Enterprise Manager с помощью схемы базы данных. Чтобы создать схему для базы данных MyDB с двумя таблицами Bicycle_Sales и Bicycle_Inventory, выполните следующие шаги:

  • Раскройте MyDB в левой панели Enterprise Manager и затем щелкните правой кнопкой мыши на Diagrams (Схемы). Выберите из контекстного меню пункт Choose New Database Diagram (Выбор новой схемы базы данных), чтобы появилось начальное окно мастера создания схемы базы данных Create Database Diagram Wizard (рис. 15.9).
  • Щелкните на кнопке Next (Далее), чтобы появилось окно Select Tables to be Added (Выбор таблиц для добавления) (рис. 15.10). Выделите таблицы, которые хотите включить в вашу схему, в списке Available Tables (Имеющиеся таблицы) и затем щелкните на кнопке Add. В этом примере мы добавили таблицы Bicycle_Inventory и Bicycle_Sales.
  • Щелкните на кнопке Next, чтобы появилось окно Completing the Create Database Diagram Wizard (Завершение работы мастера создания схемы базы данных). Щелкните на кнопке Finish (Готово), если выбраны нужные таблицы, или щелкните на кнопке Back (Назад) и внесите необходимые изменения.
  • После щелчка на кнопке Finish вы увидите схему базы данных (рис. 15.11).
  • Сохраните вашу схему, указав описательное имя (щелкните на кнопке Save Disk и введите имя, когда появится соответствующий запрос).
    •  (рис 15.10) Начальное окно мастера Create Database Diagram Wizard (Создание схемы базы данных)(рис 15.9) Окно Select Tables to be Added (Выбор таблиц для добавления)

    Вертикальная линия, которая заканчивается изображением ключа и соединяет две таблицы в вашей схеме, представляет связь между ними, выраженную ограничением foreign key. Чтобы вывести на экран метку этой связи, щелкните правой кнопкой мыши на фоновой части окна и выберите из контекстного меню пункт Show Relationship Labels (Показать метки связей). Появится имя ограничения foreign key (рис. 15.12).

    Чтобы выделить какую-либо таблицу, щелкните на ней; чтобы выделить более одной таблицы, щелкните на каждой таблице, удерживая клавишу Ctrl. Если щелкнуть правой кнопкой мыши на одной из таблиц и затем выбрать из контекстного меню какой-либо пункт, то соответствующая операция будет применена ко всем выделенным таблицам. Например, если выделить в вашей схеме баз данных обе таблицы, щелкнуть правой кнопкой мыши на одной из таблиц и затем выбрать из контекстного меню пункт Table View, затем Standard, то вид обеих таблиц будет изменен, чтобы представить на экране свойства всех колонок (рис. 15.13).

    (рис 15.12) Пример схемы базы данных(рис 15.11) Просмотр меток связей между таблицами(рис 15.13) Просмотр свойств по колонкам в схеме базы данных

    Представление свойств колонок на экране для каждой таблицы аналогично окну Design Table, и каждую таблицу можно модифицировать с помощью тех же методов, что и в окне Design Table. Просто добавьте или измените данные колонок в нужных ячейках и сохраните ваши изменения, щелкнув на кнопке Save Disk. Вы можете также перемещать таблицы и изменять их размеры в схеме базы данных, выводить на экран различные представления таблиц и выполнять другие действия. Опробуйте различные команды контекстного меню. Отметим, что как только вы вносите какое-либо изменение в таблицу, рядом с именем этой таблицы появляется символ "звездочка", указывающий, что внесены изменения.

    Следствия изменения таблицы

    Если вы изменили таблицу, то любые необходимые изменения вносятся в существующие строки данных сразу после исполнения оператора T-SQL ALTER TABLE или после сохранения изменений, если вы используете Enterprise Manager. SQL Server налагает блокировку на данную таблицу, чтобы никакие другие пользователи не имели к ней доступа, пока выполняются изменения. Модифицирование, которое требует изменения всех строк в достаточно большой таблице, такое как добавление колонки со свойством NOT NULL и значением по умолчанию или удаление колонки, может занять некоторое время и должно выполняться с осторожностью в периоды минимального пользовательского доступа. Если вы модифицируете колонку путем изменения длины ее данных, точности или масштаба, то соответствующая таблица повторно создается в базе данных и существующие данные преобразуются в новый тип данных.

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

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

    Если вы удаляете таблицу, то удаляется все, что связано с этой таблицей: определения и данные таблицы, индексы, ограничения, триггеры и полномочия доступа. Представления и хранимые процедуры, содержащие ссылки на удаляемую таблицу, должны быть удалены явным образом. Таблицу нельзя удалить, если на нее есть ссылка с помощью ограничения foreign key в другой таблице; иными словами, таблицу нельзя удалить, если от нее зависит другая таблица. Прежде чем удалить данную таблицу, следует сначала удалить это ограничение или ссылающуюся таблицу. С другой стороны, таблицу, содержащую ограничение foreign key, можно удалить, если от нее не зависит никакая другая таблица. В этом разделе мы рассмотрим удаление таблицы с помощью T-SQL и Enterprise Manager.

    Использование T-SQL для удаления таблицы

    Оператор T-SQL, используемый для удаления таблицы, имеет следующий синтаксис:

    DROP TABLE <имя_таблицы>

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

    Для удаления таблицы Bicycle_Inventory, на которую имеется ссылка из таблицы Bicycle_Sales с помощью ограничения foreign key, мы должны сначала удалить это ограничение foreign key (с именем sales_inventory_fk ) и затем удалить таблицу, как это показано ниже:

    ALTER TABLE      	Bicycle_Sales
DROP CONSTRAINT  	sales_inventory_fk
GO 
DROP TABLE       	Bicycle_Inventory 
GO

    Если вы попытаетесь удалить таблицу до удаления ограничения foreign key, то получите сообщение об ошибке и таблица не будет удалена.

    Использование Enterprise Manager для удаления таблицы

    В Enterprise Manager имеется два метода удаления таблицы: использование диалогового окна Drop Objects (Удаление объектов) или использование схемы базы данных. Диалоговое окно Drop Objects наиболее подходит, если вы удаляете таблицу, от которой не зависят никакие другие таблицы. Чтобы использовать этот метод, выполните следующие шаги:

  • В левой панели Enterprise Manager раскройте базу данных, содержащую таблицу, которую вы хотите удалить, и щелкните на папке Tables; затем в правой панели щелкните правой кнопкой мыши на имени таблицы, которую хотите удалить.
  • Выберите из контекстного меню пункт Delete (Удалить), чтобы появилось диалоговое окно Drop Objects (рис 15.14(рис 15.14) Диалоговое окно Drop Objects (Удаление объектов)
  • Если данная таблица имеет какие-либо зависимые таблицы, щелкните на кнопке Show Dependencies (Показать зависимости), чтобы появилось диалоговое окно Dependencies (рис. 15.15). Любые таблицы, зависящие от данной таблицы, появятся в левом списке этого диалогового окна. Если имеются какие-либо зависимые таблицы, то вы не можете удалить данную таблицу, пока не будут удалены соответствующие зависимости.
  • Если никакие другие таблицы не зависят от выбранной таблицы, то вы можете удалить эту таблицу, щелкнув на кнопке Drop All (Удалить все) в диалоговом окне Drop Objects.
    •  (рис 15.15) Диалоговое окно Dependencies для таблицы Bicycle_Sales

    Чтобы удалить таблицу, которая имеет зависимые таблицы, мы будем использовать второй метод: схему базы данных. В этом примере мы удалим таблицу Bicycle_Inventory, на которую имеется ссылка с помощью ограничения foreign key в таблице Bicycle_Sales. Это удаление не удалось бы выполнить, если бы мы использовали диалоговое окно Drop Objects. Но, используя схему базы данных, где показаны обе таблицы, мы можем удалить любую таблицу, а ограничение foreign key будет удалено автоматически. Для удаления таблицы Bicycle_Inventory выполните следующие шаги:

  • Откройте схему базы данных в Enterprise Manager, раскрыв базу данных, которую хотите использовать, щелкнув на Diagrams и затем дважды щелкнув на имени подходящей схемы в правой панели. Выделите имя таблицы, которую хотите удалить (в данном случае – Bicycle_Inventory).
  • Щелкните правой кнопкой мыши в любом месте этой таблицы и выберите из контекстного меню пункт Delete Table From Database (Удалить таблицу из базы данных). При появлении окна, где нужно подтвердить, что вы хотите удалить данную таблицу из базы данных, щелкните на кнопке Yes. Таблица и ограничение foreign key будут удалены из схемы.
  • Если вы уверены, что хотите окончательно удалить эту таблицу, сохраните свои изменения, щелкнув на кнопке Save Disk. После этого таблица будет удалена из базы данных. Если вы отказались от своего решения удалить таблицу, просто выйдите из окна Edit Diagram (Редактирование схемы) без сохранения изменений, щелкнув на кнопке Close (Закрыть). Если вы затем повторно откроете схему базы данных, исходные таблицы будут на прежнем месте. Никакие изменения не начнут действовать, пока вы не сохраните свою работу.
    •

    Заключение

    В этой лекции вы узнали, как использовать T-SQL и Enterprise Manager для модифицирования таблиц базы данных путем изменения, удаления, добавления и переименования колонок. В процессе изложения материала были показаны некоторые отличия между функциональными возможностями T-SQL и Enterprise Manager. Вы также узнали, как создается схема базы данных с помощью мастера Create Database Diagram Wizard и как происходит удаление таблицы и всех ее данных из базы данных. В таблицу можно вносить другие типы модификаций, включая изменение, добавление и удаление ограничений и умолчаний. (О модификациях см. лекцию 16.)

    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^