Как добавить constraint в таблицу sql
Если таблица уже была ранее создана, и ее необходимо изменить, то для этого применяется команда ALTER TABLE . Ее сокращенный формальный синтаксис:
ALTER TABLE название_таблицы
Вообще данная команда поддерживает гораздо больше опций и возможностей. Все их можно посмотреть в документации. Рассмотрим лишь основные сценарии, с которыми мы можем столкнуться.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Address:
ALTER TABLE Customers ADD Address VARCHAR(50) NULL;
В данном случае столбец Address имеет тип VARCHAR и для него определен атрибут NULL.
Удаление столбца
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers DROP COLUMN Address;
Изменение значения по умолчанию
Установим в таблице Customers для столбца Age значение по умолчанию 22:
ALTER TABLE Customers ALTER COLUMN Age SET DEFAULT 22;
Изменение типа столбца
Изменим в таблице Customers тип данных у столбца FirstName на CHAR(100) и установим для него атрибут NULL :
ALTER TABLE Customers MODIFY COLUMN FirstName CHAR(100) NULL;
Добавление и удаление внешнего ключа
Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:
CREATE TABLE Customers ( Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL ); CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date );
Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:
ALTER TABLE Orders ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);
При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT , после которого указывается имя ограничения:
ALTER TABLE Orders ADD CONSTRAINT orders_customers_fk FOREIGN KEY(CustomerId) REFERENCES Customers(Id);
В данном случае ограничение внешнего ключа называется orders_customers_fk. Затем по этому имени мы можем удалить ограничение:
ALTER TABLE Orders DROP FOREIGN KEY orders_customers_fk;
Добавление и удаление первичного ключа
Добавим в таблицу Products первичный ключ:
CREATE TABLE Products ( Id INT, Model VARCHAR(20) ); ALTER TABLE Products ADD PRIMARY KEY (Id);
Теперь удалим первичный ключ:
ALTER TABLE Products DROP PRIMARY KEY;
Добавить constraints который будет управлять Foreign Key в зависимости от значений поля в таблице
И есть таблица Customers , в которой соответственно хранятся данный клиентов. Нужно добавить в таблицу Documents поле CustomerId и связать это поле с соответствующей записью в таблице Customers, но только тогда, когла значение target = 1 . Как правильно реализовать данную задачу? Добавление поля CustomerId и заполнение его я сделал, вопрос только в реализации foreign key . Нужно добавить constraint , который на каждый alter или insert будет проверять значение target и делать запрос в таблицу Customers ? Или, может, можна как то управлять FK через constraint ? Когда target не равно 1 — CustomerId должно быть null .
Отслеживать
задан 17 апр 2018 в 21:31
317 1 1 золотой знак 3 3 серебряных знака 16 16 бронзовых знаков
Когда target не равно 1 — CustomerId должно быть null — значит, в реферальной таблице нужно добавить запись (а хоть бы и фэйковую), где референсное поле имеет значение Null. Тогда констрейнт получается простейший — CASE WHEN target=1 THEN CustomerId IS NOT NULL ELSE CustomerId IS NULL END . Ну и плюс FK, сабо самой. PS. Надеюсь, на поле не наложено требование уникальности (без учёта NULLов)?
18 апр 2018 в 4:54
Это плохая идея добавлять фейковую запись для решения данной задачи.
Оператор 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,
то получим приведенное выше сообщение об ошибке, в котором вместо имени ограничения chk_type будет упомянуто ограничение chk_maker_Z. При этом модель принтера будет добавлена без проблем:
Разумеется, другой производитель сможет выпускать все, что угодно:
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