Mysql примеры всех запросов alter table. Справочное руководство по MySQL. ALTER имя_столбца SET DEFAULT значение_по_умолчанию

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

ALTER TABLE для добавления и удаления столбца, значения по умолчанию

Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD ИМЯ_НОВОГО_СТОЛБЦА ТИП_ДАННЫХ

Пример 1. Есть база данных фирмы - Company. В ней есть таблица Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец - SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD SEX CHAR(1)

Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE для этой цели содержит ещё одну команду - ALTER COLUMN - и имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ALTER COLUMN ИМЯ_СТОЛБЦА SET DEFAULT "ЗНАЧЕНИЕ_ПО_УМОЛЧАНИЮ"

Пример 2. Пусть в фирме, база данных которой имеет название Company, трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца SEX (Пол) и установить его значение по умолчанию - "F" (что означает женский пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ALTER COLUMN SEX SET DEFAULT "F"

Теперь только если новый сотрудник - мужчина, потребуется вписывать в столбец SEX значение "M", а в большинстве случаев значение установится по умолчанию - "F".

При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет следующим:

ALTER TABLE ИМЯ_ТАБЛИЦЫ DROP COLUMN ИМЯ_СТОЛБЦА

Пример 3. Пусть в той же базе данных Company из таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE DROP COLUMN SEX

Таким образом в таблице появился новый столбец, значения которого могут принимать символьный тип и состоять из одного символа.

ALTER TABLE для добавления ключей

О ключах в таблицах базы данных - на уроке Реляционная модель данных . Если при создании таблицы базы данных не был определён первичный ключ (PRIMARY KEY), то это может быть сделано при помощи команды ALTER TABLE. Синтаксис команды в случае простого первичного ключа следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА)

Синтаксис команды в случае составного первичного ключа:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА_1, ИМЯ_ИДЕНТИФИКАТОРА_2)

Пример 4. Работаем всё с той же базой данных Company, со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора сотрудника и сделать это так:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID)

Если решено использовать составной ключ, состоящий из идентификатора сотрудника и идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID, DEPT_ID)

ALTER TABLE для добавления и удаления ограничений

О том, что такое ограничения целостности, говорилось на уроке Реляционная модель данных . Если коротко - при удалении какой-либо строки из одной таблицы (1), в другой таблице (2), строка в которой ссылается на строку таблицы (1), не должно быть ссылок, которые не содержатся в таблице (1), то есть ссылок, ведущих в никуда.

Команда с выражением ALTER TABLE может понадобиться для установки значения NULL для такой ссылки. Её синтаксис следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE SET NULL

Пример 5. Пусть в той же базе данных Company требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть уставлено в NULL (полностью неопределенное). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD CONSTRAINT DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT ON DELETE SET NULL

Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение столбца DEPT_ID примет значение NULL.

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

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE RESTRICT

Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:

alter table Employee add constraint PH_ID foreign key(PH_ID) references Pharmacy on delete restrict

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

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE CASCADE

Пример 7.

Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Iv class="even">

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Запросом с командой ALTER TABLE можно установить проверочное ограничение. Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD CONSTRAINT CHECK_DATA CHECK(ПРОВЕРЯЕМОЕ_УСЛОВИЕ)

Пример 8. Продолжаем работать с базой данных сети аптек.

Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Реляционные базы данных и язык SQL

Н а этом уроке SQL мы рассмотрим, как использовать команду команду ALTER к таблице в базе данных. Команда ALTER в основном используется для добавления, изменения и удаление столбцов, к индексам и ограничениям на таблицы в реляционной базе данных, например в , Oracle, Sybase и SQL Server. Хотя ALTER не является частью классической операции CRUD, это одна из важных команд DDL. Одним из наиболее частого использования команды ALTER в SQL является добавление и удаление индексов для повышения производительности запросов SQL SELECT. В этом обучающей статье мы увидим различные способы использования команды ALTER. С помощью команды ALTER можно добавлять, редактировать или изменять таблицы, представления в базы данных. Мы можем добавить новый столбец в таблице, мы можем изменить значение столбца или мы можем переименовать столбец.

10 Примеров команды ALTER в SQL

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

Пример 1: Как добавить столбец в существующую таблицу с помощью ALTER в SQL:

Предположим, у нас есть одна таблица с именем студента с полем roll_number, stu_name, class, subject , и мы хотим добавить еще один столбец marks-obtained, как будет делать с помощью команды ALTER.

ALTER TABLE Student ADD marks_obtained Number (3);

Когда это поле добавляется в таблицу он будет содержать нулевое значение по умолчанию.

Пример 2: Как изменить существующий столбец с помощью команды ALTER в SQL :

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

