SQL ALTER TABLE — изменение таблицы базы данных
Команда языка SQL ALTER TABLE предназначена для изменения структуры столбца таблицы базы данных, если таблица уже существует.
ALTER TABLE для добавления и удаления столбца, значения по умолчанию
Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:
Пример 1. Есть база данных фирмы — Company. В ней есть таблица Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец — SEX (Пол). Соответствующий запрос выглядит следующим образом:
Таким образом в таблице появился новый столбец, значения которого могут принимать символьный тип и состоять из одного символа.
Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE для этой цели содержит ещё одну команду — ALTER COLUMN — и имеет следующий синтаксис:
Пример 2. Пусть в фирме, база данных которой имеет название Company, трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца SEX (Пол) и установить его значение по умолчанию — ‘F’ (что означает женский пол). Соответствующий запрос выглядит следующим образом:
Теперь только если новый сотрудник — мужчина, потребуется вписывать в столбец SEX значение ‘M’, а в большинстве случаев значение установится по умолчанию — ‘F’.
При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет следующим:
Пример 3. Пусть в той же базе данных Company из таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос выглядит следующим образом:
Таким образом в таблице появился новый столбец, значения которого могут принимать символьный тип и состоять из одного символа.
ALTER TABLE для добавления ключей
О ключах в таблицах базы данных — на уроке Реляционная модель данных. Если при создании таблицы базы данных не был определён первичный ключ (PRIMARY KEY), то это может быть сделано при помощи команды ALTER TABLE. Синтаксис команды в случае простого первичного ключа следующий:
Синтаксис команды в случае составного первичного ключа:
Пример 4. Работаем всё с той же базой данных Company, со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора сотрудника и сделать это так:
Если решено использовать составной ключ, состоящий из идентификатора сотрудника и идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:
ALTER TABLE для добавления и удаления ограничений
О том, что такое ограничения целостности, говорилось на уроке Реляционная модель данных. Если коротко — при удалении какой-либо строки из одной таблицы (1), в другой таблице (2), строка в которой ссылается на строку таблицы (1), не должно быть ссылок, которые не содержатся в таблице (1), то есть ссылок, ведущих в никуда.
Команда с выражением ALTER TABLE может понадобиться для установки значения NULL для такой ссылки. Её синтаксис следующий:
Пример 5. Пусть в той же базе данных Company требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть уставлено в NULL (полностью неопределенное). Соответствующий запрос выглядит следующим образом:
Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение столбца DEPT_ID примет значение NULL.
Запросом с командой ALTER TABLE можно также установить запрет на удаление ссылающихся записей из таблицы (1), если существует хотя бы одна запись в таблице (2), на которую ссылается запись в таблице (1). Синтаксис такого запроса следующий:
Пример 6. Далее работаем с базой данных сети аптек, которую мы создали на уроке Создание базы данных SQL и таблиц базы данных.
Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:
Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:
Запросом с командой ALTER TABLE можно также определить, что если удалить запись в таблице (2), на которую ссылается запись в таблице (1), то должны быть удалены все соответствующие записи в таблице (1) (каскадное удаление). Синтаксис такого запроса следующий:
Пример 7. Продолжаем работать с базой данных сети аптек.
Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.
Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:
Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:
Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:
Запросом с командой ALTER TABLE можно установить проверочное ограничение. Синтаксис такого запроса следующий:
Пример 8. Продолжаем работать с базой данных сети аптек.
Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.
25. Sql. Команда изменения структуры таблицы. Примеры.
Специальная команда ALTER TABLE предназначена для модификации структуры таблицы. С ее помощью можно изменять свойства существующих столбцов, удалять или добавлять в таблицу столбцы, а также управлять ограничением целостности как на уровне столбца, так и на уровне таблицы, т.е. выполнять следующие функции:
• добавить в таблицу определение нового столбца;
• удалить столбец из таблицы;
• изменить значение по умолчанию для какого-либо столбца;
• добавить или удалить первичный ключ таблицы;
• добавить или удалить внешний ключ таблицы;
• добавить или удалить условие уникальности;
• добавить или удалить условие на значение.
Основные режимы использования команды ALTER TABLE следующие:
• изменение, добавление и удаление ограничений (первичных и внешних ключей, значений по умолчанию).
1. Добавление столбца.
ALTER TABLE Студенты
ADD Год_поступления INTEGER NOT NUL DEFAULT YEAR(GETDATE())
В структуру таблицы «Студент» будет добавлен еще один столбец со значением по умолчанию, равным текущему году.
2. Модификация столбца.
Для модификации существующего столбца служит ключевое слово ALTER COLUMN. Изменение столбца невозможно, если:
• столбец участвует в ограничениях PRIMARY KEY или FOREGIG KEY;
• на столбец наложены ограничения целостности CHECK или UNIQUE (исключение – столбцы, имеющие тип данных переменной длины, т.е. типы данных, начинающиеся на var);
• со столбцом связано значение по умолчанию.
Определяя для столбца новый тип данных, следует помнить о том, что старый тип данных должен конвертироваться в новый.
ALTER TABLE Студенты
ALTER COLUMN Номер_группы CHAR(6) NOT NULL
3. Удаление столбца.
Нельзя удалять столбцы с ограничением целостности CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY, а также столбцы, для которых определены значения по умолчанию.
ALTER TABLE Студенты
DROP COLUMN Год_поступления
4. Добавление ограничений на уровне таблицы.
Добавление внешних ключей в таблицу «Учебный план» (создание связи с именем FK_Дисциплина и связи с именем FK_Кадровый_состав_):
ALTER TABLE Ученый план
ADD CONSTRAINT FK_Дисциплина
FOREIGN KEY (ID_Дисциплина)
ALTER TABLE Ученый план
ADD CONSTRAINT FK_Кадровый_состав
FOREIGN KEY (ID_Преподаватель)
С помощью конструкции ADD CONSTRAINT создается поименованное ограничение. Необходимо отметить, что удаление любого ограничения на уровне таблицы происходит только по его имени, поэтому ограничение должно быть поименовано (чтобы его можно было удалить).
Добавление значения по умолчанию для столбца Номер_группы:
ALTER TABLE Студент
ADD CONSTRAINT DEF_Номер_группы DEFAULT 1 FOR Номер_группы
На уровне таблицы будет создано ограничение целостности с именем DEF_Номер_группы.
5. Удаление ограничений.
ALTER TABLE Учебный_план
DROP CONSTRAINT FK_Дисциплина
ALTER TABLE Студент
DROP CONSTRAINT DEF_Номер_группы.
6. Удаление таблиц.
Невозможно удалить таблицу, если на нее с помощью ограничения целостности FOREIGN KEY ссылается другая таблица: попытка удаления таблицы «Дисциплины» вызовет сообщение об ошибке, так как на таблицу «Дисциплины» ссылается таблица «Учебный_план»: DROP TABLE Дисциплины.
26. SQL. Команды добавления, модификации и удаления данных. Примеры.
1. Добавление данных – команда INSERT
Команда может быть использована для вставки как одной, так и множества строк.
INSERT [INTO] <имя_таблицы> [<список_столбцов>]
Имя таблицы – идентифицирует имя таблицы, в которую необходимо вставить строку данных.
Список столбцов – задает имена столбцов, в которые будет производиться добавление данных.
Пример: таблица «Сводная_ведомость». Каждая строка таблицы содержит результат сдачи экзамена (зачета) по отдельной дисциплине отдельным студентом. Если студент, ID_Студент которого равен 10, сдал экзамен со значением 4 в столбце ID_Дисциплина на оценку «пять», то команда добавления этих данных в таблицу «Сводная_ведомость» выглядит следующим образом:
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. Мы можем:
Добавить столбец в таблицу
Мы можем добавить столбцы в таблицу с помощью команды ALTER TABLE с оператором ADD. Например:
Здесь мы добавили столбец с именем phone в таблицу Customers.
Добавить несколько столбцов в таблицу
Мы также можем добавить сразу несколько столбцов в таблицу. Например:
Здесь мы добавили столбцы phone и age в таблицу Customers.
Переименовать столбец в таблице
Мы можем переименовать столбцы в таблице с помощью команды ALTER TABLE с оператором RENAME COLUMN. Например:
Здесь мы изменили имя столбца customer_id на c_id в таблице Customers.
Изменить столбец в таблице
Мы также можем изменить тип данных столбца с помощью команды ALTER TABLE с оператором MODIFY или ALTER COLUMN. Например: