Обходження обмежень доменів у PostgreSQL: глибоке занурення

8 Березня 2024 в 19:53 109

PostgreSQL є однією з найпопулярніших систем управління базами даних, що використовується розробниками по всьому світу завдяки своїм розширеним можливостям та гнучкості. Однак, під час роботи з доменами та обмеженнями на рівні бази даних, розробники можуть стикнутися з певними обмеженнями, як-от неможливість використання підзапитів у визначеннях CHECK обмежень. У цій статті ми розглянемо, як обійти ці обмеження, забезпечивши при цьому валідність та цілісність даних.

Спроба використання підзапитів безпосередньо у CHECK обмеженнях домену зазвичай призводить до помилки. Це пов’язано з тим, що підзапити в обмеженнях можуть суттєво уповільнити вставку та оновлення даних, а також ускладнити процес перевірки цілісності даних на рівні бази даних.

Використання тригерів для динамічної перевірки даних

Тригери в PostgreSQL можуть стати в нагоді, коли необхідно виконати додаткову логіку перевірки перед вставкою або оновленням даних у таблиці. Тригер, який активується перед вставкою або оновленням, може виконати підзапит до іншої таблиці для перевірки, чи відповідає значення вказаним критеріям. Це дозволяє динамічно перевіряти валідність даних без необхідності жорсткого кодування обмежень у самій структурі домену.

CREATE OR REPLACE FUNCTION verify_language_code()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM languages WHERE language_id = NEW.value) THEN
        RAISE EXCEPTION 'Invalid language code: %', NEW.value;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_language_code
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION verify_language_code();

CREATE OR REPLACE FUNCTION verify_language_code()

RETURNS TRIGGER AS $$

BEGIN

IF NOT EXISTS (SELECT 1 FROM languages WHERE language_id = NEW.value) THEN

RAISE EXCEPTION 'Invalid language code: %', NEW.value;

END IF;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER check_language_code

BEFORE INSERT OR UPDATE ON your_table

FOR EACH ROW EXECUTE FUNCTION verify_language_code();

Цей підхід дозволяє не тільки перевіряти валідність даних, але й забезпечувати високу гнучкість управління обмеженнями, які можуть змінюватися без необхідності зміни структури бази даних.

Створення віртуальних таблиць для зовнішніх ключів

Ще один ефективний метод обходу обмежень використання підзапитів у обмеженнях CHECK полягає у створенні віртуальної таблиці, яка діє як посередник між основною таблицею та таблицею з дозволеними значеннями. Це дозволяє використовувати зовнішні ключі для валідації даних, надаючи при цьому більшу гнучкість у порівнянні з традиційними методами валідації даних.

Створення такої віртуальної таблиці та налаштування зовнішнього ключа може виглядати так:

CREATE TABLE language_codes (
    code character(2) PRIMARY KEY
);
-- Заповнення таблиці дозволеними кодами мов
INSERT INTO language_codes(code) SELECT language_id FROM languages;
-- Створення таблиці, що використовує код мови, з посиланням на віртуальну таблицю
CREATE TABLE your_table (
    id serial PRIMARY KEY,
    language_code character(2),
    FOREIGN KEY (language_code) REFERENCES language_codes(code)
);

CREATE TABLE language_codes (

code character(2) PRIMARY KEY

);

-- Заповнення таблиці дозволеними кодами мов

INSERT INTO language_codes(code) SELECT language_id FROM languages;

-- Створення таблиці, що використовує код мови, з посиланням на віртуальну таблицю

CREATE TABLE your_table (

id serial PRIMARY KEY,

language_code character(2),

FOREIGN KEY (language_code) REFERENCES language_codes(code)

);

Цей метод вимагає додаткової роботи при налаштуванні та підтримці, але він забезпечує надійне та гнучке рішення для обмеження значень без прямого використання підзапитів у обмеженнях CHECK.