Məlumatların bütövlüyünün yoxlanılması

Bu gün biz araşdırmağa başlayacağıq ki, xarici açarlar məlumatların bütövlüyünə necə nəzarət etməyə və uyğunsuz və ya səhv məlumatlarla bağlı tipik problemlərin qarşısını almağa kömək edir.

İlk növbədə, "məlumatların bütövlüyü" dedikdə nəyi nəzərdə tutduğumuzu başa düşək. Təsəvvür elə ki, səndə sifarişlər üçün bir cədvəl (orders) və müştərilər üçün bir cədvəl (customers) var. Əgər sifarişdə elə bir müştəri varsa ki, o, müştərilər cədvəlində yoxdur, bu, bütövlüyün pozulmasıdır. Vacibdir ki, əlaqəli cədvəllərdəki bütün məlumatlar məntiqi baxımdan uyğun olsun.

Məlumatların bütövlüyü deməkdir ki:

• Heç bir "boş" istinad yoxdur: əgər başqa bir cədvəldə nəyəsə istinad edirik, o "nə isə" həmişə mövcud olmalıdır.
• Dəyişiklik səhvlərinə davamlılıq: əgər cədvəldən başqa qeydlərin istinad etdiyi bir dəyəri silirik, database bizi xəbərdar etməlidir və ya bu vəziyyəti düzgün həll etməlidir.

Məhz buna görə PostgreSQL-də xarici açarlardan istifadə olunur.

Xarici açarlar məlumatların bütövlüyünü necə təmin edir?

Cədvəldə xarici açar yaradanda, PostgreSQL avtomatik olaraq yoxlayır:

1. Valideyn cədvəldə məlumatın olması. Qeyd əlavə etməzdən və ya yeniləmədən əvvəl, PostgreSQL yoxlayır ki, göstərilən xarici açar əlaqəli cədvəldə mövcuddurmu.
2. Məlumatların silinməsi və ya dəyişdirilməsi. Valideyn cədvəldə qeyd silinməzdən və ya dəyişdirilməzdən əvvəl, PostgreSQL yoxlayır ki, ona istinad edən qeydlər övlad cədvəldə varmı.

Xarici açarlar bir növ "mühafizəçi" kimidir. Onlar səhv məlumatların keçməsinə imkan vermir və cədvəllərin müəyyən olunmuş qaydalara uyğun işləməsinə zəmanət verir.

Nümunə: tələbə və kurs cədvəllərində məlumatların bütövlüyü

Tutaq ki, bizdə iki cədvəl var — students və courses. Hər bir tələbə bir neçə kursa yazıla bilər. Bu əlaqəni göstərmək üçün enrollments cədvəlindən istifadə edirik. İndi baxaq, kimsə tələbəni mövcud olmayan kursa yazmaq istəsə nə baş verər.

Addım 1. Üç əlaqəli cədvəl yaradaq:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

Burada:

• enrollments cədvəlində biz açıq şəkildə student_id və course_id xarici açarlarını göstərmişik, hansılar ki, students və courses cədvəllərinin əsas açarlarına istinad edir.

Məlumatların bütövlüyünün tipik yoxlanışları

1. Məlumat əlavə edəndə yoxlama

Əgər biz enrollments cədvəlinə mövcud olmayan student_id və ya course_id ilə qeyd əlavə etməyə çalışsaq, səhv baş verəcək.

Nümunə:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Səhv! ID-si 999 olan tələbə mövcud deyil.

Səhv mesajı:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Məlumat silinəndə yoxlama

Gəlin valideyn cədvəldən istinad olunan qeydi silməyə çalışaq.

Nümunə:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Uğurlu əlavə

DELETE FROM students WHERE student_id = 1; -- Səhv, çünki tələbə hələ də kursa yazılıb!

Səhv mesajı:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Belə hallarda qeydləri düzgün silmək üçün CASCADE, SET NULL və ya RESTRICT strategiyalarından istifadə edirik, bunlar əvvəl danışılmışdı.

Xarici açarlarla bütövlüyün yoxlanması üçün nümunələr

Nümunə 1: Səhv məlumatlardan avtomatik qorunma

Xarici açarlar vasitəsilə PostgreSQL "mövcud olmayan" məlumatların əlavə olunmasının qarşısını avtomatik alır:

-- Mövcud olmayan tələbələri kursa əlavə etməyə çalışaq:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Səhv! ID-si 42 olan tələbə mövcud deyil.

Bu, tələbənin students cədvəlində yoxdursa, kursa yazıla bilməyəcəyinə zəmanət verir.

Nümunə 2: ON DELETE CASCADE ilə məlumatların silinməsi

Əgər xarici açar ON DELETE CASCADE ilə qurulubsa, valideyn cədvəldə qeyd silinəndə, əlaqəli məlumatlar övlad cədvəldə də silinəcək.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Köhnə xarici açarı silirik

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- İndi enrollments cədvəlindən də qeydlər silinəcək

Nümunə 3: ON UPDATE ilə dəyişikliklərin işlənməsi

Əgər xarici açar ON UPDATE CASCADE ilə qurulubsa, valideyn cədvəldə dəyər dəyişəndə PostgreSQL avtomatik olaraq övlad cədvəldə də məlumatı yeniləyəcək.

-- Xarici açarı elə qururuq ki, valideyn açar dəyişəndə övlad cədvəldə də avtomatik dəyişsin:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Tələbənin identifikatorunu dəyişirik:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- İndi enrollments cədvəlində də student_id 10 olacaq.

Məlumatların bütövlüyünün test edilməsi

Həmişə faydalıdır ki, xarici açar ayarlarının müxtəlif ssenarilərdə necə işlədiyini test edəsən:

1. Səhv student_id və ya course_id ilə məlumat əlavə etməyə çalış.
2. students cədvəlindən məlumat sil və enrollments cədvəlinin necə davrandığını yoxla.
3. students cədvəlində məlumatı dəyiş və əlaqəli qeydlərin yenilənib-yenilənmədiyinə bax.

Xarici açarlarla işləyərkən xüsusiyyətlər

Bəzən çaşdırıcı vəziyyətlər ola bilər:

• İndeksin olmaması. Əgər valideyn cədvəl (students, məsələn) istinad olunan sütunda indeks yoxdursa, PostgreSQL "çalışa" bilər ki, daha yavaş işləsin. Ona görə də, valideyn cədvəldə əsas açar həmişə indeks olmalıdır.
• Dairəvi istinadlar. Əgər iki cədvəl bir-birinə istinad edirsə, məlumat əlavə edəndə çətinliklər yarana bilər. Belə hallarda dizayna daha diqqətlə yanaşmaq lazımdır.
• Bütün qeydlərin silinməsi. Əgər bütün qeydləri kaskadlı silmək lazımdırsa, övlad cədvəldəki məlumatların xarakterini nəzərə almaq lazımdır ki, gözlənilməz nəticələr olmasın.

Bu problemlərin qarşısını almaq üçün cədvəlləri düzgün dizayn etmək və əlaqə qaydalarını real database-də tətbiq etməzdən əvvəl test etmək vacibdir.