Справочное руководство по MySQL. Синтаксис оператора alter table Alter table sql синтаксис
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
В первой из приведенных выше команд для добавления ограничения 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 TABLE t1 DROP UNIQUE (p_name);
Вы должны войти, чтобы оставить комментарий.
Оператор ALTER TABLE
Можно выделить следующие уровни проверочных ограничений:
- уровень атрибута (столбца),
- уровень кортежа (строки),
- уровень отношения (таблицы).
В ограничении уровня столбца проверяется значение только одного отдельного столбца, другими словами, в ограничении данного типа имеется ссылка только на один столбец той таблицы, в определении которой содержится данное ограничение. Чтобы привести пример такого ограничения, вернёмся к схеме «Компьютерная фирма». В таблице Product в столбце type может находиться одно из трех значений. Мы можем запретить ввод любой другой информации в этот столбец при помощи такого ограничения:
Давайте сделаем отступление, чтобы познакомиться с оператором ALTER TABLE , который позволит нам изменять структуру таблицы, не пересоздавая её всякий раз заново. Это тем более важно, что изменение структуры может потребоваться тогда, когда таблица уже содержит данные.
С помощью оператора ALTER TABLE можно добавить или удалить столбцы, значения по умолчанию, а также ограничения.
В настоящий момент нас интересует добавление ограничения на столбец type , поэтому вначале приведём синтаксис оператора для добавления ограничения:
Давайте теперь добавим наше ограничение и проверим, как оно работает.
Чтобы убедиться в том, что ограничение работает как мы того ожидаем, попробуем добавить модель нового типа:
Как и ожидалось, в ответ мы получим сообщение об ошибке:
The INSERT statement conflicted with the CHECK constraint “chk_type”. The conflict occurred in database “learn”, table “dbo.product”, column ‘type’. The statement has been terminated.
(Конфликт инструкции INSERT с ограничением CHECK “chk_type”. Конфликт произошел в базе данных “learn”, таблица “dbo.product”, столбец ‘type’. Выполнение данной инструкции было прервано.)
Как легко догадаться, ограничение уровня строки содержит ссылки на несколько столбцов. При этом ограничение проверяется для каждой изменяемой строки отдельно. Строка может быть добавлена (или изменена), если ограничение не нарушено.
В качестве примера давайте запретим производителю Z выпускать что-либо помимо принтеров.
Итак, ограничение проверяет, что модель в таблице Product должна быть принтером производителя Z (maker=’Z’ and type= ‘printer’) или любого другого производителя (но не Z).
Если мы попытаемся добавить модель ПК производителя Z,
Разумеется, другой производитель сможет выпускать все, что угодно:
ALTER TABLE. Изменение таблиц в базе данных MySQL
Команда ALTER TABLE в MySQL
Команда ALTER TABLE используется для осуществления изменений таблицы:
- Добавление колонок
- Удаление колонок
- Модификация колонок
- Изменения имени таблицы
- Изменения кодировки таблицы
- Добавление и удаление ограничений
Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.
Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:
SHOW COLUMNS FROM table_name;
Перед началом работ выберем базу данных, с которой будем работать.
Как добавить колонку к таблице MySQL?
Синтаксис добавления колонки в таблицу:
Для примера добавим к таблице books колонку с именем discount, в которой будет хранится процент скидки на книги и amount для хранения количества книг.
Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.
Каждая новая колонка добавляется в конец таблицы. Если вы хотите добавить новую колонку после определенной колонки, то используйте команду AFTER .
Добавим колонку shelf_position сразу после колонки price.
Как удалить колонку из таблицы MySQL?
Синтаксис удаления колонки из таблицы:
Если в таблице осталась только одна колонка, то ее удалить нельзя.
Удалим колонку edition с номером издания книги.
Как переставить колонки в таблице MySQL?
Чтобы переставить колонку используйте команду AFTER, также понадобится повторно определить тип данных.
Переместим колонку с ценой Price на место после колонки Author:
Как изменить имя и тип данных у колонки в таблице MySQL?
Синтаксис смены имени и типа данных у колонки:
Сменим имя у колонки сreation_date на entry_date и тип данных c TIMESTAMP на DATE .
Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:
Как изменить значение по умолчанию в колонке таблицы MySQL?
С помощью команды ALTER также можно изменить значение по умолчанию для любой колонки, даже если оно не было установлено при создании таблицы.
Синтаксис установки значения по умолчанию для колонки:
Установим для колонки discount значение по умолчанию:
Чтобы удалить значение по умолчанию, используйте синтаксис:
Удалим созданное значение по умолчанию для колонки discount:
Как изменить имя таблицы MySQL?
Синтаксис переименования таблицы:
Изменим имя таблицы books на books_collection;
Как изменить метаданные о таблице в MySQL?
С помощью команды ALTER можно также изменить некоторые метаданные о таблице.
Для начала давайте их выведем.
Вывод для таблицы books будет выглядеть следующим образом.
mysql> SHOW TABLE STATUS LIKE ‘books’G;
*************************** 1. row ***************************
Name: books
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 24
Avg_row_length: 682
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: 25
Create_time: 2019-01-18 20:00:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Для примера мы можем изменить тип движка таблиц:
И добавить комментарий к таблице:
Понравилась статья? Расскажите о ней друзьям!
