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

Метод 1: использование подзапросов
Один из способов ссылки на другие таблицы в проверочных ограничениях — использование подзапросов. Давайте рассмотрим пример, когда у нас есть две таблицы: «Сотрудники» и «Отделы», и мы хотим убедиться, что отдел сотрудника существует, прежде чем вставлять новую запись.

CREATE TABLE Departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    CONSTRAINT chk_department_exists CHECK (department_id IN (SELECT id FROM Departments))
);

В этом примере проверочное ограничение chk_department_existsссылается на таблицу Departmentsс помощью подзапроса.

Метод 2: использование объединений
Другой подход заключается в использовании объединений таблиц внутри проверочного ограничения. Давайте рассмотрим сценарий, в котором у нас есть таблица «Студенты» и таблица «Курсы», и мы хотим гарантировать, что студент может записаться только на существующий курс.

CREATE TABLE Courses (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    course_id INT,
    CONSTRAINT chk_course_exists CHECK (course_id IN (SELECT Courses.id FROM Courses JOIN Students ON Courses.id = Students.course_id))
);

В этом примере проверочное ограничение chk_course_existsпроверяет, существует ли course_idв таблице Courses, соединяя его с Studentsтаблица.

Метод 3: использование пользовательских функций
Если подход с подзапросом или соединением становится сложным или недостаточным для ваших требований, вы можете создать пользовательские функции для инкапсуляции логики. Давайте рассмотрим сценарий, в котором у нас есть таблицы «Продажи» и «Товары», и мы хотим убедиться, что цена продукта находится в определенном диапазоне.

CREATE TABLE Products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2)
);
CREATE TABLE Sales (
    id INT PRIMARY KEY,
    product_id INT,
    sale_price DECIMAL(10,2),
    CONSTRAINT chk_valid_price CHECK (dbo.IsValidPrice(product_id, sale_price) = 1)
);
CREATE FUNCTION dbo.IsValidPrice (@product_id INT, @sale_price DECIMAL(10,2))
RETURNS INT
AS BEGIN
    DECLARE @min_price DECIMAL(10,2), @max_price DECIMAL(10,2)
    SELECT @min_price = min_price, @max_price = max_price FROM Products WHERE id = @product_id
    IF (@sale_price >= @min_price AND @sale_price <= @max_price)
        RETURN 1
    ELSE
        RETURN 0
END;

В этом примере проверочное ограничение chk_valid_priceиспользует функцию dbo.IsValidPriceдля проверки диапазона цен для конкретного продукта.

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