ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
use slcbookshelf; Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL; Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL DEFAULT 'Неизвестен'; Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
ALTER TABLE books RENAME COLUMN author TO authors; Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
ALTER TABLE books RENAME TO books_selectel; Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
ALTER TABLE books DROP COLUMN authors; Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
ALTER TABLE books ALTER COLUMN book_category VARCHAR(200); В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
ALTER TABLE books MODIFY book_category VARCHAR(200), MODIFY book_name VARCHAR(200), . ; Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
ALTER TABLE books ALTER COLUMN book_category NVARCHAR(200); Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
ALTER TABLE books ADD PRIMARY KEY (book_id); Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
ALTER TABLE Customers ADD CHECK (Age > 21); При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21); Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
ALTER TABLE Customers ADD CONSTRAINT Check_Age_Greater_Than_Twenty_One CHECK (Age > 21); Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
ALTER TABLE Customers DROP Check_Age_Greater_Than_Twenty_One; После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
Сброс пароля root в MySQL
CONSTRAINT: целостность базы данных
Еще одна важная вещь, которую нужно знать об базах данных — это констрейты (CONSTRAINS). С помощью констрейнов ты можешь контролировать изменения данных в своих таблицах и поддерживать их целостность и согласованность.
Что такое согласованность данных, когда мы говорим о базе данных?
Давай возьмем наш интернет-магазин с таблицами employee, product и task. Мы уже знаем, что в таблице task могут быть задачи, не назначенные ни на кого: employee_id у таких строк равен NULL.
А вот что будет, если в таблице task будет запись с employee_id равным, допустим, 115? Ведь у нас нет такого сотрудника. У нас нет сотрудника с в таблице employee. В то же время ссылка на сотрудника с таким id есть в таблице task. Это и есть пример несогласованности данных.
Так как же нам согласовать эти данные? Идеально было бы сделать так, что SQL-сервер при любом изменении данных контролировал все эти нюансы. И такая возможность есть, называется она FOREIGN_KEY.
Если какая-то колонка в вашей таблице содержит не просто цифры, а id строк из другой таблицы, то это можно указать явно.
Добавление FOREIGN KEY
Такой ключ можно добавлять в таблицу как на этапе ее создания, так и после, с помощью ALTER TABLE. Формат принципиально не отличается. Мы приведем оба варианта.
Общий вид такого ключа/правила имеет вид:
FOREIGN KEY (колонка) REFERENCES таблица(колонка) Давай добавим в таблицу task этот ключ/правило, чтобы гарантировать, что все employee_id из таблицы ссылаются на существующую запись в таблице employee. Выглядеть этот скрипт будет так:
ALTER TABLE task ADD FOREIGN KEY (employee_id) REFERENCES employee(id) А если бы мы решили добавить это правило в момент создания таблицы task, то код выглядел бы так:
CREATE TABLE task ( id INT, name VARCHAR(100), employee_id INT, deadline DATE, PRIMARY KEY (id), FOREIGN KEY (employee_id) REFERENCES employee(id) ); Кстати, бывают ситуации, когда строка, на которую мы ссылаемся, имеет уникальный составной ключ: например “ФИО и год рождения” или “productCatogoryId и productId”. Тогда FOREIGN KEY можно записать так:
FOREIGN KEY (наша_колонка1, наша_колонка2) REFERENCES таблица(их_колонка1, их_колонка2) FOREIGN KEY и изменение данных
А теперь представь ситуацию, когда в таблице employee мы решили обновить некоторые данные и у нас поменялся id сотрудника. Что при этом произойдет с данными в таблице task? Правильно, они станут неактуальными, и целостность нашей базы данных нарушится.
Чтобы такого не произошло, можно указать SQL-серверу, чтобы он при изменении id в таблице employee поменял бы и employee_id всех строк во всех таблицах, которые ссылаются на это конкретное изменившееся id.
Такие сценарии называются OnUpdate и OnDelete. Что сделать в случае изменения id-записи, и что делать в случае удаления записи?
С удалением вообще не все так однозначно. Если у тебя есть зависимые объекты, представленные строками в БД, которые ссылаются друг на друга, то возможны самые разнообразные сценарии поведения при удалении одного объекта.
Допустим, у нас удаляется пользователь сайта, значит, мы должны удалить всю его личную переписку. Но вряд ли мы при этом должны удалить все его публичные комментарии.
Или увольняется сотрудник. Было бы странно, чтобы он уволился и одновременно из базы исчезли все задачи, которые на него назначены. Но если бы они остались назначенными не него, тоже вышло бы нехорошо. Правильнее сделать так, чтобы сотрудник мог уволиться после того, как переназначил все свои задачи на других людей.
Вот как мы можем описать эти сценарии с помощью FOREIGN KEY. Общий вид такого ключа/правила имеет вид:
FOREIGN KEY (колонка) REFERENCES таблица(колонка) [ON DELETE reference_option] [ON UPDATE reference_option] Что делать в случае удаления (ON DELETE) или изменения (ON UPDATE) записей? Всего может быть 5 вариантов действия SQL-сервера в каждой из этих ситуаций:
| # | reference_option | Пояснение |
|---|---|---|
| 1 | RESTRICT | Запретить действие, если найдены ссылки на строку |
| 2 | CASCADE | Поменять id в зависимых строках |
| 3 | SET NULL | Установить id в зависимых строках в NULL |
| 4 | NO ACTION | Ничего не делать |
| 5 | SET DEFAULT x | Установить id в зависимых стоках в значение x |
Вот как бы мы могли изменить нашу таблицу task:
ALTER TABLE task ADD FOREIGN KEY (employee_id) REFERENCES employee(id) ON UPDATE CASCADE ON DELETE RESTRICT; Что тут написано:
ON UPDATE CASCADE: Если меняется ключ id в таблице employee, то так же поменять employee_id в таблице task, которая на него ссылается.
ON DELETE RESTRICT: Если удаляется строка из таблицы employee и на нее есть ссылка из таблицы task, то запретить удаление строки из таблицы employee.
SQL — Ограничения
От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.
Ограничения могут применяться либо на уровне столбцов, либо на уровне таблицы. Ограничения на уровне столбца применяются только к одному столбцу, тогда как ограничения уровне таблицы применяются ко всей таблице.
Ниже приведены некоторые из наиболее часто используемых ограничений, доступных в SQL. Эти ограничения уже рассматривались в главе «Концепции SQL-RDBMS» , но сейчас мы вернемся к ним еще раз.
NOT NULL Constraint — столбец не может иметь значение NULL.
DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.
UNIQUE Constraint — все значения в столбце должны быть разными.
PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.
FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.
CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.
INDEX — используется для быстрого создания данных базы данных.
Ограничения могут указываться при создании таблицы с помощью оператора CREATE TABLE или вы можете использовать оператор ALTER TABLE для создания ограничений уже после создания таблицы.
Удаление ограничений
Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.
Ограничения в SQL
Ограничение (constraints) — это ограничение типа значений, которое накладывается на один или несколько столбцов таблицы. Это позволяет поддерживать точность и целостность данных в таблице БД.
В SQL существует несколько различных типов ограничений, в том числе:
- NOT NULL
- PRIMARY KEY
- UNIQUE
- DEFAULT
- FOREIGN KEY
- CHECK
Давайте обсудим каждое из этих ограничений попродробнее.
Ограничение NOT NULL
Ограничение NOT NULL указывает, что столбец не может принимать значения NULL .
Если к столбцу применено ограничение NOT NULL , вы не сможете вставить новую строку в таблицу без добавления не-NULL-значения в этот столбец.
Пример
Следующая SQL-инструкция создает таблицу persons с четырьмя столбцами, из которых три столбца — id , name и phone — не могут иметь значение NULL .
CREATE TABLE persons ( id INT NOT NULL, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL );
Примечание. Нулевое значение ( NULL ) — это не ноль(0) и не строка символов нулевой длины (»). NULL означает, что записи нет.
Ограниение PRIMARY KEY
Ограничение PRIMARY KEY определяет столбец или набор столбцов, значения которых однозначно идентифицируют строку в таблице. То есть никакие две строки в таблице не могут иметь одинаковое значение первичного ключа. Также нельзя вводить значение NULL в столбец первичного ключа.
Пример
Следующая SQL-инструкция создает таблицу persons и указывает столбец id в качестве первичного ключа. Это означает, что в этом поле не допускаются значения NULL или дубликаты.
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL );
Примечание. Первичный ключ обычно состоит из одного столбца в таблице, однако несколько столбцов могут составлять первичный ключ. Например, адрес электронной почты сотрудника или присвоенный ID является логическим первичным ключом для таблицы сотрудников.
Ограничение UNIQUE
Ограничение UNIQUE означает, что в указанных столбцах обязательно должны быть уникальные значения.
Хотя и ограничение UNIQUE, и ограничение PRIMARY KEY обеспечивают уникальность значений, есть различия.
UNIQUE лучше PRIMARY KEY, когда вы хотите обеспечить уникальность столбца или комбинации столбцов, которые не являются первичным ключом.
Пример
Следующая SQL-инструкция создает таблицу persons и определяет столбец phone как уникальный. Это означает, что это поле не допускает дублирования значений.
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE );
Примечание. В одной таблице может быть задано ограничений UNIQUE , но только одно ограничение PRIMARY KEY . Кроме того, в отличие от ограничений PRIMARY KEY , ограничения UNIQUE допускают значения NULL .
Ограничение DEFAULT
Ограничение DEFAULT определяет значение по умолчанию для столбцов.
Значение столбца по умолчанию — это некоторое значение, которое будет вставлено в столбец базой данных, если оператор INSERT явно не назначит конкретное значение.
Пример
В следующей SQL-инструкции мы задаем значение по умолчанию для столбца country .
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE, country VARCHAR(30) NOT NULL DEFAULT 'Россия' );
Примечание. Если вы определили столбец таблицы как NOT NULL , но присвоили ему значение по умолчанию, то в операторе INSERT вам не нужно явно присваивать значение этому столбцу, чтобы вставить новую строку в таблицу.
Ограничение FOREIGN KEY
Внешний ключ (foreign key) — это столбец или комбинация столбцов, которые используются для установления и обеспечения взаимосвязи между данными в двух таблицах.
Ниже — диаграмма, которая показывает связь между таблицами сотрудников ( employees ) и отделов ( departments ). Если вы внимательно посмотрите на нее, то заметите, что столбец dept_id таблицы сотрудников совпадает со столбцом первичного ключа таблицы отделов. Поэтому столбец dept_id таблицы сотрудников является внешним ключом для таблицы отделов.
В MySQL можно создать внешний ключ, задав ограничение FOREIGN KEY при создании таблицы следующим образом.
Пример
В следующей SQL-инструкции мы определяем столбец dept_id таблицы employees как внешний ключ, который ссылается на столбец dept_id таблицы departments .
CREATE TABLE employees ( emp_id INT NOT NULL PRIMARY KEY, emp_name VARCHAR(55) NOT NULL, hire_date DATE NOT NULL, salary INT, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
Ограничение CHECK
Ограничение CHECK используется для ограничения значений, которые могут быть помещены в столбец.
Например, диапазон значений для столбца зарплаты salary можно ограничить, создав ограничение CHECK , которое допускает значения только от 30 000 до 100 000. Это предотвратит ввод зарплат за пределами обычного (условного) диапазона.
Пример
CREATE TABLE employees ( emp_id INT NOT NULL PRIMARY KEY, emp_name VARCHAR(55) NOT NULL, hire_date DATE NOT NULL, salary INT NOT NULL CHECK (salary >= 3000 AND salary , dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
Примечание. MySQL не поддерживает ограничение CHECK.
СodeСhick.io — простой и эффективный способ изучения программирования.
2023 © ООО «Алгоритмы и практика»