Инструкция 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 для поля можно задать только один раз. Попытка определить это свойство повторно приведет к ошибке выполнения.
SQL ALTER TABLE
Команда ALTER TABLE используется для добавления, удаления или модификации колонки в уже существующей таблице.
Команда ALTER TABLE
Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:
- добавляет столбец
- добавляет ограничение целостности
- переопределяет столбец (тип данных, размер, умалчиваемое значение)
- удаляет столбец
- модифицирует характеристики памяти или иные параметры
- включает, выключает или удаляет ограничение целостности или триггер.
Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE. Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы. Изменяя типы данных существующих столбцов или добавляя столбцы в таблицу базы данных, нужно соблюдать ряд условий. Общепринято, что увеличение – это хорошо, а уменьшение, как правило, — не очень. Допустимые увеличения:
- Увеличение размера столбца CHAR или VARCHAR2
- Увеличение размера столбца NUMBER
- Добавление новых столбцов в таблицу
Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:
- Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
- Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
- Изменение типа данных столбца (только при пустом столбце для всех строк)
ALTER TABLE Пример 1
Добавление столбца в таблицу:
ALTER TABLE t1(pole1 char(10));
ALTER TABLE Пример 2
Изменение размера столбца таблицы:
ALTER TABLE t1 MODIFY (pole1 char(20));
ALTER TABLE Пример 3
Удаление столбца таблицы:
ALTER TABLE t1 DROP COLUMN pole1;
С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:
ALTER TABLE t1 RENAME TO t2;
Аналогичную операцию можно выполнить с помощью команды RENAME:
RENAME t1 TO t2;
Ограничения целостности столбцов и таблиц БД можно изменять, а также запрещать, разрешать и удалять. Это дает разработчику возможность создавать, модифицировать и удалять бизнес-правила, ограничивающие данные. Рассмотрим добавление ограничений в БД. Простота или сложность этого процесса зависит от определенных обстоятельств. Если вместе с БД создать ограничение нельзя, проще всего добавить его перед вводом данных:
ALTER TABLE Пример 4
Модификация структуры таблицы
ALTER TABLE t1 MODIFY (pole1 NOT NULL); CREATE TABLE t2 (pole1 CHAR(10) PRIMARY KEY); ALTER TABLE t1 ADD(CONSTRAINT fk_t1 FOREIGN KEY (pole1) REFERENCES t2 (pole1)); ALTER TABLE t1 ADD (UNIQUE (p_name)); ALTER TABLE t1 ADD (p_size CHAR(4) CHECK(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));
В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.
ALTER TABLE Пример 5
Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова CONSTRAINT. В этом случае команда будет выглядеть следующим образом:
ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);
Существует ряд условий создания ограничений:
- Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
- Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
- Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
- Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
- NOT NULL: NULL-значения в столбцах запрещены.
Ограничения можно разрешать и запрещать. Разрешенное ограничение выполняет свои функции, реализуя бизнес-правила по отношению к вводимым в таблицу данным, а запрещенное ограничение переводится в разряд недействующих, как если бы оно было удалено, и его правила не реализуются.
ALTER TABLE Пример 6
ALTER TABLE t1 DISABLE PRIMARY KEY; ALTER TABLE t1 DISABLE UNIQUE (p_name);
ALTER TABLE Пример 7
В некоторых случаях запрещение первичного ключа, от которого зависят внешние ключи, может вызвать определенные сложности, например:
ALTER TABLE t2 DISABLE PRIMARY KEY;Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)
Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE обязательна конструкция CASCADE:
ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;
ALTER TABLE Пример 8
Запрещенное ограничение разрешается следующим образом:
ALTER TABLE t1 ENABLE PRIMARY KEY; ALTER TABLE t1 ENABLE UNIQUE (p_name);
Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены. Ограничение, процесс создания которого завершился неудачей, не будет существовать в запрещенном виде, ожидая своего разрешения после устранения ошибки. Как правило, владелец таблицы или тот, кому предоставлены соответствующие права, может удалить ограничение:
Изменение структуры таблицы (ALTER) — Основы реляционных баз данных
Наиболее сложный запрос при работе с таблицами — обновление ее структуры. Если посмотреть официальную документацию, то общая запись этого запроса занимает больше трех экранов монитора. Но ни один человек не помнит всех возможностей этой команды. Более того, большинство из них редко выполняются. Единственное, что нужно знать — возможности этой команды — общие принципы ее работы. А детали всегда можно будет найти в документации.
В этом уроке разберем такой тип запроса, как 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 , который отвечает за изменение таблицы базы данных. Мы узнали, что с его помощью можно добавлять, переименовывать, удалять и обновлять колонки.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
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 и политика конфиденциальности.