1,4, Редактирование таблиц, Transact-SQL

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

Для изменения параметров таблицы используется оператор ALTER TABLE. Ее синтаксис достаточно большой и на первый взгляд сложный, но мы постараемся рассмотреть его по частям, и все встанет на свои места. Синтаксис оператора смотрите в листинге 1.9.

Листинг 1.9. Синтаксис команды ALTER TABLE

ALTER TABLE table 
{ [ ALTER COLUMN column_name 
    { new_data_type [ ( precision [ , scale ] ) ] 
        [ COLLATE < collation_name > ] 
        [ NULL | NOT NULL ] 
        | {ADD | DROP } ROWGUIDCOL } 
    ] 
    | ADD 
        { [ < column_definition > ] 
        |  column_name AS computed_column_expression 
        } [ ,...n ] 
    | [ WITH CHECK | WITH NOCHECK ] ADD 
        { < table_constraint > } [ ,...n ] 
    | DROP 
        { [ CONSTRAINT ] constraint_name 
            | COLUMN column } [ ,...n ] 
    | { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 
    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] } 
}

< column_definition > ::= 
    { column_name data_type } 
    [ [ DEFAULT constant_expression ] [ WITH VALUES ] 
    | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ] 
        ] 
    [ ROWGUIDCOL ] 
    [ COLLATE < collation_name > ] 
    [ < column_constraint > ] [ ...n ] 

< column_constraint > ::= 
    [ CONSTRAINT constraint_name ] 
    { [ NULL | NOT NULL ] 
        | [ { PRIMARY KEY | UNIQUE } 
            [ CLUSTERED | NONCLUSTERED ] 
            [ WITH FILLFACTOR = fillfactor ] 
            [ ON { filegroup | DEFAULT } ] 
            ] 
        | [ [ FOREIGN KEY ] 
            REFERENCES ref_table [ ( ref_column ) ] 
            [ ON DELETE { CASCADE | NO ACTION } ] 
            [ ON UPDATE { CASCADE | NO ACTION } ] 
            [ NOT FOR REPLICATION ] 
            ] 
        | CHECK [ NOT FOR REPLICATION ] 
            ( logical_expression ) 
    }

< table_constraint > ::= 
    [ CONSTRAINT constraint_name ] 
    { [ { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        { ( column [ ,...n ] ) } 
        [ WITH FILLFACTOR = fillfactor ] 
        [ ON {filegroup | DEFAULT } ] 
        ] 
        |    FOREIGN KEY 
            [ ( column [ ,...n ] ) ] 
            REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
            [ ON DELETE { CASCADE | NO ACTION } ] 
            [ ON UPDATE { CASCADE | NO ACTION } ] 
            [ NOT FOR REPLICATION ] 
        | DEFAULT constant_expression 
            [ FOR column ] [ WITH VALUES ] 
        |    CHECK [ NOT FOR REPLICATION ] 
            ( search_conditions ) 
    }

Прежде чем рассматривать возможности по изменению существующей таблицы, создадим простую таблицу для хранения информации о жителях дома. Таблицу назовем Peoples. Так как в таблице должно быть хотя бы одно поле, создадим ключевое поле. Я всегда называю поле первичного ключа как "idИмяТаблицы", т.е. для таблицы Peoples первичный ключ должен называться "idPeoples". Дадим этому полю для простоты жизни целочисленный тип int:

CREATE TABLE Peoples
(
 idPeoples int
)

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

Добавление новых полей

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

ALTER TABLE Peoples 
ADD 
 vcFamil VARCHAR(30) NOT NULL default '',
 vcName VARCHAR(30) NOT NULL default '',
 vcSurName VARCHAR(30) NULL

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

Если попытаться добавить поле, не позволяющее нулевые значения и не указать значение по умолчанию, то поле не будет добавлено. Вместо этого вы увидите сообщение об ошибке:

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

При добавлении фамилии и имени мы указываем значение по умолчанию – пустую строку ('' – между кавычками ничего нет, даже пробела, т.е. пустая строка). Это лишний раз доказывает, что пустая строка и нулевое значение – это разные вещи и сервер воспринимает их по-разному. А для отчества у нас разрешены нулевые значения.

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

Удаление полей

Для удаления полей нужно использовать оператор DROP COLUMN, после которого указывается имя удаляемой колонки. Например, следующий сценарий удаляет колонку vcSurName из таблицы:

ALTER TABLE Peoples 
DROP COLUMN vcSurName

Я думаю, что все понятно и без дополнительных комментариев. Проще уже просто некуда. После ALTER TABLE просто пишем оператор DROP COLUMN и имя удаляемой колонки.

Изменение ограничений

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

ALTER TABLE Peoples 
ADD dDate datetime NULL

Устанавливаем ограничение, чтобы дата не была больше текущей:

ALTER TABLE Peoples WITH NOCHECK 
ADD CONSTRAINT check_dDate CHECK 
   (dDate < getdate())

Как видите, для добавления используется оператор ADD CONSTRAINT. После этого указывается имя ограничения и само ограничение CHECK. Для удаления используется оператор DROP CONSTRAINT, которому нужно указать имя ограничения, которое нужно удалить:

ALTER TABLE Peoples DROP CONSTRAINT check_dDate

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

Итак, давайте вернем ограничение даты на родину:

ALTER TABLE Peoples WITH NOCHECK 
ADD CONSTRAINT check_dDate CHECK 
   (dDate < getdate())

Попробуем добавить строку с датой 10.10.2010. Я думаю, что когда вы будете читать эту книгу, этот год еще не наступит :):