ALTER TABLE Student ALTER COLUMN marks_obtained DECIMAL (5, 2);

Пример 3: Как удалить существующий столбец из таблицы с помощью команды ALTER в SQL:

Теперь, если мы хотим, удалить существующий столбец из таблицы мы используем DROP вместе с командой ALTER

ALTER TABLE Student DROP column marks_obtained;

Пример 4. Как удалить первичный ключ с помощью команды ALTER в базе данных :

Предположим, существует первичный ключ с именем ‘pk_roll_num’ для столбца roll_number из таблицы Student, и мы хотим отказаться от этого ограничения

ALTER TABLE Student DROP CONSTRAINT "pk_roll_num’;

Пример 5. Как добавить ограничения первичного ключа с помощью команды ALTER в SQL:

Теперь мы хотим добавить ограничение первичного ключа в таблице Student под названием ‘pk_roll_num’ для столбца roll_number ‘. Мы делаем так

ALTER TABLE Student ADD Constraint "pk_roll_num PRIMARY KEY(roll_number)

Пример 6. Как сбросить ограничение внешнего ключа с помощью команды ALTER :

Предположим, что существует ограничение внешнего ключа с именем ‘fk_roll_num’ для столбца roll_number ‘ из таблицы ‘Marks’, и мы хотим отказаться от этого ограничения

ALTER TABLE Marks DROP CONSTRAINT "fk_roll_num’;

Пример 7: Как добавить ограничения внешнего ключа с помощью команды ALTER

Теперь мы хотим добавить ограничение внешнего ключа в таблицу Marks, названный ‘fk_roll_num’ для столбца roll_number “. Мы сделаем так:

ALTER TABLE Marks ADD Constraint "fk_roll_num FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Пример 8: Как добавить уникальные ключевые ограничения при помощи команды ALTER

Теперь, если мы хотим добавить уникальный ключ ограничения в таблицу Student под названием ‘unique_roll_no’ для столбца roll_number. Мы сделаем так:

ALTER TABLE Student ADD CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Пример 9 : Как удалить уникальный ключ Constraint с помощью команды ALTER

Теперь, если мы хотим отказаться от уникального ключа ограничения в таблице Student под названием ‘unique_roll_no’ для столбца roll_number. Мы сделаем так:

ALTER TABLE Student DROP CONSTRAINT unique_roll_no);

Пример 10: Как добавить СНЕСК с помощью команды ALTER в SQL

Основной синтаксис для добавления проверочного ограничения в таблице:

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Это все о примерах команды ALTER в SQL. Мы видели 10 различных способов использования команды ALTER в SQL. ALTER является одним из самых важных во время работы в любой базе данных, например, MySQL, Oracle или SQL Server, особенно из командной строки. Так как нам часто нужно добавлять, изменять и удалять столбцы, индексы и ограничения в таблице.

В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

Описание

MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.

Добавить столбец в таблицу

Синтаксис

Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name

FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts . Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id .

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

Синтаксис

Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
new_column_name — имя нового столбца для добавления в таблицу.
column_definition — тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.

Пример

Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;

Этот пример ALTER TABLE добавит в таблицу contacts два столбца — last_name и first_name .

Поле last_name будет создано как столбец varchar (40) NOT NULL и появится в таблице contacts после столбца contact_id . Столбец first_name будет создан как столбец NULL varchar (35) и появится в таблице после столбца last_name .

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

Синтаксис

Синтаксис для изменения столбца в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name

[ FIRST | AFTER column_name ];

table_name — имя таблицы для изменения.

Пример

Рассмотрим пример, который показывает, как изменить столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts MODIFY last_name varchar(50) NULL;

