В этом руководстве вы узнаете, как использовать ограничение SQL Server CHECK для обеспечения целостности домена.
Введение в ограничение SQL Server CHECK
Ограничение CHECK позволяет указать значения в столбце, которые должны удовлетворять логическому выражению.
Например, чтобы потребовать положительные цены за единицу товара, можно использовать:
CREATE SCHEMA test;
GO
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2) CHECK(unit_price > 0)
);
Как вы видите, определение ограничения CHECK следует после типа данных. Оно состоит из ключевого слова CHECK, за которым следует логическое выражение в скобках:
CHECK(unit_price > 0)
Вы также можете присвоить ограничению отдельное имя, используя ключевое слово CONSTRAINT следующим образом:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
);
Явные имена помогают классифицировать сообщения об ошибках и позволяют ссылаться на ограничения, когда вы хотите их изменить.
Если вы не укажете имя ограничения таким образом, SQL Server автоматически сгенерирует его для вас.
См. следующее вставное заявление:
INSERT INTO test.products(product_name, unit_price)
VALUES('Awesome Free Bike', 0);
SQL Server выдал следующую ошибку:
The INSERT statement conflicted with the CHECK constraint "positive_price". The conflict occurred in database "BikeStores", table "test.products", column 'unit_price'.
Ошибка произошла из-за того, что цена за единицу товара не больше нуля, как указано в ограничении CHECK.
Следующий оператор работает нормально, поскольку логическое выражение, определенное в ограничении CHECK, оценивается как TRUE:
INSERT INTO test.products(product_name, unit_price)
VALUES('Awesome Bike', 599);
Ограничение CHECK SQL Server и NULL
Ограничения CHECK отклоняют значения, которые приводят к тому, что логическое выражение принимает значение FALSE.
Поскольку NULL оценивается как UNKNOWN, его можно использовать в выражении для обхода ограничения.
Например, вы можете вставить продукт, цена за единицу которого равна NULL, как показано в следующем запросе:
INSERT INTO test.products(product_name, unit_price)
VALUES('Another Awesome Bike', NULL);
Вот что получилось:
(1 row affected)
SQL Server вставил значение NULL в столбец unit_price и не вернул ошибку.
Чтобы исправить это, необходимо использовать ограничение NOT NULL для столбца unit_price.
CHECK ограничение, относящееся к нескольким столбцам
Ограничение CHECK может ссылаться на несколько столбцов. Например, вы храните обычную и сниженную цену в таблице test.products и хотите убедиться, что сниженная цена всегда ниже обычной цены:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2) CHECK(unit_price > 0),
discounted_price DEC(10,2) CHECK(discounted_price > 0),
CHECK(discounted_price < unit_price)
);
Первые два ограничения для unit_price и discounted_price должны показаться вам знакомыми.
Третье ограничение использует новый синтаксис, который не привязан к определенному столбцу. Вместо этого он отображается как отдельная строка в списке столбцов, разделенных запятыми.
Первые два ограничения столбцов являются ограничениями столбцов, тогда как третье ограничение является ограничением таблицы.
Обратите внимание, что вы можете записать ограничения столбцов как ограничения таблиц. Однако вы не можете записать ограничения таблиц как ограничения столбцов. Например, вы можете переписать приведенное выше выражение следующим образом:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2),
discounted_price DEC(10,2),
CHECK(unit_price > 0),
CHECK(discounted_price > 0),
CHECK(discounted_price > unit_price)
);
или даже:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2),
discounted_price DEC(10,2),
CHECK(unit_price > 0),
CHECK(discounted_price > 0 AND discounted_price > unit_price)
);
Вы также можете присвоить имя ограничению таблицы таким же образом, как и ограничению столбца:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2),
discounted_price DEC(10,2),
CHECK(unit_price > 0),
CHECK(discounted_price > 0),
CONSTRAINT valid_prices CHECK(discounted_price > unit_price)
);
Добавить ограничения CHECK к существующей таблице
Чтобы добавить ограничение CHECK к существующей таблице, используйте оператор ALTER TABLE ADD CONSTRAINT.
Предположим, у вас есть следующая таблица test.products:
CREATE TABLE test.products(
product_id INT IDENTITY PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
unit_price DEC(10,2) NOT NULL
);
Чтобы добавить ограничение CHECK в таблицу test.products, используйте следующий оператор:
ALTER TABLE test.products ADD CONSTRAINT positive_price CHECK(unit_price > 0);
Чтобы добавить новый столбец с ограничением CHECK, используйте следующий оператор:
ALTER TABLE test.products ADD discounted_price DEC(10,2) CHECK(discounted_price > 0);
Чтобы добавить ограничение CHECK с именем valid_price, используйте следующий оператор:
ALTER TABLE test.products ADD CONSTRAINT valid_price CHECK(unit_price > discounted_price);
Удалить ограничения CHECK
Чтобы удалить ограничение CHECK, используйте оператор ALTER TABLE DROP CONSTRAINT:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Если вы назначаете ограничению CHECK определенное имя, вы можете ссылаться на это имя в операторе.
Однако, если вы не назначили ограничению CHECK конкретное имя, вам необходимо найти его с помощью следующего оператора:
EXEC sp_help 'table_name';
Например:
EXEC sp_help 'test.products';
Это утверждение выдает много информации, включая имена ограничений:
Следующий оператор снимает ограничение positive_price:
ALTER TABLE test.products DROP CONSTRAINT positive_price;
Отключить ограничения CHECK для вставки или обновления
Чтобы отключить ограничение CHECK для вставки или обновления, используйте следующий оператор:
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name;
Следующий оператор отключает ограничение valid_price:
ALTER TABLE test.products NO CHECK CONSTRAINT valid_price;
В этом руководстве вы узнали, как использовать ограничение SQL Server CHECK для ограничения значений, которые можно вставлять или обновлять в одном или нескольких столбцах таблицы.