INSERT INTO Peoples(idPeoples, vcFamil, vcName, dDate)
VALUES(1, 'Иванов', 'Иван', '10.10.2010')

В ответ на это мы получим ошибку с сообщением о попытке нарушить ограничение, и строка не будет добавлена. Давайте отключим временно ограничение:

ALTER TABLE Peoples 
NOCHECK CONSTRAINT check_dDate

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

Снова включаем ограничение. Для этого используется оператор CHECK CONSTRAINT, которому нужно указать имя ограничения, которое надо включить:

ALTER TABLE Peoples 
CHECK CONSTRAINT check_dDate

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

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

ALTER TABLE Peoples 
ADD CONSTRAINT check_vcFamil 
  CHECK (vcFamil <> '')

Теперь попробуйте добавить строку, в которой в поле vcFamil будет пустая строка:

INSERT INTO Peoples (vcFamil) 
VALUES ('')

В ответ на это, сервер вернет нам ошибку ограничения check_vcFamil.

В данном случае, мы явно пытаемся прописать в поле vcFamil пустую строку. А что, если это поле вообще не указать при добавлении новой записи? В следующем примере добавляется новая строка, но при этом мы заполняем только поле vcName:

INSERT INTO Peoples (vcName) 
VALUES ('')

И все равно сработает ограничение check_vcFamil. Дело в том, что для поля vcFamil установлено значение по умолчанию – пустая строка. Добавляя новую запись, и не указав значение, будет взято значение по умолчанию, а так как ограничение сработало, значит проверка ограничений происходит после того, как база данных установит для полей значения по умолчанию. Таким образом, мы реализовали необходимую защиту от нулевых и пустых значений. Конечно, это стоило нам некоторых дополнительных усилий и появились лишние затраты сервера на установку значения по умолчанию и вызов проверки ограничения CHECK, но это не такие уж и высокие затраты. Да, то, что нам пришлось так выкручиваться - это минус для MS SQL Server, но минус очень маленький, на который можно закрыть глаза.

Изменение поля

Зачем изменять уже существующее поле? На первый взгляд незачем, но в реальности оказывается, что это действительно нужно. Например, вы указали, что поле должно иметь размер 20 символов, но в определенный момент оказалось, что этого недостаточно и необходимо расширить размер поля. Для этого используется команда ALTER COLUMN. Но тут есть свои подводные камни. Попробуйте изменить размер имени или фамилии в созданной нами таблице Peoples:

ALTER TABLE Peoples 
ALTER COLUMN vcName NVARCHAR(40) NOT NULL

Я уверен, что вы увидите ошибку типа:

В первой строки говориться, что объект DF__Peoples__vcName__72C60C4A ссылается на колонку vcName. Во второй строке сообщения нам говорят, что команда не выполнена, потому что один или более объектов ссылаются на выбранное поле.

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

Exec sp_help peoples

В результате мы увидим несколько таблиц с параметрами таблицы peoples. В самой последней можно увидеть указанное имя объекта в колонке constraint_name. Вот в чем проблема – на поле ссылается ограничение, которое проверяет наличие пустого значения, ведь мы указали для поля vcName свойство NOT NULL. Сначала необходимо удалить это ограничение, и только потом можно будет изменить размер поля:

ALTER TABLE Peoples 
DROP CONSTRAINT DF__Peoples__vcName__72C60C4A 

ALTER TABLE Peoples 
ALTER COLUMN vcName NVARCHAR(40) NOT NULL

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