Как задать ограничения в sql
Перейти к содержимому

Как задать ограничения в sql

  • автор:

CHECK SQL

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

Ограничение CHECK

Ограничение CHECK состоит из ключевого слова CHECK, сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля, которое могло бы сделать этот предикат неверным будет отклонена.

SQL CHECK в CREATE TABLE

CHECK SQL. Пример 1

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char (30) NOT NULL UNIQUE, Adres char (50), Ball decimal CHECK ( Ball > 0));

Ограничение CHECK SQL. Пример 2

Можно использовать ограничение CHECK чтобы защитить от ввода в поле определенных значений, и таким образом предотвратить ошибку.

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char (30) NOT NULL UNIQUE, Adres char (50), Ball decimal CHECK ( Ball > 0), Form_ob char(10) CHECK (Form_ob IN (‘Дневная’, ‘Заочная’, ‘Вечерняя’));

CHECK SQL. Пример 3

Можно также использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях когда необходимо включить более одного поля строки в условие.

CREATE TABLE Student (Kod_stud integer NOT NULL PRIMARY KEY, Fam char (30) NOT NULL UNIQUE, Adres char (50), Ball decimal, Form_ob char(10), CHECK (Ball > 50 OR Form_ob = ‘Дневная’));

Если строка вставляется в таблицу и не предоставляются значения для каждого поля, SQL должен иметь значения по умолчанию для заполнения ими значений полей, не заданных явно в команде; в противном случае команда вставки должна быть отвергнута. Наиболее распространенным значением по умолчанию является значение NULL. Это значение является значением по умолчанию для любого столбца, ели для него не указано ограничение NOT NULL, либо не указано значение, присвоенное по умолчанию. Для назначения иного значения по умолчанию используют ограничение DEFAULT.

CREATE TABLE Student

( Kod_stud integer NOT NULL PRIMARY KEY,

Fam char (30) NOT NULL UNIQUE,

Ball decimal CHECK (Ball > 0),

Stip decimal DEFAULT =300);

SQL CHECK в ALTER TABLE

Для создания ограничения для столбца «Year» в таблице «Books», когда таблица уже создана, необходимо использовать следующий SQL

SQL-Ex blog

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.
  • Выражение не может включать подзапросы, параметры, переменные, хранимые функции или загружаемые функции. Однако оно может включать операторы, литералы или встроенные функции (как детерминированные, так и недерминированные).
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 сообщает о нарушении ограничения.

  • Выражение не может ссылаться на столбец, определенный с AUTO_INCREMENT, или на столбец в другой таблице.
  • Выражение не может включать хранимые функции, загружаемые функции, процедурные или функциональные параметры, переменные или подзапросы. Однако выражение может включать литералы, операторы или детерминированные встроенные функции.
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.

Sysadminium

Из статьи вы узнаете про ограничение целостности SQL. А также, на примере PostgreSQL, я покажу, как эти ограничения создаются.

Оглавление скрыть

Теория

В прошлой статье: «Базовые команды SQL» я показывал как создавать таблицы в СУБД PostgreSQL. Первичный ключ уже накладывает некоторые ограничения целостности. Например, все значения в поле, которое является первичным ключом должны быть уникальными.

