Xarici açarları nəzərə alaraq məlumatların silinməsi və dəyişdirilməsi

Artıq bilirik ki, xarici açarlar (FOREIGN KEY) nədir, necə işləyir və hətta onların köməyi ilə cədvəllər yaratmaqda məşq etmişik. Bəs, vaxt gəlib məlumatları silmək və ya əlaqəli qeydləri dəyişmək lazımdırsa, nə etməliyik? İndi baxaq, məlumatların silinməsi və dəyişdirilməsi xarici açarlarla necə işləyir və burada hansı fəndlər var.

Təsəvvür elə ki, database — mürəkkəb bir kart evinə bənzəyir. Bir kartı çıxarsan, bütün konstruksiyanı dağıda bilərsən. Bax, burada xarici açarlar köməyə gəlir və əlaqəli məlumatlar silinəndə database-in "dağılmasının" qarşısını alır. Gəlin baxaq, bu necə işləyir.

Məlumatları silməyə cəhd etsək nə baş verəcək?

Cədvəldə xarici açar varsa, DBMS yoxlayır ki, qeyd başqa cədvəllərlə əlaqəlidirmi. Əgər əlaqəlidirsə, qeydi silmək cəhdi məlumatların bütövlüyü səhvinə səbəb ola bilər. Belə sürprizlər olmasın deyə, xarici açar üçün əvvəlcədən hansı əməliyyatın olacağını təyin edə bilərik. Bu əməliyyatlar ON DELETE seçimləri ilə tənzimlənir.

ON DELETE ilə davranışın tənzimlənməsi

Xarici açarı yaradanda aşağıdakı qaydalardan birini seçə bilərsən:

1. ON DELETE CASCADE: Parent cədvəldə qeyd silinəndə, avtomatik olaraq child cədvəldə əlaqəli bütün qeydlər də silinir.
2. ON DELETE SET NULL: Əlaqəli qeydlər silinmir, sadəcə onların xarici açarları NULL olur.
3. ON DELETE SET DEFAULT: Xarici açarın dəyəri default dəyərə dəyişir.
4. ON DELETE RESTRICT (default davranış): Əgər əlaqəli qeydlər varsa, silmək mümkün deyil və səhv çıxacaq.
5. ON DELETE NO ACTION: Demək olar ki, RESTRICT kimidir, amma bütövlük yoxlaması transaction bitənə qədər təxirə salınır.

Nümunə: Kaskad silmə ON DELETE CASCADE

-- Müştərilər cədvəli
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Sifarişlər cədvəli
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Məlumat əlavə edirik
INSERT INTO customers (name) VALUES ('İvan İvanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Müştərini silirik
DELETE FROM customers WHERE customer_id = 1;

-- orders cədvəlində nə baş verdi?
SELECT * FROM orders; -- Heç bir qeyd yoxdur, onlar kaskadla silindi!

customers cədvəlindən qeyd siləndə, PostgreSQL avtomatik olaraq həmin müştərinin bütün sifarişlərini orders cədvəlindən silir.

Nümunə: Linkləri NULL etmək ON DELETE SET NULL

-- Yeni davranış qaydası ilə sifarişlər cədvəli
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Məlumat əlavə edirik
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Müştərini silirik
DELETE FROM customers WHERE customer_id = 1;

-- orders_with_null cədvəlinə baxırıq
SELECT * FROM orders_with_null;

Nəticə:

ON DELETE SET NULL ilə sifarişləri saxlayırıq, amma artıq mövcud olmayan müştəri ilə əlaqəni "qırırıq".

Xarici açarları nəzərə alaraq məlumatların dəyişdirilməsi

Silinmədən əlavə, parent cədvəldə məlumatların dəyişməsi də əlaqəli qeydlərə təsir edə bilər. Məsələn, əgər müştəri öz customer_id-sini dəyişsə, nə baş verəcək? Burada ON UPDATE seçimi işə düşür.

ON UPDATE ilə davranışın tənzimlənməsi

Parent cədvəldə dəyişiklikləri aşağıdakı strategiyalarla idarə edə bilərsən:

1. ON UPDATE CASCADE: Parent cədvəldə xarici açarın dəyəri dəyişəndə, child cədvəldə də avtomatik dəyişir.
2. ON UPDATE SET NULL: Child cədvəldə xarici açarın dəyəri NULL olur.
3. ON UPDATE SET DEFAULT: Default dəyər qoyulur.
4. ON UPDATE RESTRICT: Əlaqəli qeydlər varsa, xarici açarın dəyərini dəyişmək olmaz.
5. ON UPDATE NO ACTION: Yoxlama transaction sonuna qədər təxirə salınır.

Nümunə: Kaskad yeniləmə ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Məlumat əlavə edirik
INSERT INTO customers_with_cascade (name) VALUES ('İvan İvanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- customer_id-ni dəyişirik
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- orders_with_cascade cədvəlinə baxırıq
SELECT * FROM orders_with_cascade;

Nəticə:

customer_id dəyişəndə, PostgreSQL onu avtomatik olaraq orders_with_cascade cədvəlində də yeniləyir.

Praktik məşq: enrollments cədvəli

Gəlin, students və courses ilə olan nümunəmizi xatırlayaq. Biz kurslara yazılış cədvəli enrollments ilə işləyəcəyik və məlumatların silinməsini idarə edəcəyik.

-- Tələbələr cədvəli
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Kurslar cədvəli
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Aralıq yazılış cədvəli
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Məlumat əlavə edirik
INSERT INTO students (name) VALUES ('Aleksey Petrov');
INSERT INTO courses (title) VALUES ('Riyaziyyat');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Tələbəni silirik
DELETE FROM students WHERE student_id = 1;

-- enrollments cədvəlinə baxırıq
SELECT * FROM enrollments; -- Boşdur! Qeyd avtomatik silindi.

Tipik səhvlər və onların qarşısını almaq

Ən çox rast gəlinən səhvlərdən biri parent cədvəldən qeyd silmək cəhdidir, amma xarici açar üçün düzgün davranış təyin olunmayıb. Məsələn, əgər ON DELETE üçün heç nə göstərməsən, default olaraq RESTRICT işləyəcək və bu da səhvə səbəb olacaq.

Həmçinin, çox tez-tez kaskad əməliyyatlardan (CASCADE) istifadə etmək gözlənilməz nəticələrə gətirib çıxara bilər. Məsələn, təsadüfən lazım olandan çox məlumat silə bilərsən.

Belə problemlərin qarşısını almaq üçün bu tövsiyələrə əməl et:

• Həmişə ON DELETE və ON UPDATE davranışını tətbiqin məntiqinə uyğun olaraq yaxşı düşün.
• Xüsusilə vacib əməliyyatlar üçün dəyişiklik və ya silməzdən əvvəl yoxlama sorğuları əlavə et.
• Səhv baş verərsə, dəyişiklikləri geri qaytarmaq üçün transaction-lardan istifadə et.