Работа с ограничениями в MySQL
Пересказ статьи Robert Sheldon. Working with MySQL constraints
MySQL предоставляет набор ограничений, которые вы можете включить в определения ваших таблиц для обеспечения целостности данных. Ограничения позволяют лучше контролировать типы данных, которые могут быть добавлены в базу данных. Например, вы можете использовать ограничения, чтобы установить уникальность столбца или допустимость NULL-значений, указать значение по умолчанию для столбца или проверить, что данные попадают в определенный интервал допустимых значений.
MySQL поддерживает шесть основных типов ограничений для обеспечения целостности данных: PRIMARY KEY, NOT NULL, DEFAULT, CHECK, UNIQUE и FOREIGN KEY. В этой статье я познакомлю вас с каждым типом и рассмотрю примеры их работы. Примеры включают ряд операторов CREATE TABLE, которые демонстрируют различные способы встраивания ограничений в определения таблиц. Если вы не знакомы с оператором CREATE TABLE или с тем, как создавать таблицы в базе данных MySQL, обратитесь к моей более ранней статье этой серии.
Некоторые источники также считают типы данных ограничениями, поскольку они ограничивают значения данных, которые могут быть вставлены в столбцы таблицы. Например, столбец, сконфигурированный с целочисленным типом данных будет принимать целые числа, но не десятичные или строковые значения. Здесь я не буду рассматривать типы данных, а сосредоточусь лишь на других шести типах ограничений. Относительно типов данных обратитесь к статье MySQL.
Замечание. Примеры в этой статье выполнялись на локальном экземпляре MySQL с очень простой базой данных. В последнем разделе статьи - "Приложение: Подготовка среды MySQL" - приведена информация о том, как я настроил среду, и приложен скрипт SQL для создания используемой в примерах базы данных.
Ограничения PRIMARY KEY
Ограничение PRIMARY KEY (первичный ключ) обеспечивает механизм уникальной идентификации каждой строки в таблице. Оно определяется на одном или нескольких столбцах таблицы (ключевые столбцы) для обеспечения уникальности строк. При добавлении ограничения PRIMARY KEY в таблицу MySQL также создает уникальный индекс на ключевых столбцах, чтобы поддерживать их уникальность.
Таблица может иметь только один первичный ключ, и ключевые столбцы должны быть определены как NOT NULL. Если они явно не определены как NOT NULL, MySQL неявно объявит их таковыми, чтобы обеспечить каждую строку в таблице связанным значением первичного ключа. Это важно, поскольку сам уникальный индекс может содержать множество NULL-значений, поэтому NOT NULL необходимо для гарантии, что каждая строка имеет связанный с ней уникальный идентификатор.
Вы можете добавить первичный ключ в таблицу при ее создании. Самый простой способ добавления одностолбцового первичного ключа - это включить его в определение столбца, как показано в следующем примере:
DROP TABLE IF EXISTS airplanes;
CREATE TABLE airplanes (
plane_id INT UNSIGNED PRIMARY KEY,
plane VARCHAR(50),
engine_type VARCHAR(50),
engine_count TINYINT);Этот оператор создает первичный ключ на столбце plane_id. Определение столбца включает ключевые слова PRIMARY KEY, которые сообщают MySQL создать ограничение PRIMARY KEY на этом столбце при создании таблицы airplanes. Одновременно с этим MySQL создает уникальный индекс.
Это все, что необходимо сделать для создания первичного ключа. Остальное сделает MySQL. Если вы хотите создать первичный ключ на нескольких столбцах, вы должны определить ограничение отдельно от определения столбца, что мы скоро обсудим.
Замечание. Оператор CREATE TABLE в этом примере предваряется оператором DROP TABLE, который включает опцию IF EXISTS. Поскольку пример в этой статье повторно создает таблицу airplanes, вам нужно перед каждый примером применять оператор DROP TABLE, если вы собираетесь выполнять у себя эти примеры. Хотя я не буду повторять в последующем оператор DROP TABLE.
MySQL отслеживает все ограничения, определенные на всех таблицах в базе данных. Вы можете увидеть имеющиеся ограничения, выполнив запрос к таблице TABLE_CONSTRAINTS в INFORMATION_SCHEMA, которая содержит все метаданные в базе данных. Следующий оператор SELECT получает информацию о существующих ограничениях в базе данных travel:
SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE constraint_schema = 'travel';Этот оператор возвращает результаты, показанные на следующем рисунке. Результаты свидетельствуют, что ограничения PRIMARY KEY были определены на таблицах airplanes и manufacturers, который были созданы при подготовке этой статьи.
MySQL автоматически присваивает имя PRIMARY каждому ограничению первичного ключа и связанному с ним уникальному индексу. В отличие от других типов ограничений, вы не можете изменить эти имена. Кроме того, вы не можете использовать имя PRIMARY для других создаваемых вами индексов.
Вы можете проверить работу ограничения PRIMARY KEY в таблице airplanes, выполнив следующий оператор INSERT дважды подряд:
INSERT INTO airplanes
(plane_id, plane, engine_type, engine_count)
VALUES (1001,'A340-600','Jet',4);При первом выполнении этого оператора MySQL вставляет данные без проблем, а при повторном выполнении MySQL вернет следующую ошибку, поскольку нарушается ограничение PRIMARY KEY:
Error Code: 1062. Duplicate entry '1001' for key 'airplanes.PRIMARY'Вы можете использовать другой метод определения ограничения PRIMARY KEY - добавление отдельного определения ограничения после определений столбцов, как в следующем примере:
CREATE TABLE airplanes (
plane_id INT UNSIGNED,
plane VARCHAR(50),
engine_type VARCHAR(50),
engine_count TINYINT,
PRIMARY KEY (plane_id));Этот оператор CREATE TABLE приводит к тем же результатам, что и предыдущий оператор. Однако в этом случае вам требуется указать столбец, на котором будет создан первичный ключ. Тут MySQL создаст ограничение PRIMARY KEY на столбце plane_id, именуя ограничение и индекс PRIMARY.
В некоторых случаях вам может потребоваться создать ограничение PRIMARY KEY на нескольких столбцах. Например, у вас может быть два столбца в таблице, которые по отдельности не идентифицируют однозначно каждую строку, но вместе делают это. В следующем примере оператор CREATE TABLE определяет первичный ключ на столбцах plane_id и alt_id:
CREATE TABLE airplanes (
plane_id INT UNSIGNED,
alt_id INT UNSIGNED,
plane VARCHAR(50),
engine_type VARCHAR(50),
engine_count TINYINT,
PRIMARY KEY (plane_id, alt_id));Первичный ключ, состоящий из нескольких столбцов иногда называют составным первичным ключом. Необходимость использовать составные первичные ключи диктуется природой ваших данных. В нашем случае столбцы plane_id и alt_id используются совместно для создания уникального идентификатора каждой строки в качестве первичного ключа. Для тестирования его работы начните с выполнения следующего оператора INSERT:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type, engine_count)
VALUES (1001,173,'A340-600','Jet',4);Оператор должен без проблем вставить данные, поскольку нет конфликтующих значений первичного ключа. Теперь выполните следующий оператор INSERT, который задает то же самое значение plane_id, но отличное значение alt_id:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type, engine_count)
VALUES (1001,174,'A340-600','Jet',4);И снова оператор INSERT должен выполниться нормально, поскольку комбинация значений двух столбцов по-прежнему уникальна. Вы можете проверить, что таблица airplanes теперь содержит две строки данных, выполнив следующий оператор SELECT:
SELECT * FROM airplanes;Оператор возвращает результаты, показанные на следующем рисунке и подтверждающие, что информация в обеих строках совпадает за исключением значений alt_id.
Если повторно выполнить предыдущий оператор INSERT (или использовать те же самые значения plane_id и alt_id в другом операторе INSERT), MySQL должен вернуть следующую ошибку:
Error Code: 1062. Duplicate entry '1001-174' for key 'airplanes.PRIMARY'При определении первичных ключей в таблицах InnoDB старайтесь использовать ключевые столбцы как можно более короткими, чтобы минимизировать накладные расходы на хранение, как-то использование целых чисел вместо 20-символьных строк. Каждый вторичный индекс в таблице InnoDB содержит копию столбца первичного ключа для соответствующих строк, и могут накапливаться избыточные данные. Более короткие ключевые столбцы могут также улучшить производительность запросов, в зависимости от их типа.
Заметьте, что вам не обязательно удалять всю таблицу, чтобы изменить ограничение. С помощью оператора ALTER TABLE вы можете удалить ограничение первичного ключа, а затем пересоздать его.
-- Удаление существующего ограничения
ALTER TABLE airplanes
DROP PRIMARY KEY;
-- Добавление нового ограничения PRIMARY KEY
ALTER TABLE airplanes
ADD PRIMARY KEY (plane_id, alt_id);Если вы новичок в MySQL, синтаксис изменения первичного ключа может показаться вам неожиданным, но ограничение PRIMARY KEY имеет установленное имя, в отличие от некоторых других типов РСУБД.
Ограничения NOT NULL
При определении столбца в операторе CREATE TABLE вы можете указать, допускает ли столбец NULL-значения. Значение NULL обычно означает, что данные отсутствуют или же значение столбца неизвестно. Это отличается от значения 0 или пустой строки, хотя NULL иногда путают с этими значениями.
Вы можете задать свойство допустимости NULL-значений, включив ключевые слова NULL (допустимо) или NOT NULL (недопустимо) в определение столбца. По умолчанию MySQL допускает NULL-значения, поэтому, если вы не укажите вариант явно, MySQL будет предполагать NULL, только если столбец не является первичным ключом. Если вы не хотите допускать NULL, то должны добавить ключевые слова NOT NULL в определения столбцов, как показано в следующем примере:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
PRIMARY KEY (plane_id));Оператор CREATE TABLE тот же, что и в предыдущем примере, только определение каждого столбца включает NOT NULL. Вы можете проверить допустимость NULL, выполнив несколько операторов INSERT, начав со следующего:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type, engine_count)
VALUES (1001,173,'A340-600','Jet',4);Этот оператор должен выполниться без проблем, поскольку он предоставляет не-NULL значения для всех столбцов. Однако теперь попытайтесь выполнить следующий оператор INSERT, который задает NULL в качестве значения engine_type:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type, engine_count)
VALUES (1002,174,'A350-800 XWB',NULL,2); Теперь MySQL возвращает следующую ошибку, в которой сообщается, что значение engine_type не может быть NULL:
Error Code: 1048. Column 'engine_type' cannot be nullВместо попытки вставить NULL-значение вы могли попытаться вставить строку без указания значения engine_type:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_count)
VALUES (1002,174,'A350-800 XWB',2); Теперь вы получите другое сообщение об ошибке, поскольку MySQL не знает, что делать со столбцом engine_type:
Error Code: 1364. Field 'engine_type' doesn't have a default value Если столбцу назначено значение по умолчанию, MySQL вставит это значение в столбец, если в запросе значение не предоставлено. При отсутствии значения по умолчанию MySQL вернет эту ошибку.
Однако это справедливо, если на сервере MySQL включен строгий режим (strict mode), который принимается по умолчанию. Если строгий режим выключен, MySQL будет неявно вставлять значение по умолчанию, принятое для типа данных, которым в нашем случае является пустая строка. Так как типом данных для столбца engine_type является VARCHAR, то MySQL использует пустую строку в качестве неявного значения по умолчанию для всех строковых типов данных за исключением ENUM.
Замечание. Обсуждение строгого режима и того, как выключить и включить его, выходит за рамки этой статьи. За информацией обратитесь к соответствующей статье MySQL.
Вам может потребоваться определить столбец, допускающим NULL-значения; в этом случае вы можете указать NULL в определении столбца или просто опустить эту опцию. (Многие команды баз данных предпочитают включать установки по умолчанию в определение схемы, что считается лучшей практикой.) Следующий оператор CREATE TABLE устанавливает значение по умолчанию столбца engine_type в NULL:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NULL,
engine_count TINYINT NOT NULL,
PRIMARY KEY (plane_id));Попытаемся теперь вставить строку в таблицу без указания значения engine_type:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_count)
VALUES (1001,173,'A340-600',4); Теперь MySQL вставит NULL для значения столбца, в чем можно убедиться, обратившись к таблице airplanes:
SELECT * FROM airplanes; На следующем рисунке показаны результаты, возвращаемые оператором SELECT. Как видно, значение engine_type установлено в NULL.
Дебаты о поддержке NULL-значений в реляционных базах данных ведутся годами (как и о том, что означают значения NULL). В какой степени использовать NULL будет зависеть от политики, которой придерживается ваша команда. Если вы стараетесь ограничить использование NULL, вы можете иногда заменять неизвестные значения добавлением ограничений DEFAULT в определения столбцов.
Ограничения DEFAULT
При создании или обновлении таблицы вы можете добавить ограничения DEFAULT к определениям столбцов. Ограничение DEFAULT задает значение для использования в столбце, когда оператор INSERT не предоставляет значение.
Значением по умолчанию может быть литеральная константа или скалярное выражение. Если вы задаете выражение, он должно придерживаться следующих правил:
- Выражение должно заключаться в скобки.
- Выражение может ссылаться на другие столбцы, но не может зависеть от столбца, определенного с AUTO_INCREMENT.
- Выражение не может включать подзапросы, параметры, переменные, хранимые функции или загружаемые функции. Однако оно может включать операторы, литералы или встроенные функции (как детерминированные, так и недерминированные).
Чтобы добавить литеральное ограничение DEFAULT в определение столбца, вам нужно только написать ключевое слово DEFAULT с последующим значением по умолчанию, которое соответствует типу данных столбца. Например, следующий оператор CREATE TABLE определяет ограничение DEFAULT для столбца engine_type:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
PRIMARY KEY (plane_id)); В этом случае значением по умолчанию является строка unknown. Вы можете протестировать это, выполнив следующий оператор INSERT, который не включает значение столбца engine_type:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_count)
VALUES (1001,173,'A340-600',4);Для проверки добавления значения по умолчанию вы можете выполнить этот оператор SELECT:
SELECT * FROM airplanes;Оператор возвращает результаты, показанные на рисунке ниже, который подтверждает, что столбец engine_type имеет значение unknown.
Как отмечалось выше, вы можете задать выражение в качестве значения по умолчания, а не литерал. Например, следующий оператор CREATE TABLE содержит столбцы create_date и last_update со значениями по умолчанию:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id));Оба новых столбца используют функцию CURRENT_TIMESTAMP для значения по умолчанию. Столбец last_update тоже включает предложение ON UPDATE CURRENT_TIMESTAMP, которое поручает MySQL обновлять значение столбца при обновлении строки. Однако это предложение не является частью предложения DEFAULT. В обоих определениях столбца выражением по умолчанию является только функция CURRENT_TIMESTAMP.
Вы могли заметить, что предложение DEFAULT в определениях двух столбцов не заключает свои выражения в скобки. Дело в том, что скобки не требуются, если вы используете функцию CURRENT_TIMESTAMP в качестве значения по умолчанию в столбцах TIMESTAMP или DATETIME. Можно проверить, что это работает, выполнив следуюшие операторы INSERT и SELECT:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type, engine_count)
VALUES (1001,173,'A340-600','Jet',4);
SELECT * FROM airplanes;Оператор INSERT выполняется без проблем, а оператор SELECT возвращает ожидаемые результаты, которые представлены на картинке. MySQL автоматически добавляет временную метку в столбцы create_date и last_update. Если вы заключите выражение по умолчанию в скобки, то получите те же самые результаты.
Если строку обновить, MySQL автоматически обновит столбец last_update значением текущей временной метки, обеспечивая запись времени последнего обновления строки.
Ограничения CHECK
Другим типом ограничений, поддерживаемое MySQL, является ограничение CHECK, которое проверяет, что каждое значение данных, вставляемое в столбец, удовлетворяет требованиям, указанным в ограничении. Ограничение CHECK определяет выражение, которое должно принимать значение TRUE или UNKNOWN (для NULL-значений) для значения добавляемого в столбец. Если выражение равно FALSE, вставка или обновление завершается неудачно, и MySQL сообщает о нарушении ограничения.
Ограничение CHECK может задаваться в определении столбца или после определений столбцов. В любом случае выражение ограничения должно придерживаться следующих правил:
- Выражение не может ссылаться на столбец, определенный с AUTO_INCREMENT, или на столбец в другой таблице.
- Выражение не может включать хранимые функции, загружаемые функции, процедурные или функциональные параметры, переменные или подзапросы. Однако выражение может включать литералы, операторы или детерминированные встроенные функции.
Для создания ограничения CHECK в определении столбца вам необходимо только указать ключевое слово CHECK с последующим выражением. Например, следующий оператор CREATE TABLE определяет ограничение CHECK на столбце wingspan:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL
CHECK (wingspan BETWEEN 10 AND 400),
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id)); Выражение ограничения CHECK указывает, что значение wingspan должно находиться между 10 и 400. Вы можете проверить корректность его работы, выполнив сначала следующий оператор INSERT, который указывает для wingspan значение 208.17.
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan)
VALUES (1001,173,'A340-600','Jet',4,208.17);MySQL должен вставить эту строку, поскольку значение wingspan удовлетворяет критерию, заданному ограничением CHECK. А теперь попробуйте добавить значение, которое выходит за допустимый диапазон:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan)
VALUES (1002,174,'A350-800 XWB','Jet',2,408.17); В этом случае для wingspan задается значение 408.17, которое вызывает следующую ошибку:
Error Code: 3819. Check constraint 'airplanes_chk_1' is violated.Обратите внимание на то, что в сообщении об ошибке фигурирует ограничение airplanes_chk_1. Это имя, которое MySQL автоматически присвоило ограничению при его создании. Вы можете подтвердить это, опять обратившись к информационной схеме, как мы делали это после добавления первичного ключа:
SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE constraint_schema = 'travel';Следующие рисунки показывают результаты, возвращаемые оператором SELECT, которые включают вновь добавленное ограничение CHECK:
MySQL следует конкретной формуле при именовании ограничения. Для ограничений CHECK используется имя таблицы с последующим _chk_, а затем последовательный номер, который автоматически инкрементируется при каждом новом ограничении CHECK. Однако вы можете сами предоставить имя для ограничения, как в следующем примере:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id));Чтобы дать имя ограничению, вы предваряете ключевое слово CHECK ключевым словом CONSTRAINT с последующим именем ограничения, которым в нашем случае является chk_wingspan. Теперь, если обратиться к информационной схеме, результаты покажут новое имя, как на следующем рисунке.
При именовании ограничения CHECK или ограничений определенного типа имя ограничения должно быть уникальным в пределах базы данных и для типа ограничений. Это означает, что вы не можете создать два ограничения CHECK в одной и той же базе с одинаковым именем chk_wingspan, даже если они определены на разных таблицах. (При именовании ограничения следуйте правилам, принятым в вашей команде.)
Вы можете также определить ограничение CHECK после определения столбцов, как это делалось нами для ограничений PRIMARY KEY:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400));Этот оператор CREATE TABLE даст тот же результат, что и предыдущий, создав ограничение CHECK с именем chk_wingspan. Одним из преимуществ создания ограничения CHECK после определения столбцов заключается в том, что вы не привязаны к конкретному столбцу, давая возможность ссылаться на несколько столбцов в вашем выражении, как это делается в следующем примере:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)));Оператор определяет ограничение CHECK c именем chk_length. Выражение в ограничении обеспечивает значение plane_length, которое всегда будет меньше удвоенного значения wingspan. Это тот сорт ограничений, которые помогают избежать вставки аномальных данных. Вы можете проверить работу ограничения, выполнив пару операторов INSERT, начиная со следующего:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1001,173,'A340-600','Jet',4,208.17,247.24);Этот оператор INSERT должен выполниться без проблем, поскольку значение plane_length попадает в допустимый интервал, но что если выполнить следующий оператор, который задает значение plane_length равным 498.58:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1002,174,'A350-800 XWB','Jet',2,212.42,498.58); Поскольку значение plane_length превышает величину, заданную в выражении CHECK, MySQL вернет следующую ошибку:
Error Code: 3819. Check constraint 'chk_length' is violated.Ограничения CHECK в MySQL могут быть полезны, когда вам необходимо применить бизнес правила, которые описывают допустимые типы данных. По этой причине ограничения CHECK часто определяются спецификой тех обстоятельств, в которых они применяются.
Ограничения могут быть добавлены и удалены из таблицы при помощи оператора ALTER TABLE, используя имя ограничения:
ALTER TABLE airplanes
DROP CONSTRAINT chk_wingspan;
ALTER TABLE airplanes
ADD CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400);Ограничения UNIQUE
Ограничение UNIQUE создает уникальный индекс на одном или более ключевых столбцов. Индекс гарантирует уникальность вставляемых в столбцы данных. Единственное исключение представляет значение NULL. В отличие от некоторых систем управления базами данных MySQL допускает уникальные индексы с множеством NULL-значений. Однако вы можете избежать этого с конфигурацией NOT NULL.
Простейший способ определить ограничение UNIQUE на единственном столбце - это добавить его в определение столбца. Например, следующий оператор CREATE TABLE определяет ограничение UNIQUE на столбце alt_id:
DROP TABLE IF EXISTS airplanes;
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL UNIQUE,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)));Как и для других примеров, вы можете протестировать ограничение, выполнив операторы INSERT, начиная с этого:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1001,173,'A340-600','Jet',4,208.17,247.24);Этот оператор должен выполниться успешно, но следующий - нет, поскольку значение alt_id нарушает ограничение UNIQUE, т.к. оператор снова пытается вставить значение 173:
INSERT INTO airplanes
(plane_id, alt_id, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1002,173,'A350-800 XWB','Jet',2,212.42,198.58);Неудивительно, что оператор возвращает следующую ошибку:
Error Code: 1062. Duplicate entry '173' for key 'airplanes.alt_id'Как и для других типов ограничений, вы можете также определить ограничение UNIQUE после определений столбцов, хотя в этом случае вы должны будете указать имя столбца:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)),
UNIQUE (alt_id));После добавления ограничения UNIQUE в определение таблицы, вы можете снова обратиться к INFORMATION_SCHEMA, которая должна дать вам результаты, показанные на следующем рисунке.
MySQL назвал ограничение UNIQUE alt_id в честь столбца, в котором определено ограничение. MySQL также присвоил это имя связанному индексу. Однако вы можете сами дать имя ограничению (и индексу), как и в случае ограничения CHECK:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)),
CONSTRAINT uc_alt_id UNIQUE (alt_id));В этом случае именем ограничения является uc_alt_id, которое вы опять проверить, обратившись к информационной схеме. Вы можете также определить ограничения UNIQUE на нескольких столбцах, как в случае первичного ключа. В следующем примере я определяю составное ограничение UNIQUE на столбцах alt_id1 и alt_id2.
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id1 INT UNSIGNED NOT NULL,
alt_id2 INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)),
CONSTRAINT uc_alt_id UNIQUE (alt_id1, alt_id2));Составное ограничение UNIQUE работает точно как ограничение PRIMARY KEY при вставке данных. Например, следующие два оператора INSERT выполняются успешно, несмотря на то, что они задают одно и то же значение alt_id1:
INSERT INTO airplanes
(plane_id, alt_id1, alt_id2, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1001,173,297,'A340-600','Jet',4,208.17,247.24);
INSERT INTO airplanes
(plane_id, alt_id1, alt_id2, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1002,173,298,'A350-800 XWB','Jet',2,212.42,198.58);Однако следующий оператор INSERT пытается добавить пару значений alt_id1 и alt_id2, которые уже существуют:
INSERT INTO airplanes
(plane_id, alt_id1, alt_id2, plane, engine_type,
engine_count, wingspan, plane_length)
VALUES (1003,173,298,'A350-900','Jet',2,212.42,198.58);Как ожидалось, MySQL возвращает следующую ошибку:
Error Code: 1062. Duplicate entry '173-298' for key 'airplanes.uc_alt_id'Добавление ограничения UNIQUE в таблицу довольно простой процесс. Однако помните, что MySQL использует одно и то же имя для ограничения и уникального индекса, поэтому не пытайтесь создать другой индекс с тем же именем.
Замечание: Как и ранее, вы можете удалить и изменить ограничение UNIQUE, используя оператор ALTER TABLE.
Ограничение FOREIGN KEY
Другим типом ограничений, поддерживаемых MySQL, является ограничение FOREIGN KEY. Оно отличается от других ограничений в том, что позволяет вам установить ссылочную целостность между таблицами. Внешним ключ устанавливает связь между родительской и дочерней таблицами. Родительская таблица содержит значения столбца, на который ссылаются, а дочерняя таблица содержит значения ссылающегося столбца.
Замечание. Тема внешних ключей значительно более емкая, чем я могу осветить ее в данной статье. Эта статья призвана дать вам общую картину ограничений в MySQL. Более подробную информацию о внешних ключах вы найдете в этой статье MySQL.
Хотя вы можете установить ограничение FOREIGN KEY, которое ссылается на столбец в той же таблице (что может иметь место при работе с иерархическими данными), большинство внешних ключей ссылается на один или более столбцов в другой таблице. Например, следующий оператор CREATE TABLE определяет внешний ключ, который ссылается на столбец manufacturer_id в таблице manufacturers:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
CHECK (plane_length < (wingspan * 2)),
CONSTRAINT uc_ids UNIQUE (plane_id, alt_id),
FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id));При определении внешнего ключа вы должны задать предложение FOREIGN KEY с последующим именем столбца, на котором вы создаете внешний ключ. Вы должны также включить предложение REFERENCES, которое задает родительскую таблицу и целевой столбец в этой таблице. В нашем случае столбец manufacturer_id в таблице airplanes ссылается на столбец manufacturer_id в таблице manufacturers.
Для тестирования внешнего ключа вы можете выполнить следующий оператор INSERT, который использует 101 для значения manufacturer_id:
INSERT INTO airplanes
(plane_id, alt_id, plane, manufacturer_id, engine_type,
engine_count, wingspan, plane_length)
VALUES (1001,173,'A340-600',101,'Jet',4,208.17,247.24); Чтобы оператор INSERT выполнился успешно, таблица manufacturers должна включать строку с значением manufacturer_id 101, которое там есть (в предположении, что вы создали и заполнили таблицу manufacturers). Но предположим, что теперь вы выполнили следующий оператор INSERT, который использует значение manufacturer_id, отсутствующее в таблице manufacturers:
INSERT INTO airplanes
(plane_id, alt_id, plane, manufacturer_id, engine_type,
engine_count, wingspan, plane_length)
VALUES (1002,175,'A350-800 XWB',121,'Jet',2,212.42,198.58); Если вы попытаетесмь выполнить этот оператор, MySQL вернет следующую ошибку:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint
fails (`travel`.`airplanes`, CONSTRAINT `ibfk_1` FOREIGN KEY (`manufacturer_id`)
REFERENCES `manufacturers` (`manufacturer_id`))Как видно из этого сообщения, MySQL присвоило ограничению имя airplanes_ibfk_1. Что вы не увидели - это создание неуникального индекса на столбце manufacturer_id в таблице airplanes с именем manufacturer_id.
Как и для ограничений других типов, вы можете присвоить собственное имя внешнему ключу. Для этого вы должны перед предложением FOREIGN KEY использовать ключевое слово CONSTRAINT с последующим именем, как в следующем примере:
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
alt_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
engine_count TINYINT NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT chk_wingspan
CHECK (wingspan BETWEEN 10 AND 400),
CONSTRAINT chk_length
-- CHECK (wingspan < (plane_length / 2)));
CHECK (plane_length < (wingspan * 2)),
CONSTRAINT uc_ids UNIQUE (plane_id, alt_id),
CONSTRAINT fk_manufacturer
FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id));Теперь и ограничение, и связанный индекс будут иметь имя fk_manufacturer, а не имена, присвоенные MySQL. И опять не забудьте, что имя должно быть уникально среди данных конкретного типа ограничений, поэтому не пытайтесь создать другой внешний ключ с тем же самым именем, как и индекс с тем же самым именем.
Начала работы с ограничениями в MySQL
Ограничения в MySQL являются наиболее важными инструменами для обеспечения целостности ваших данных. Но важно понимать, как они работают и как их применять для полной реализации их потенциала. Эта статья является введением в различные типы ограничений и способы их добавления в таблицы. Для более глубокого изучения ограничений я советую вам начать со статьи MySQL, посвященной оператору CREATE TABLE. Там вы найдете описание каждого типа ограничений и ссылки для дополнительной информации.
Приложение: подготовка среды MySQL
Для примеров этой статьи я использовал компьютер Mac, на котором был установлен экземпляр MySQL 8.0.29 (Community Server edition) и MySQL Workbench. Я также создал базу данных travel и таблицу manufacturers. Если вы хотите поработать с примерами, то выполните сначала следующий скрипт на экземпляре MySQL:
-- Замечание: если вы прорабатываете примеры,
-- вам сначала необходимо удалить существующую базу данных (или использовать
-- другое имя базы данных)
CREATE DATABASE IF NOT EXISTS travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) );
INSERT INTO manufacturers (manufacturer_id, manufacturer)
VALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'),
(103,'Beechcraft'), (104,'Boeing'); Скрипт создает базу данных travel, добавляет таблицу manufacturers и вставляет в нее несколько строк. Однако имейте в виду, что большинство примеров в этой статье не ссылаются на таблицу manufacturers. Они просто используют оператор CREATE TABLE для определения различных версий таблицы airplanes, чтобы продемонстрировать различные типы ограничений. Таблица manufacturers используется только для объяснения определения ограничения FOREIGN KEY.
Ссылки по теме
1. Удалить таблицу, если она существует
2. Замена ограничений уникальности уникальными индексами
3. Синтаксис MySQL CREATE TABLE для разработчиков T-SQL
4. Столбцы, включенные в уникальный некластеризованный индекс, не являются частью ограничения UNIQUE
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой