Hanya izinkan nilai tertentu di kolom B berdasarkan nilai kolom A

Saya ingin mengizinkan hanya mengizinkan sejumlah nilai untuk dimasukkan ke dalam kolom A, dan bergantung pada nilai yang dimasukkan, hanya mengizinkan sejumlah nilai tertentu untuk dimasukkan ke dalam kolom B. Misalnya

Misalnya

  • Jika A = 1, B dapat berada di antara 1 dan 9
  • Jika A = 2, B bisa berada di antara 10 DAN 19
  • Jika A = 3, B bisa berada di antara 20 DAN 29

Bagaimana saya bisa mencapainya?

Saya pikir memeriksa batasan adalah tempat terbaik untuk memulai. Batasan sederhana akan memastikan hanya nilai 1-3 yang dapat ditambahkan ke kolom A. Seperti:

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

Kemudian saya menggunakan fungsi skalar untuk menentukan apakah col_b valid, dengan memasukkan nilai dari col_a dan 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

Kemudian tambahkan batasan ke tabel, dan panggil fungsi tersebut sebagai bagian dari pemeriksaan.

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

Namun, penyisipan berikut gagal, mengeluhkan konflik dengan batasan ch_col_b_valid_based_on_a yang ditambahkan.

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

Pernyataan INSERT bertentangan dengan batasan CHECK "ch_col_b_valid_based_on_a". Konflik terjadi pada database "MyDB", tabel "dbo.test".

Apa yang dapat saya lakukan untuk mengatasi hal ini dan mencapai hasil yang disebutkan di atas?

Jika dilihat ke belakang, pendekatan penggunaan fungsi skalar dalam batasan pemeriksaan ini berfungsi persis seperti yang diharapkan.


sql constraints sql-server check-constraints
person Klicker    schedule 18.04.2019    source sumber

Jawaban (2)


arrow_upward
1
arrow_downward

Salah satu metodenya adalah batasan 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)
             )

);

Namun, saya mungkin cenderung membuat tabel AB_valid dengan daftar pasangan yang valid dan menggunakan batasan kunci asing. Dengan begitu, daftar nilai yang valid dapat dipertahankan secara dinamis daripada memerlukan modifikasi pada definisi tabel.

person Gordon Linoff    schedule 18.04.2019
comment
Masalah dengan batasan pemeriksaan adalah bahwa batasan tersebut tidak berfungsi saat memasukkan nilai ke dalam col_a dan col_b. Satu-satunya cara mengatasi hal ini, sejauh yang saya tahu, adalah dengan memasukkan ke col_a dalam satu transaksi, lalu memasukkan ke col_b di transaksi lain, tetapi ini jauh dari ideal. Saya menyukai cita-cita Anda untuk menjalin hubungan FK dengan daftar opsi yang valid. Itu bisa bekerja dengan baik - person Klicker; 19.04.2019
comment
Sebenarnya, komentar saya salah - pendekatan batasan pemeriksaan ini berfungsi, dan contoh dalam komentar asli saya berfungsi. - person Klicker; 19.04.2019

arrow_upward
0
arrow_downward

Anda bisa menggunakan beberapa matematika untuk membuat batasan yang lebih sederhana.

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