Этот пример ALTER TABLE изменит столбец с именем last_name как тип данных varchar (50) и установит для столбца значения NULL.

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице MySQL (с использованием оператора ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — имя таблицы для изменения.
column_name — имя столбца для изменения в таблице.
column_definition — измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name — необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице MySQL, используя оператор ALTER TABLE.

MySQL

ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;

Этот пример ALTER TABLE будет изменять в таблице contacts два столбца — last_name и first_name .
Поле last_name будет изменено на столбец NULL varchar (55) и появится в таблице после столбца contact_type . Столбец first_name будет изменен на столбец varchar (30) NOT NULL (и не изменит позицию в определении таблицы contacts , так как не указано FIRST | AFTER).

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

Синтаксис

Синтаксис для удаления столбца из таблицы в MySQL (с использованием оператора ALTER TABLE):
Например:

ALTER TABLE table_name
DROP COLUMN column_name;

table_name — имя таблицы для изменения.
column_name — имя столбца для удаления из таблицы.

Пример

Рассмотрим пример, который показывает, как удалить столбец из таблицы в MySQL с помощью оператора ALTER TABLE.
Например:

MySQL

ALTER TABLE contacts DROP COLUMN contact_type;

ALTER TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:

ADD create_definition

или ADD (create_definition, create_definition,...)

или ADD INDEX (index_col_name,...)

или ADD PRIMARY KEY (index_col_name,...)

или ADD UNIQUE (index_col_name,...)

или ADD FULLTEXT (index_col_name,...)

или ADD FOREIGN KEY index_name (index_col_name,...)

или ALTER col_name {SET DEFAULT literal | DROP DEFAULT}

или CHANGE old_col_name create_definition

или MODIFY create_definition

или DROP col_name

или DROP PRIMARY KEY

или DROP INDEX index_name

или DISABLE KEYS

или ENABLE KEYS

или RENAME new_tbl_name

или ORDER BY col

или table_options

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

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

Следует отметить, что при использовании любой другой опции для ALTER TABLE,кромеRENAME, 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 просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. В выраженииcreate_definitionдляADDиCHANGEиспользуется тот же синтаксис, что и для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;

При использовании CHANGEилиMODIFYдля того, чтобы уменьшить длину столбца, по части которого построен индекс (например, индекс по первым 10 символам столбцаVARCHAR), нельзя сделать столбец короче, чем число проиндексированных символов.

При изменении типа столбца с использованием CHANGEилиMODIFYMySQL пытается преобразовать данные в новый тип как можно корректнее.

В версии MySQL 3.22 и более поздних можно использовать FIRSTилиADD ... AFTER имя_столбцадля добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые словаFIRSTиAFTERв опцияхCHANGEилиMODIFY.

Опция ALTER COLUMNзадает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значениеNULL, то новое значение по умолчанию будетNULL. Если столбец не может бытьNULL, то MySQL назначает значение по умолчанию. ОпцияDROP INDEXудаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.

Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду DROP TABLE.

Опция DROP PRIMARY KEYудаляет первичный индекс. Если такого индекса в данной таблице не существует, то удаляется первый индексUNIQUEв этой таблице. (MySQL отмечает первый уникальный ключUNIQUEкак первичный ключPRIMARY KEY, если никакой другой первичный ключPRIMARY KEYне был явно указан). При добавленииUNIQUE INDEXилиPRIMARY KEYв таблицу они хранятся перед остальными неуникальными ключами, чтобы можно было определить дублирующиеся ключи как можно раньше.

Опция ORDER BYпозволяет создавать новую таблицу со строками, размещенными в заданном порядке. Следует учитывать, что созданная таблица не будет сохранять этот порядок строк после операций вставки и удаления. В некоторых случаях такая возможность может облегчить операцию сортировки в MySQL, если таблица имеет такое расположение столбцов, которое Вы хотели бы иметь в дальнейшем. Эта опция в основном полезна, если заранее известен определенный порядок, в котором преимущественно будут запрашиваться строки. Использование данной опции после значительных преобразований таблицы дает возможность получить более высокую производительность.

При использовании команды ALTER TABLEдля таблицMyISAMвсе неуникальные индексы создаются в отдельном пакете (подобноREPAIR). Благодаря этому командаALTER TABLEпри наличии нескольких индексов будет работать быстрее.

Начиная с MySQL 4.0, вышеуказанная возможность может быть активизирована явным образом. Команда ALTER TABLE ... DISABLE KEYSблокирует в MySQL обновление неуникальных индексов для таблицMyISAM. После этого можно применить командуALTER TABLE ... ENABLE KEYSдля воссоздания недостающих индексов. Так как MySQL делает это с помощью специального алгоритма, который намного быстрее в сравнении со вставкой ключей один за другим, блокировка ключей может дать существенное ускорение на больших массивах вставок.

Применяя функцию C API mysql_info(), можно определить, сколько записей было скопировано, а также (при использованииIGNORE) - сколько записей было удалено из-за дублирования значений уникальных ключей.

Выражения FOREIGN KEY,CHECKиREFERENCESфактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других серверов SQL и запуск приложений, создающих таблицы со ссылками.

Ниже приводятся примеры, показывающие некоторые случаи употребления команды 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,

Заметьте, что столбец cиндексируется, так как столбцыAUTO_INCREMENTдолжны быть индексированы; кроме того, столбецcобъявляется какNOT NULL, поскольку индексированные столбцы не могут бытьNULL.

При добавлении столбца AUTO_INCREMENTзначения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения командыSET INSERT_ID=#передALTER TABLEили использования табличной опцииAUTO_INCREMENT = #.

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

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

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

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

Таким образом, с помощью 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;