ALTER TABLE в SQL
Мы можем изменить структуру таблицы с помощью команды ALTER TABLE. Мы можем:
Добавить столбец в таблицу
Мы можем добавить столбцы в таблицу с помощью команды ALTER TABLE с оператором ADD. Например:
Здесь мы добавили столбец с именем phone в таблицу Customers.
Добавить несколько столбцов в таблицу
Мы также можем добавить сразу несколько столбцов в таблицу. Например:
Здесь мы добавили столбцы phone и age в таблицу Customers.
Переименовать столбец в таблице
Мы можем переименовать столбцы в таблице с помощью команды ALTER TABLE с оператором RENAME COLUMN. Например:
Здесь мы изменили имя столбца customer_id на c_id в таблице Customers.
Изменить столбец в таблице
Мы также можем изменить тип данных столбца с помощью команды ALTER TABLE с оператором MODIFY или ALTER COLUMN. Например:
Как редактировать таблицы в sql
Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу. Например, добавить или удалить столбцы, изменить тип столбцов, добавить или удалить ограничения. То есть потребуется изменить определение таблицы. Для изменения таблиц используется выражение ALTER TABLE .
Общий формальный синтаксис команды выглядит следующим образом:
Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные сценарии изменения таблицы. Рассмотрим некоторые из них.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Address:
В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT:
В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение «Неизвестно».
Удаление столбца
Удалим столбец Address из таблицы Customers:
Изменение типа столбца
Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200) :
Добавление ограничения CHECK
При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.
Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK :
По умолчанию используется значение WITH CHECK , которое проверяет на соответствие ограничениям.
Добавление внешнего ключа
Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:
Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:
Добавление первичного ключа
Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:
Добавление ограничений с именами
При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT , после которого указывается имя ограничения:
Удаление ограничений
Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:
Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей начинаются с «FK». А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с «CK», а ограничений DEFAULT — с «DF».
Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице Orders называется «FK_Orders_To_Customers». Поэтому для удаления внешнего ключа я могу использовать следующее выражение:
ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
ALTER TABLE — изменение таблицы в SQL
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».
Удаляем столбец
Теперь можно удалить column Address:
Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:
Хотите знать про SQL Server больше? Добро пожаловать на курс «MS SQL Server Developer» в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД: