Как изменить структуру таблицы базы данных

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

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

• добавить
  в таблицу определение нового столбца;

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

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

• добавить
  или удалить первичный ключ таблицы;

• добавить
  или удалить внешний ключ таблицы;

• добавить
  или удалить условие уникальности;

• добавить
  или удалить условие на значение.

Обобщенный
синтаксис команды ALTER
TABLE
представлен на слайде (слайд
11).

Команда
ALTER
TABLE
берет на себя все действия по копированию
данных во временную таблицу, удалению
старой таблицы, созданию вместо нее
новой таблицы с нужной структурой и
последующим переписыванием в нее данных.

Назначение
многих параметров и ключевых слов
команды ALTER
TABLE
аналогично назначению соответствующих
параметров и ключевых слов команды
CREATE
TABLE
(например, синтаксис конструкции
<определение_столбца> совпадает с
синтаксисом аналогичной конструкции
команды CREATE
TABLE).

Основные
режимы использования команды ALTER
TABLE
следующие:

• добавление
  столбца;

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

• модификация
  столбца;

• изменение,
  добавление и удаление ограничений
  (первичных и внешних ключей, значений
  по умолчанию).

Добавление
столбца

Для
добавления нового столбца следует
использовать ключевое слово ADD,
после которого должно стоять определение
столбца.

Добавим,
например, в таблицу «Студенты» столбец
«Год_поступления» (слайд
12).
После выполнения этой команды в структуру
таблицы «Студент» будет добавлен еще
один столбец со значением по умолчанию,
равным текущему году (значение по
умолчанию вычисляется с помощью двух
встроенных функций — YEAR()
и GETDATE()).

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

Для
модификации существующего столбца
таблицы служит ключевое слово ALTER
COLUMN.
Изменение свойств столбца невозможно,
если:

столбец
участвует в ограничениях PRIMARY KEY или
FOREIGN KEY;

на
столбец наложены ограничения целостности
CHECK или UNIQUE (исключение составляют
столбцы, имеющие тип данных переменной
длины, т.е. типы данных, начинающиеся на
var);

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

1. Определяя
   для столбца новый тип данных, следует
   помнить о том, что старый тип данных
   должен конвертироваться в новый.

Пример
модификации столбца «Номер_группы»
таблицы «Студенты» (тип данных INTEGER
заменяется на CHAR)
(слайд
12).

Удаление
столбца

Для
удаления столбца из таблицы используется
предложение DROP
COLUMN
<имя_столбца>. При удалении столбцов
следует учитывать, что нельзя удалять
столбцы с ограничениями целостности
CHECK,
FOREIGN
KEY,
UNIQUE
или PRIMARY
KEY,
а также столбцы, для которых определены
значения по умолчанию (в виде ограничения
целостности на уровне столбца или на
уровне таблицы).

Рассмотрим,
например, команду удаления из таблицы
«Студент» столбца «Год_поступления»
(слайд
12).

Эта
команда выполнена не будет, т.т. при
добавлении столбца было определено
значение по умолчанию.

Добавление
ограничений на уровне таблицы

Для
добавления ограничений на уровне таблицы
используется предложение ADD
CONSTRAINT
<имя_ограничения>.

В
качестве примера рассмотрим команды
добавления внешних ключей в таблицы
базы данных «Сессия» (слайд
13):

• добавление
  внешних ключей в таблицу «Учебный_план»
  (создание связи с именем FK_Дисциплина
  и связи с именем FK_
  Кадровый_состав);

• добавление
  внешних ключей в таблицу «Сводная_ведомость»
  (создание связи с именем FK_Студент
  и связи с именем FK_План).

С
помощью конструкции ADD
CONSTRAINT
создается поименованное ограничение.
Необходимо отметить, что удаление любого
ограничения на уровне таблицы происходит
только по его имени, поэтому ограничение
должно быть поименовано (чтобы его можно
было удалить).

Удаление
ограничений

Для
удаления из таблицы ограничения
целостности используется предложение
DROP
CONSTRAINT
<имя_ограничения>.

Удаление
ограничения целостности возможно только
в том случае, когда оно поименовано
(т.е. предложение <определение_ограничения>
содержит именование ограничения
CONSTRAINT).

Команда
удаления построенного внешнего ключа
FK_Дисциплина
из таблицы «Учебный_план» выглядит
следующим образом (слайд
14).

На
слайде (слайд
14)
показано удаление построенного ранее
ограничения на значение по умолчанию
DEF_Номер_группы.

Соседние файлы в предмете Базы данных

• #
• #
• #
• #
• #
• #
• #

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

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

Запрос ALTER TABLE

