Разрешить только определенные значения в столбце B на основе значения столбца A

Я хочу разрешить вставку только определенного количества значений в столбец A и, в зависимости от введенного значения, разрешить вставку только определенного количества значений в столбец B. Например

Например

  • Если A = 1, B может быть между 1 и 9
  • Если A = 2, B может быть между 10 и 19
  • Если A = 3, B может быть между 20 и 29

Как я могу этого добиться?

Я решил, что контрольные ограничения — лучшее место для начала. Простое ограничение гарантирует, что только значения 1-3 могут быть добавлены в столбец A. Такие как:

CREATE TABLE dbo.test (
    col_a INT, 
    col_b INT,
    CONSTRAINT ch_col_a_valid_range CHECK (col_a BETWEEN 1 AND 3)
)
GO

Затем я подумал, что используя скалярную функцию, чтобы определить, является ли col_b допустимым, передав значение из col_a и col_b.

CREATE FUNCTION dbo.value_is_valid (
    @a INT, 
    @b INT
)
RETURNS BIT
AS 
BEGIN
    IF (@a = 1 AND @b BETWEEN 1 AND 9) RETURN 1;
    IF (@a = 2 AND @b BETWEEN 10 AND 19) RETURN 1;
    IF (@a = 3 AND @b BETWEEN 20 AND 29) RETURN 1;
    RETURN 0;
END
GO

Затем добавьте ограничение в таблицу и вызовите функцию как часть проверки.

CREATE TABLE dbo.test (
    col_a INT, 
    col_b INT,
    CONSTRAINT ch_col_a_valid_range CHECK (col_a BETWEEN 1 AND 3),
    CONSTRAINT ch_col_b_valid_based_on_a CHECK(dbo.value_is_valid(col_a, col_b) = 1)
)
GO

Однако следующая вставка завершается ошибкой, жалуясь на конфликт с добавленным ограничением ch_col_b_valid_based_on_a.

INSERT INTO dbo.test (
    col_a, 
    col_b
)
VALUES (1, 9)

Оператор INSERT конфликтовал с ограничением CHECK "ch_col_b_valid_based_on_a". Конфликт произошел в базе данных "MyDB", таблица "dbo.test".

Что я могу сделать, чтобы обойти это и достичь результата, упомянутого выше?

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


sql constraints sql-server check-constraints
person Klicker    schedule 18.04.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Один метод представляет собой ограничение check:

CREATE TABLE dbo.test (
    col_a INT, 
    col_b INT,
    CONSTRAINT ch_col_a_valid_range CHECK (col_a BETWEEN 1 AND 3),
    CONSTRAINT chk_col_a_colb
        CHECK ( (col_a = 1 AND col_b BETWEEN 1 AND 9) OR
                (col_a = 2 AND col_b BETWEEN 10 AND 19) OR
                (col_a = 3 AND col_b BETWEEN 20 AND 29)
             )

);

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

person Gordon Linoff    schedule 18.04.2019
comment
Проблема с проверочным ограничением заключается в том, что оно не работает при вставке значений как в col_a, так и в col_b. Насколько я могу судить, единственным способом обойти это было бы вставить в col_a в одной транзакции, а затем вставить в col_b в другой, но это далеко не идеально. Мне нравится ваш идеал - поддерживать отношения FK с таблицей допустимых опций. Это может хорошо работать - person Klicker; 19.04.2019
comment
На самом деле мой комментарий неверен - этот подход с проверкой ограничения действительно работает, и пример в моем исходном комментарии работает. - person Klicker; 19.04.2019

arrow_upward
0
arrow_downward

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

CREATE TABLE dbo.test (
    col_a INT, 
    col_b INT,
    CONSTRAINT ch_col_a_valid_range CHECK (col_a BETWEEN 1 AND 3),
    CONSTRAINT ch_col_b_valid_based_on_a CHECK(col_b/10 + 1 = col_a)
);
person Luis Cazares    schedule 18.04.2019