Инструкция ALTER TABLE
Служит для изменения макета таблицы после того, как она была создана с помощью инструкции CREATE TABLE.
Примечание: Ядро СУБД Microsoft Access не поддерживает использование ALTER TABLE или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого используйте методы Create DAO.
Синтаксис
Инструкция ALTER TABLE включает в себя следующие элементы:
Имя таблицы, которую требуется изменить.
Имя поля, которое нужно добавить в таблицу или удалить из нее. Или имя поля, которое нужно изменить в таблице.
Тип данных поля.
Размер поля в знаках (только для полей с типом данных TEXT и BINARY).
Индекс поля.
индекс_набора_полей
Индекс набора полей, добавляемых в таблицу.
имя_индекса
Имя удаляемого индекса набора полей.
Замечания
Изменить существующую таблицу с помощью инструкции ALTER TABLE можно несколькими способами. Вы можете:
- Добавление поля в таблицу с помощью функции ADD COLUMN. Вы указываете имя поля, тип данных и (для полей с типом данных TEXT и BINARY) необязательный размер. Например, следующий текст добавляет в таблицу Employees поле «Текст» из 25 символов:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
Для этого поля можно также указать индекс.
Если для поля определено свойство NOT NULL, то поле обязательно должно содержать допустимые данные.
- Чтобы изменить тип данных существующего поля, используйте alter COLUMN. Вы указываете имя поля, новый тип данных и необязательный размер текстовых и двоичных полей. Например, следующая выписка меняет тип данных поля «Индекс» в таблице Employees (изначально он определен как «Integer») на 10-знаковое текстовое поле:
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
- Используйте инструкцию ADD CONSTRAINT для добавления индекса набора полей.
- Используйте инструкцию DROP COLUMN для удаления поля. Требуется указать только имя поля.
- Используйте DROP CONSTRAINT, чтобы удалить индекс набора полей. Требуется указать только имя индекса после зарезервированного слова CONSTRAINT.
- Невозможно одновременно добавить или удалить несколько полей или индексов.
- Чтобы добавить индекс для одного поля или для набора полей в таблице, используйте инструкцию CREATE INDEX. Чтобы удалить индекс, созданный с помощью инструкции ALTER TABLE или CREATE INDEX, можно использовать инструкцию ALTER TABLE или DROP.
- Свойство NOT NULL можно задавать для одного поля или внутри именованного предложения CONSTRAINT для одного или нескольких полей. Свойство NOT NULL для поля можно задать только один раз. Попытка определить это свойство повторно приведет к ошибке выполнения.
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:
ALTER TABLE имя_таблицы [WITH CHECK | WITH NOCHECK]
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
ALTER TABLE Customers ADD Address NVARCHAR(50) NULL;В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';Тогда, если в таблице существуют данные, для них для column Address добавится значение "Неизвестно".
Удаляем столбец
Теперь можно удалить column Address:
ALTER TABLE Customers DROP COLUMN Address;Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
ALTER TABLE Customers ALTER COLUMN FirstName NVARCHAR(200);Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
ALTER TABLE Customers ADD CHECK (Age > 21);При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21);По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
ALTER TABLE Orders ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
ALTER TABLE Orders ADD PRIMARY KEY (Id);Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название "FK_Orders_To_Customers". Здесь для удаления внешнего подойдёт такое выражение:
ALTER TABLE Orders DROP FK_Orders_To_Customers;Хотите знать про SQL Server больше? Добро пожаловать на курс "MS SQL Server Developer" в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
Alter table sql что это
Оператор ALTER TABLE обеспечивает возможность изменять структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы или переименовывать столбцы либо саму таблицу. Можно также изменять комментарий для таблицы и ее тип. See section 6.5.3 Синтаксис оператора CREATE TABLE .
Если оператор ALTER TABLE используется для изменения определения типа столбца, но DESCRIBE tbl_name показывает, что столбец не изменился, то, возможно, MySQL игнорирует данную модификацию по одной из причин, описанных в разделе section 6.5.3.1 Молчаливые изменения определений столбцов. Например, при попытке изменить столбец VARCHAR на CHAR MySQL будет продолжать использовать VARCHAR , если данная таблица содержит другие столбцы с переменной длиной.
Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами. Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица.
Следует отметить, что при использовании любой другой опции для ALTER TABLE кроме RENAME , MySQL всегда будет создавать временную таблицу, даже если данные, строго говоря, и не нуждаются в копировании (например, при изменении имени столбца). Мы планируем исправить это в будущем, однако, поскольку ALTER TABLE выполняется не так часто, мы (разработчики MySQL) не считаем эту задачу первоочередной. Для таблиц MyISAM можно увеличить скорость воссоздания индексной части (что является наиболее медленной частью в процессе восстановления таблицы) путем установки переменной myisam_sort_buffer_size достаточно большого значения.
- Для использования оператора ALTER TABLE необходимы привилегии ALTER , INSERT и CREATE для данной таблицы.
- Опция IGNORE является расширением MySQL по отношению к ANSI SQL92. Она управляет работой ALTER TABLE при наличии дубликатов уникальных ключей в новой таблице. Если опция IGNORE не задана, то для данной копии процесс прерывается и происходит откат назад. Если IGNORE указывается, тогда для строк с дубликатами уникальных ключей только первая строка используется, а остальные удаляются.
- Можно запустить несколько выражений ADD , ALTER , DROP и CHANGE в одной команде ALTER TABLE . Это является расширением MySQL по отношению к ANSI SQL92, где допускается только одно выражение из упомянутых в одной команде ALTER TABLE .
- Опции CHANGE col_name , DROP col_name и DROP INDEX также являются расширениями MySQL по отношению к ANSI SQL92.
- Опция MODIFY представляет собой расширение Oracle для команды ALTER TABLE .
- Необязательное слово COLUMN представляет собой ``белый шум'' и может быть опущено.
- При использовании ALTER TABLE имя_таблицы RENAME TO новое_имя без каких-либо других опций MySQL просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. See section 6.5.5 Синтаксис оператора RENAME TABL E.
- В выражении create_definition для ADD и CHANGE используется тот же синтаксис, что и для CREATE TABLE . Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE .
- Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition . Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбец INTEGER из a в b , можно сделать следующее:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
При изменении типа столбца, но не его имени синтаксис выражения CHANGE все равно требует указания обоих имен столбца, даже если они одинаковы. Например:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
Однако начиная с версии MySQL 3.22.16a можно также использовать выражение MODIFY для изменения типа столбца без переименовывания его:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
Ниже приводятся примеры, показывающие некоторые случаи употребления команды ALTER TABLE . Пример начинается с таблицы t1 , которая создается следующим образом:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
Для того чтобы переименовать таблицу из t1 в t2 :
mysql> ALTER TABLE t1 RENAME t2;
Для того чтобы изменить тип столбца с INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c :
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
Для того чтобы добавить новый столбец TIMESTAMP с именем d :
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Для того чтобы добавить индекс к столбцу d и сделать столбец a первичным ключом:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
Для того чтобы удалить столбец c :
mysql> ALTER TABLE t2 DROP COLUMN c;
Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем c :
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
Заметьте, что столбец c индексируется, так как столбцы AUTO_INCREMENT должны быть индексированы, кроме того, столбец c объявляется как NOT NULL , поскольку индексированные столбцы не могут быть NULL .
При добавлении столбца AUTO_INCREMENT значения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения команды SET INSERT_ID=# перед ALTER TABLE или использования табличной опции AUTO_INCREMENT = # . See section 5.5.6 Синтаксис команды SET .
Если столбец AUTO_INCREMENT для таблиц MyISAM , не изменяется, то номер последовательности остается прежним. При удалении столбца AUTO_INCREMENT и последующем добавлении другого столбца AUTO_INCREMENT номера будут начинаться снова с 1 .
SQL ALTER TABLE
Инструкция ALTER TABLE используется для добавления, удаления или изменения столбцов в существующей таблице.
Инструкция ALTER TABLE также используется для добавления и удаления различных ограничений на существующую таблицу.
ALTER TABLE - ADD column
Чтобы добавить столбец в таблицу, используйте следующее синтаксис:
ALTER TABLE table_name
ADD column_name datatype;
Следующий SQL - добавляет колонку "Email" для таблицы "Customers":
Пример
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
Чтобы удалить столбец в таблице, используйте следующую команду синтаксиса (обратите внимание, что некоторые системы баз данных не позволяют удалить столбец):
ALTER TABLE table_name
DROP COLUMN column_name;
Следующий SQL удаляет столбец "Email" из таблицы "Customers":
Пример
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - ALTER/MODIFY COLUMN
Чтобы изменить тип данных столбца в таблице, используйте следующие синтаксис:
SQL Сервер / MS Доступ:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Оракул (предыдущая версия 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Оракул 10G и более поздние версии:
ALTER TABLE table_name
MODIFY column_name datatype;
Пример ALTER TABLE
Посмотрите на таблицу "Persons":
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Теперь мы хотим добавить столбец с именем "DateOfBirth" в таблицу "Persons".
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
ADD DateOfBirth date;
Обратите внимание, что новый столбец "DateOfBirth" имеет тип date и будет содержать дату. Тип данных указывает, какой тип данных может содержать столбец. Для получения полного описания всех типов данных, доступных в MS Access, MySQL и SQL Server, перейдите к нашему полному Справочнику типов данных.
Таблица "Persons" теперь будет выглядеть так:
| ID | LastName | FirstName | Address | City | DateOfBirth |
|---|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes | |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Пример изменения типа данных
Теперь мы хотим изменить тип данных столбца с именем "DateOfBirth" в таблице "Persons".
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
Обратите внимание, что столбец "DateOfBirth" теперь имеет тип year и будет содержать year в двух или четырехзначном формате.
Пример DROP COLUMN
В первую очередь, мы хотим удалить столбец с именем "DateOfBirth" в таблице "Persons".
Мы используем следующую инструкцию SQL:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
Таблица "Persons" теперь будет выглядеть так:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
ТОП Учебники
ТОП Справочники
ТОП Примеры
SchoolsW3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.