Запрос ALTER TABLE используют, чтобы изменять структуру столбца таблицы базы данных. Он включает четыре операции:

• Добавление колонки
• Переименование колонки
• Удаление колонки
• Обновление колонки

Разберем каждую операцию подробнее.

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

С помощью ALTER можно добавить колонку в уже имеющуюся таблицу:

В этом примере мы добавили колонку age.

Делается это таким образом:

-- в таблице "users"
-- добавить колонку с именем "age" и типом "int"
ALTER TABLE users ADD COLUMN age int;

Здесь после фразы ADD COLUMN идет строка описания новой колонки, точно такая же, как и при создании таблицы. В простом варианте она выглядит так: <имя колонки> <тип>. Здесь можно указывать любые ограничения, добавлять ключи, автогенерацию, значение по умолчанию и многое другое. Синтаксис на 100% совпадает с синтаксисом определения колонки, когда таблица создается.

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

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

-- в таблице "courses"
-- изменить колонку "example1":
-- поменять имя с "example1" на "example2"
ALTER TABLE courses RENAME COLUMN example1 TO example2;

Здесь нет никаких сложностей — одно имя меняется на другое.

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

Следующий запрос удаляет колонку:

-- в таблице "courses"
-- удалить колонку с именем "example2"
ALTER TABLE courses DROP COLUMN example2;

Это тоже простая операция, которая убирает ненужную колонку.

Обновление колонки

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

ALTER TABLE addresses
    ADD PRIMARY KEY (id);

ALTER TABLE addresses
    ALTER COLUMN created_at SET DATA TYPE timestamp,
    ALTER COLUMN street DROP NOT NULL;

-- Добавление ограничения NOT NULL в таблицу addresses для колонки city
ALTER TABLE addresses
    ALTER COLUMN city SET NOT NULL;

-- Добавление уникального индекса в таблицу "products" для колонки "product_id"
ALTER TABLE products ADD UNIQUE (product_id);

Наиболее распространенные команды:

• ADD — добавление ограничения: например, ключа или уникальности
• SET — установка значения: например, типа данных
• DROP — удаление ограничения

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

Выводы

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

Изменение таблицы

Последнее обновление: 09.07.2017

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

Общий формальный синтаксис команды выглядит следующим образом:

ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] | 
  DROP COLUMN название_столбца |
  ALTER COLUMN название_столбца тип_данных_столбца [NULL|NOT NULL] |
  ADD [CONSTRAINT] определение_ограничения |
  DROP [CONSTRAINT] имя_ограничения}

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

Добавление нового столбца

Добавим в таблицу Customers новый столбец Address:

ALTER TABLE Customers
ADD Address NVARCHAR(50) NULL;

В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать
значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:

ALTER TABLE Customers
ADD Address NVARCHAR(50) NOT NULL;

Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT:

ALTER TABLE Customers
ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';

В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение «Неизвестно».

Удаление столбца

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers
DROP COLUMN Address;

Изменение типа столбца

Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200):

ALTER TABLE Customers
ALTER COLUMN FirstName NVARCHAR(200);

Добавление ограничения CHECK

При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют
ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.

ALTER TABLE Customers
ADD CHECK (Age > 21);

Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой.
Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных,
используется выражение WITH NOCHECK:

ALTER TABLE Customers WITH NOCHECK
ADD CHECK (Age > 21);

По умолчанию используется значение WITH CHECK, которое проверяет на соответствие ограничениям.

Добавление внешнего ключа

Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:

CREATE TABLE Customers
(
	Id INT PRIMARY KEY IDENTITY,
	Age INT DEFAULT 18, 
	FirstName NVARCHAR(20) NOT NULL,
	LastName NVARCHAR(20) NOT NULL,
	Email VARCHAR(30) UNIQUE,
	Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
	Id INT IDENTITY,
	CustomerId INT,
	CreatedAt Date
);

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

ALTER TABLE Orders
ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

Добавление первичного ключа

Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:

ALTER TABLE Orders
ADD PRIMARY KEY (Id);

Добавление ограничений с именами

При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT, после которого указывается имя ограничения:

ALTER TABLE Orders
ADD CONSTRAINT PK_Orders_Id PRIMARY KEY (Id),
    CONSTRAINT FK_Orders_To_Customers FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

ALTER TABLE Customers
ADD CONSTRAINT CK_Age_Greater_Than_Zero CHECK (Age > 0);

Удаление ограничений

Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:

Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей
начинаются с «FK». А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с «CK»,
а ограничений DEFAULT — с «DF».

Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице Orders называется «FK_Orders_To_Customers».
Поэтому для удаления внешнего ключа я могу использовать следующее выражение:

ALTER TABLE Orders
DROP FK_Orders_To_Customers;