Ограничение целостности SQL, это определённое требование для полей в таблице SQL. За соблюдение этих требований отвечает сама СУБД. Например:

  • Тип данных накладывает некоторое ограничение. Нельзя в поле с типом «integer» вставить текстовую строку. Но для некоторых типов можно ещё ужесточить правила:
    • для типа «char» и «varchar» можно указать максимальную длину строки. Например, в поле «vaechar(4)» нельзя будет вставить строку из пяти символов.
    • записи должны быть уникальны (unique);
    • записи не могут быть пустыми (not null).

    В документации об ограничениях можете почитать тут.

    В этой статье будем говорить о «check» проверках, так как о первичном ключе и типах данных я уже рассказывал. Также покажу как создаются ограничения unique и not null.

    Создание таблицы с ограничениями целостности

    Создадим новую базу данных и подключимся к ней:

    postgres=# CREATE DATABASE check_constraint; CREATE DATABASE postgres=# \c check_constraint You are now connected to database "check_constraint" as user "postgres".

    Создадим таблицу staff (сотрудники):

    Название Описание Тип данных Ограничения
    ( NOT NULL, UNIQUE )
    Ограничения
    (CHECK)
    number номер serial NOT NULL, UNIQUE
    fullname фио varchar (50) NOT NULL
    sex пол char (1) NOT NULL Может быть только «М» или «Ж»
    age возраст integer NOT NULL От 18 до 100 (я думаю больше 100 лет сотруднику не может быть)
    experience стаж integer NOT NULL Стаж должен быть меньше возраста

    Для создания подойдёт следующий запрос:

    # CREATE TABLE staff (number serial NOT NULL UNIQUE, fullname varchar(50) NOT NULL, sex char(1) NOT NULL, CHECK ( sex IN ('М', 'Ж')), age integer NOT NULL, CHECK ( age > 18 AND age < 100), experience integer NOT NULL, CHECK ( experience < age) ); CREATE TABLE

    И посмотрим на то, что у нас получилось:

    # \d staff Table "public.staff" Column | Type | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------------------------------------- number | integer | | not null | nextval('staff_number_seq'::regclass) fullname | character varying(50) | | not null | sex | character(1) | | not null | age | integer | | not null | experience | integer | | not null | Indexes: "staff_number_key" UNIQUE CONSTRAINT, btree (number) Check constraints: "staff_age_check" CHECK (age > 18 AND age < 100) "staff_check" CHECK (experience < age) "staff_sex_check" CHECK (sex = ANY (ARRAY['М'::bpchar, 'Ж'::bpchar]))

    Попробуем создать 15 летнего сотрудника:

    # INSERT INTO staff (fullname, sex, age, experience) VALUES ('Иванов Иван Алексеевич', 'М', 15, 10); ERROR: new row for relation "staff" violates check constraint "staff_age_check" DETAIL: Failing row contains (1, Иванов Иван Алексеевич, М, 15, 10).

    Как видно из ошибки, сработало ограничение staff_age_check.

    А 18 летний создастся нормально:

    # INSERT INTO staff (fullname, sex, age, experience) VALUES ('Иванов Иван Алексеевич', 'М', 20, 10); INSERT 0 1

    А что будет, если ошибёмся с полом:

    # INSERT INTO staff (fullname, sex, age, experience) VALUES ('Донченко Иван Андреевич', 'К', 25, 8); ERROR: new row for relation "staff" violates check constraint "staff_sex_check" DETAIL: Failing row contains (3, Донченко Иван Андреевич, К, 25, 8).

    Здесь уже сработало ограничение staff_sex_check.

    Если мы что-то забудем указать, то сработает ограничение NOT NULL:

    # INSERT INTO staff (fullname, sex, age) VALUES ('Донченко Иван Андреевич', 'Ж', 25); ERROR: null value in column "experience" of relation "staff" violates not-null constraint DETAIL: Failing row contains (4, Донченко Иван Андреевич, Ж, 25, null).

    И наконец введем второго пользователя в базу:

    # INSERT INTO staff (fullname, sex, age, experience) VALUES ('Донченко Иван Андреевич', 'М', 25, 8); INSERT 0 1

    А теперь посмотрим на табличку:

    check_constraint=# SELECT * FROM staff; number | fullname | sex | age | experience --------+-------------------------+-----+-----+------------ 2 | Иванов Иван Алексеевич | М | 20 | 10 5 | Донченко Иван Андреевич | М | 25 | 8 (2 rows)

    Как вы могли заметить последовательность постоянно растёт, даже если мы ошибаемся при вставке. Поэтому у нас в поле number вначале 2, а затем 5.

    Создание ограничения для существующей таблицы

    NOT NULL

    Добавить или удалить ограничение целостности NOT NULL можно с помощью ALTER TABLE таким образом:

    # ALTER TABLE staff ALTER COLUMN number DROP NOT NULL; # ALTER TABLE staff ALTER COLUMN number SET NOT NULL;

    UNIQUE

    Ограничения целостности UNIQUE удаляются с помощью DROP CONSTRAINT, а добавляется с помощью ADD CONSTRAINT:

    # ALTER TABLE staff DROP CONSTRAINT staff_number_key; # ALTER TABLE staff ADD CONSTRAINT staff_number_key UNIQUE (number);

    Кстати, при удалении ограничения целостности UNIQUE удаляется и индекс.

    CHECK

    Ограничения целостности CHECK создаются и удаляются подобным способом:

    # ALTER TABLE staff DROP CONSTRAINT staff_age_check; # ALTER TABLE staff ADD CONSTRAINT staff_age_check CHECK (age > 18 AND age < 100);

    При этом названия ограничения (staff_number_key, staff_age_check и другие) вы придумываете сами.

    Ограничение целостности в SQL

    Имя статьи
    Ограничение целостности в SQL

    Я покажу вам на примере PostgreSQL, что такое ограничения целостности SQL. А также, как указать ограничения целостности для полей таблицы при её создании или для уже существующей таблицы

    1.2.4. Ограничения значений полей в SQL

    Задача любого администратора и программиста, совместно обеспечить целостность и корректность данных. Например, если пользователь не укажет в одной из строк в списке жителей дома фамилию, то корректность данных портиться. Человека очень сложно найти, если не известна его фамилия.

    А что если в нашем списке добавить поле для хранения пола. Что можно записать в такое поле? Конечно же, достаточно одной буквы «М» или «Ж», другого пола пока не придумали :). А что если пользователь случайно введет букву «Ь»? Это не смешно, а вполне реальная ситуация. Посмотрите на клавиатуру и увидите, что на ней две буквы «М», просто одна английская, а другая русская. На одной клавише с английской буквой «М» находиться «Ь». Начинающие пользователи нередко путаются и если не проверят результат своего ввода, то корректность данных нарушается.

    Нередко бывают ситуации, когда вместо русской буквы М пользователь вводить английскую. В этом случае в базе получается три пола: М русская, М английская и Ж. Конечно, программно эта ситуация решается достаточно просто, достаточно только при определении мужчин выбирать из базы все записи с буквой М в любой раскладке, но качество такой базы будет далеко от идеала.

    Все эти проблемы известны уже давно и для их решения придумали ограничения – универсальное средство, с помощью которого можно задать правила, которым должны удовлетворять данные, для возможности записи в поле. Если записываемое значение не удовлетворяет ограничениям, назначенным полю, то запись завершается ошибкой. Таким образом, сервер сам будет контролировать целостность данных, вводимых пользователем.

    Самое простейшее ограничение – это разрешение или запрещение введения нулевых значений (NULL). Тут нужно отметить, что NULL и пустая строка это совершенно разные вещи. Об этом мы еще поговорим во второй главе. Итак, для таблицы, в которой хранятся ФИО, пол и дата рождения не нулевыми должны быть фамилия, имя и пол. Эти параметры есть у каждого жителя нашей страны, поэтому в базе данных обязательно должно быть что-то указано, иначе будет сгенерирована ошибка и сервер не позволит записать такую строку.

    А вот с отчеством и датой рождения бывают проблемы, особенно у тех, кто родился во время второй мировой войны. В те времена сложно было определить дату рождения сирот, а в детских домах после войны детям давали только имя и отчество, поэтому отсутствие этих параметров не будет считаться ошибкой.

    Следующий пример, показывает, как создаются поля, в которых запрещаются пустые значения:

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, dDate datetime DEFAULT (getdate()) NULL, vcName varchar(50) NOT NULL )

    В данном случае указано, что поле id и vcName не могут содержать нулевые значения. Пользователь обязательно должен указать хоть что-нибудь, иначе изменения не будут приняты, и сервер сгенерирует ошибку. А вот поле dDate может содержать нулевое значение NULL.

    Что не корректно в этом примере? Хотя нет, некорректного ничего нет, но на первый взгляд есть одна глупость. Для поля dDate мы разрешили нулевое значение, но это бессмысленно. Дело в том, что у этого поля есть значение по умолчанию, а значит, если в поле будет попытка записать нулевое значение, оно будет автоматически заменено на значение по умолчанию. Получается, что указывать NULL бессмысленно, если для поля установлено значение по умолчанию и нулевого значения просто не будет.

    Но теперь вспомним, когда генерируется значение по умолчанию – только при вставке строки, а не при обновлении. Вы уловили ход мысли? Если полю назначено значение по умолчанию и при этом запрещено нулевое значение, то ноль нельзя будет установить даже у существующей строки. Именно поэтому я сказал, что глупость есть только на первый взгляд. Никогда не забывайте, когда устанавливается значение по умолчанию.

    Ограничения NULL и NOT NULL являются не жесткими и некоторые специалисты даже не относят их к ограничениям, хотя, по своей сути они такими являются. Более жесткие ограничения задаются оператором CHECK. Рассмотрим этот оператор на примере. Допустим, что нам нужно создать список хозяев квартир. Для этого нам понадобиться ключевое поле ("id"), имя хозяина ("vcName") и номер квартиры ("iApartment"). Для квартиры вполне логичным будет сделать ограничение ввода от 1 до 1000. Квартир с отрицательными номерами и нулевыми значениями не бывает (по крайней мере, в моем городе), да и более 1000 у нас не бывает, я даже не видел квартиры, с номером более 400. Поэтому логично будет запретить ввод явно некорректных данных с помощью ограничения. Итак, посмотрим на следующий запрос создания таблицы:

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcName varchar(50) NOT NULL, iApartment int CHECK (iApartment>0 and iApartment<1000) )

    После указания имени и типа поля "iApartment" указано ключевое слово CHECK, после которого в круглых скобках необходимо указать ограничения для данного поля. В нашем примере в качестве ограничения выступает:

    iApartment>0 and iApartment
    

    Это значит, что значение в поле iApartment должно быть более 0 и в то же время меньше 1000. Объединение происходит с помощью ключевого слова and, то есть «и». Это значит, что оба условия должны быть выполнены. Бывают случаи, когда необходимо, чтобы хотя бы одно из условий было выполнено, тогда их объединяют оператором or. Например, нужно чтобы в поле заносилось значение или меньше нуля, или больше 1000. Значения в промежутке 1 – 1000 указывать нельзя. В этом случае, сравнение выглядело бы:

    iApartment1000

    Попробуйте вставить в таблицу квартир данные. Для этого давайте выполним три запроса. Первый будет вставлять корректные данные:

    INSERT INTO TestTable(vcName, iApartment) VALUES('Корректная запись', 10)

    Не будем вдаваться в подробности работы запроса, это нам предстоит узнать во 2-й главе. Операция пройдет успешно и никаких ошибок не появиться. Следующий запрос будет пытаться вставить запись с номером квартиры 10000, что не соответствует ограничению:

    INSERT INTO TestTable(vcName, iApartment) VALUES('Ошибочная запись', 10000)

    В ответ на это, перед нами должно появиться окно с сообщением об ошибке:

    INSERT statement conflicted with COLUMN CHECK constraint 'CK__TestTable__iApar__22AA2996'. The conflict occurred in database 'TestDatabase', table 'TestTable', column 'iApartment'.

    The statement has been terminated.

    Оператор INSERT конфликтует с COLUMN CHECK ограничением 'CK__TestTable__iApar__22AA2996'. Конфликт обнаружен в базе данных 'TestDatabase', таблица 'TestTable', колонка 'iApartment'.

    Выполнение было прервано.

    Эта ошибка может быть отображена и в окне результата выполнения запроса, а не в отдельном окне диалога. Смысл сообщения в том, что произошел конфликт с ограничением данных.

    Если с помощью Enterprise Manager открыть свойства таблицы TestDatabase, то в появившемся окне на закладке Check Constraints в выпадающем списке Selected constraint выбрать ограничение, которое мы увидели в сообщении (CK__TestTable__iApar__22AA2996), а в окне Constraint expression появится описание ограничения.

    Напоследок, выполним запрос, который покажет содержимое таблицы:

    SELECT * FROM TestTable

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

    Самое неприятное в этом примере – это имя ограничения, которое было назначено автоматически - CK__TestTable__iApar__22AA2996. С такими именами очень неудобно работать, намного приятнее видеть читаемые имена и со смыслом. Чтобы задать имя ограничению, необходимо использовать конструкцию:

    CONSTRAINT имя CHECK (ограничения)

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

    Такие ограничения можно описывать внутри описания поля (как параметры), а можно вынести все в один блок, после описания полей. В этом случае, ограничение должно выглядеть следующим образом:

    CONSTRAINT Имя ограничения CHECK (Ограничение)

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

    Давайте удалим старую таблицу, а создадим новую с еще одним полем – dDate типа datetime. Для этого поля поставим ограничение, которое позволит вводить только даты, которые меньше текущей:

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcName varchar(50) NOT NULL, iApartment int, dDate datetime, CONSTRAINT check_iApartment CHECK (iApartment>0 and iApartment<1000), CONSTRAINT check_dDate CHECK (dDate
    

    Как видите, в этом примере ограничения описываются через запятую, так же, как и поля. После ключевого слова CONSTRAINT указывается имя ограничения. Из своей практики хочу порекомендовать называть их в виде check_имя, где имя – имя поля, которое проверяется ограничением. После этого указывается само ограничение точно также как и в предыдущем примере.

    Обратите внимание, что для ограничения ввода в поле "dDate" мы использовали функцию (getdate). Как и при описании значений по умолчанию, в ограничениях также могут использоваться функции.

    При создании ограничения, можно использовать многие операторы сравнения языка SQL. Например, в SQL есть очень удобный оператор IN. С его помощью можно задать возможные значения для поля, которые оно может принимать. Например, вам нужно в таблице ограничить ввода данных в поле содержащую такую информацию как пол человека. В этом случае, можно разрешить ввод букв "М" и "Ж" следующим образом:

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcName varchar(50) NOT NULL, cPol char(1), CONSTRAINT check_cPol CHECK (cPol IN ('М', 'Ж')) )

    В данном случае, ограничение выглядит следующим образом: cPol IN ('М', 'Ж'). Оператор IN означает, что поле может принимать любые значения, перечисленные в круглых скобках. В нашем случае указано две строки 'М' и 'Ж'. Другие буквы вносить в поле нельзя. Конечно же, то же самое можно было бы написать и следующим образом:

    (cPol = 'М' or cPol = 'Ж')

    Но это не очень удобно, особенно, если очень много возможных вариантов. Ограничение окажется не очень удобным для чтения. Оператор IN в этом случае намного красивее и читабельнее.

    Очень мощных возможностей можно добиться, используя в ограничении оператора LIKE. Например, вы хотите, чтобы поле для хранения телефонного номера содержало номер в формате (ХХХ) ХХХ-ХХ-ХХ, где Х – это любая цифра. Для реализации примера такого ограничения создадим новую таблицу с полем "vcPhonenumber":

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcPhonenumber varchar(50) NOT NULL CONSTRAINT check_vcPhonenumber CHECK (vcPhonenumber LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]') )

    В данном случае, ограничение с именем check_vcPhonenumber при проверки использует оператор LIKE. С этим оператором мы познакомимся ближе в следующей главе, когда будем изучать работу с оператором SELECT, но я решил вставить этот пример, чтобы вы заранее знали о его мощи. Если классический знак равенства производит жесткое сравнение, то LIKE позволяет сравнивать строки с определенным шаблоном. В данном случае шаблоном является:

    В квадратных скобках мы указываем возможный диапазон символа. В данном случае, диапазон от 0 до 9. Если заменить все [0-9] на Х, то мы получим искомый шаблон (ХХХ) ХХХ-ХХ-ХХ. Следующий пример запроса добавляет в таблицу номер телефона (085) 880-08-00:

    INSERT INTO TestTable(vcPhonenumber) VALUES('(085) 880-08-00')

    Операция пройдет успешно. Но если убрать пробел, или любой из символов ( или -, или заменить какую-либо цифру буквой, то операция завершиться неудачей:

    INSERT INTO TestTable(vcPhonenumber) VALUES('(095)8800800')

    В ответ на это мы увидим сообщение:

    INSERT statement conflicted with COLUMN CHECK constraint 'check_vcPhonenumber'. The conflict occurred in database 'TestDatabase', table 'TestTable', column 'vcPhonenumber'.

    The statement has been terminated.

    Смысл сообщения в следующем: «Оператор INSERT конфликтует с ограничением COLUMN CHECK с именем check_vcPhonenumber. Конфликт появился в базе данных TestDatabase, таблице TestTable, колонке vcPhoneNumber. Выполнение оператора прервано».

    Следующий пример задает шаблон для ввода даты:

    CREATE TABLE TestTable ( id int DEFAULT 1 NOT NULL, vcDate varchar(50) NOT NULL CONSTRAINT check_vcDate CHECK (vcDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]') )

    Такой шаблон бывает удобным, если дату нужно хранить в текстовом виде. Да, есть специализированный тип datetime, но все же, бывают разные задачи и может пригодиться текстовый хранение. Шаблон, позволяет ограничить действия пользователя, дабы уменьшить вероятность ошибки, но не решает задачу полностью. Давайте посмотрим на него по частям.

    Первая часть (до точки) определяет число месяца. Первая цифра числа может принимать значения от 0 до 3, а вторая цифра от 0 до 9. Это значит, что пользователь может ввести числа от 01 до 39. Числа от 32 до 39 заранее являются не корректными. При указании месяца пользователь также может указать неверное значение, потому что это значение мы ограничили значениями от 01 до 19.

    Бывает необходимость, чтобы определенное поле или сочетание полей были в базе данных уникальными. Это можно проверять с помощью запросов самостоятельно, а можно доверить проверку серверу. Для создания ограничения уникальности используется ограничение UNIQUE, которое выглядит следующим образом:

    CONSTRAINT Имя ограничения UNIQUE (Поле или список полей)

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

    CREATE TABLE Names ( idName int, vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT cn_unique UNIQUE (idName) )

    Недавно мне пришлось разрабатывать базу данных, в которой формировалась отчетность из списка клиентов организации. Когда я начал работать с базой данных, то оказалось, что в базе есть дубликаты клиентов. В старой базе данных не было проверок на двойственность записей, только не эффективные проверки в пользовательской программе.

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

    CREATE TABLE Names ( idName int , vcName varchar(50), vcLastName varchar(50), vcSurName varchar(50), dBirthDay datetime, CONSTRAINT cn_unique UNIQUE (vcName, vcLastName, vcSurName, dBirthDay) )

    В данном примере создается таблица из полей: идентификатора, имени, фамилии, отчества и даты рождения. После этого, создается ограничение, при этом оно имеет тип не CHECK, а UNIQUE. В скобках указываются поля, которые должны быть уникальными. В данном примере, я перечислил поля фамилия, имя, отчество и дата рождения. Таким образом, база данных не позволит создать дубликат записи.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *